SQL for Data science : Calculation YTD Amount with PARTITION BY
SQL script for data science เสนอตอนการคำนวณ YTD จากรูปด้านล่างคือ Output ที่เราต้องการให้มีการคำนวณ YTD ของ measurement Amount โดยให้ sum แบบ incremental ทุกๆ เดือนมาดูวิธีการเขียน Script ง่ายๆกันครับ
ส่วนที่ 1 : เรามาเตรียมข้อมูลเพื่อใช้ในการเขียน script
–Prepare Data for Test
–drop table #table
select a.* into #table from (
select ‘P1’ as Product,‘2017-11-30’ as as_of_date, 100 as Amount union all
select ‘P1’ as Product,‘2017-12-31’ as as_of_date, 200 as Amount union all
select ‘P1’ as Product,‘2018-01-31’ as as_of_date, 100 as Amount union all
select ‘P1’ as Product,‘2018-02-28’ as as_of_date, 200 as Amount union all
select ‘P1’ as Product,‘2018-03-31’ as as_of_date, 50 as Amount union all
select ‘P1’ as Product,‘2018-03-31’ as as_of_date, 75 as Amount union all
select ‘P1’ as Product,‘2018-01-31’ as as_of_date, 120 as Amount union all
select ‘P1’ as Product,‘2018-01-31’ as as_of_date, 60 as Amount union all
select ‘P1’ as Product,‘2018-03-31’ as as_of_date, 50 as Amount union all
select ‘P1’ as Product,‘2018-04-30’ as as_of_date, 100 as Amount union all
select ‘P1’ as Product,‘2018-05-31’ as as_of_date, 45 as Amount union all
select ‘P1’ as Product,‘2018-06-30’ as as_of_date, 78 as Amount union all
select ‘P1’ as Product,‘2018-02-28’ as as_of_date, 90 as Amount
)a
ส่วนที่ 2 : เขียน SQL คำนวณโดยใช้คำสั่ง OVER (PARTITION BY)
–Script For Cal YTD
SELECT a.as_of_date,a.Product
,MTD_Amount = a.Amount
,YTD_Amount = SUM( a.Amount) OVER (PARTITION BY YEAR(a.as_of_date), a.Product ORDER BY a.as_of_date)
FROM (
SELECT Product,as_of_date,SUM(Amount) as Amount FROM #table
GROUP BY Product,as_of_date )a
