/*
统计月度商品累计销售量金额和销售量
样本数为每月商品销售记录以及时间维度表

1）样本分析：日期、商品ID、销售金额、销售量，且存在某月商品销售记录为空，
2）目标结果：日期、商品ID、累计销售金额、累计销售量
3）取数思路1：
1.1)先计算出中间表1（商品ID、日期、销售金额、销售量），然后按照窗口函数，计算结果
1.2)先补全数据，得出中间表1,
1.3）根据商品ID、时间ID关联得出全量中间表2，再与商品销售记录进行关联得出中间表3
取数思路2：
这里取数设置一个需求条件，即满足每个商品生命周期内期间的统计，早过或者晚于这个周期不用统计。
1.1 统计每个产品每年的最后销售月份，作为最大时间，作为商品销售记录的新信息，得表2
1.2 通过时间维度表与新表（表2）进行笛卡尔积关联，保证时间在同一年，时间维度表的时间居于表2的销售月份和该记录的最大时间之间，

*/

----------------数据准备---------------
/*
创建商品销售记录表（month_end_sales_order_fact），录入样本数据
创建商品信息表、时间维度表
*/
USE sale_order;

DROP TABLE
IF EXISTS month_end_sales_order_fact;

CREATE TABLE month_end_sales_order_fact (
	order_year_sk INT COMMENT ‘order year SK‘
	,order_month_sk INT COMMENT ‘order month SK‘
	,product_sk INT COMMENT ‘product SK‘
	,month_order_amount DECIMAL (10, 2) COMMENT ‘month order amount‘
	,month_order_quantity INT COMMENT ‘month order quantity‘
);

INSERT INTO month_end_sales_order_fact
VALUES
	(2021,202101, 1001, 1.11, 1)
	,(2021,202103, 1001, 2.22, 1)
	,(2021,202104, 1002, 2.22, 1)
	,(2021,202105, 1003, 2.22, 1)
	,(2020,202001, 1001, 1.11, 1)
	,(2020,202003, 1001, 2.22, 1)
	,(2020,202004, 1002, 2.22, 1)
	,(2020,202005, 1003, 2.22, 1)
	,(2020,202001, 1004, 3.33, 1)
;

DROP TABLE
IF EXISTS product_sk;

CREATE TABLE product_sk (
	product_sk INT COMMENT ‘product SK‘
);

INSERT INTO product_sk
VALUES
	(1001)
	,(1002)
	,(1003)
	,(1004)
;
DROP TABLE
IF EXISTS month_dim;

CREATE TABLE month_dim (
	year_sk INT COMMENT ‘year_id‘,
	month_sk INT COMMENT ‘month_id‘
);

INSERT INTO month_dim
VALUES
	(2021, 202101),
	(2021, 202102),
	(2021, 202103),
	(2021, 202104),
	(2021, 202105),
	(2020, 202001),
	(2020, 202002),
	(2020, 202003),
	(2020, 202004),
	(2020, 202005);

-- 计算月度商品销售记录的年度累计销售情况（方法1）结果40条数据
select
	 a.year_sk
	,a.month_sk
	,a.product_sk
	,sum(IFNULL(c.month_order_amount ,0)) over (PARTITION by  a.year_sk, a.product_sk order by a.month_sk rows between unbounded preceding and current row     ) month_order_amount
	,sum(IFNULL(c.month_order_quantity ,0)) over (PARTITION by  a.year_sk,a.product_sk order by a.month_sk rows between unbounded preceding and current row     ) month_order_quantity
from  (select * from month_dim 
JOIN	product_sk )a
LEFT JOIN	month_end_sales_order_fact c
on 		c.order_month_sk = a.month_sk
and 	a.product_sk = c.product_sk
ORDER BY a.product_sk,a.month_sk
;

-- 计算月度商品销售记录的年度累计销售情况（方法2）结果14条记录
SELECT
	a.month_sk,
	b.product_sk,
	sum(b.month_order_amount) month_order_amount,
	sum(b.month_order_quantity) month_order_quantity
FROM
	month_dim a,
	(
		SELECT
			a.*, b.year_sk,
			b.month_sk,
			max(a.order_month_sk) over (PARTITION BY a.product_sk) max_month_sk
		FROM
			month_end_sales_order_fact a,
			month_dim b
		WHERE
			a.order_month_sk = b.month_sk
	) b
WHERE
a.month_sk <= b.max_month_sk
AND 
a.year_sk = b.year_sk
AND b.month_sk <= a.month_sk
GROUP BY
	a.month_sk,
	b.product_sk
ORDER BY 	
	b.product_sk asc ,a.month_sk ASC
;

【SQL语句】统计月度商品累计销售量金额和销售量