sql-年度销售总额，拓展出对照表，排序表 ，日期排序表，日历表

–author:李银实
–按年度列出销售总额
–1)建表插入数据 并查询
Create table If Not Exists 83_Product (product_id int, product_name STRING );
Create table If Not Exists 83_Sales (product_id STRING, period_start DATETIME , period_end DATETIME, average_daily_sales int);
Truncate table 83_Product;
insert into 83_Product (product_id, product_name) values (‘1’, 'LC Phone ');
insert into 83_Product (product_id, product_name) values (‘2’, ‘LC T-Shirt’);
insert into 83_Product (product_id, product_name) values (‘3’, ‘LC Keychain’);
Truncate table 83_Sales;
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values (‘1’, ‘2019-01-25 00:00:00’, ‘2019-02-28 00:00:00’, ‘100’);
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values (‘2’, ‘2018-12-01 00:00:00’, ‘2020-01-01 00:00:00’, ‘10’);
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values (‘3’, ‘2019-12-01 00:00:00’, ‘2020-01-31 00:00:00’, ‘1’);
SELECT * from 83_Product;
SELECT * from 83_Sales;
–2)需求 查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。
–销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。
–83_Product表
– product_id product_name
– 3 LC Keychain
– 1 LC Phone
– 2 LC T-Shirt
–83_Sales表
– product_id period_start period_end average_daily_sales
– 2 2018-12-01 00:00:00 2020-01-01 00:00:00 10
– 3 2019-12-01 00:00:00 2020-01-31 00:00:00 1
– 1 2019-01-25 00:00:00 2019-02-28 00:00:00 100
– 最后的表格如下
– ±-----------±-------------±------------±-------------+
– | product_id | product_name | report_year | total_amount |
– ±-----------±-------------±------------±-------------+
– | 1 | LC Phone | 2019 | 3500 |
– | 2 | LC T-Shirt | 2018 | 310 |
– | 2 | LC T-Shirt | 2019 | 3650 |
– | 2 | LC T-Shirt | 2020 | 10 |
– | 3 | LC Keychain | 2019 | 31 |
– | 3 | LC Keychain | 2020 | 31 |
– ±-----------±-------------±------------±-------------+

–3）83_Product表 只是维度表，处理好事实表然后去关联即可 ，其实就是拆分 83_Sales表 关键是 report_year 的算法
—如何通过一个开始时间和结束时间求出跨越的年份月份？
CREATE TABLE st_en
(
id STRING
,start DATETIME
,end DATETIME
)
;
INSERT INTO st_en VALUES (‘1’,‘2018-10-01 00:00:00’,‘2020-03-20 00:00:00’) ;

INSERT INTO st_en VALUES (‘2’,‘2019-10-01 00:00:00’,‘2020-01-31 00:00:00’) ;

INSERT INTO st_en VALUES (‘3’,‘2021-01-01 00:00:00’,‘2021-04-06 00:00:00’) ;

SELECT * from st_en;
CREATE table st_end_exp(
id STRING ,
month STRING ,
year STRING
);
SELECT * from st_end_exp;
insert into st_end_exp VALUES (‘1’,‘10’,‘2018’),(‘1’,‘11’,‘2018’),(‘1’,‘12’,‘2018’),(‘1’,‘01’,‘2019’),(‘1’,‘02’,‘2019’),(‘1’,‘03’,‘2019’),(‘1’,‘04’,‘2019’),(‘1’,‘05’,‘2019’),
(‘1’,‘06’,‘2019’),(‘1’,‘07’,‘2019’),(‘1’,‘08’,‘2019’),(‘1’,‘09’,‘2019’),(‘1’,‘10’,‘2019’),(‘1’,‘11’,‘2019’),(‘1’,‘12’,‘2019’),(‘1’,‘01’,‘2020’),(‘1’,‘02’,‘2020’),(‘1’,‘03’,‘2020’);
insert into st_end_exp VALUES (‘2’,‘10’,‘2019’),(‘2’,‘11’,‘2019’),(‘2’,‘12’,‘2019’),(‘2’,‘01’,‘2020’);
insert into st_end_exp VALUES (‘3’,‘01’,‘2021’),(‘3’,‘02’,‘2021’),(‘3’,‘03’,‘2021’),(‘3’,‘04’,‘2021’);
【如何不写java，不写python只用sql 得到上面的表st_end_exp，我一条条插入数据只是想让大家看看效果。】
—拓展 sql 如何写 select row_number()over() as rk from ypp_user limit 10000 从1 到100000
—1⃣️拓展 写个排序表
CREATE TABLE rk
(
rk BIGINT
)
COMMENT ‘排序表’
;

DROP TABLE rk ;

INSERT INTO rk
SELECT row_number()OVER() AS rk
FROM ethan(随便找个公司的业务表)
LIMIT 50000
;
–2⃣️写个日期排序表

CREATE TABLE date_rk
(
date_time DATETIME
)
COMMENT “日期排序表”
;

INSERT INTO date_rk
SELECT dateadd(‘2010-01-01 00:00:00’,rk,‘dd’) AS date_time
FROM rk
;
–3⃣️ 写个日历表

DROP TABLE dim_ethan_calendar ;

CREATE TABLE dim_ethan_calendar
(
date_time DATETIME
,date_id STRING
,DATE STRING
,month_id STRING
,month_date STRING
,year_id STRING
,quarter INT
,week_day BIGINT
,week_start_day STRING
,week_end_day STRING
,week_start_day_sunday_start STRING
,week_end_day_sunday_start STRING
,week_end_day_saturday_start STRING
,week_start_day_saturday_start STRING
)
COMMENT ‘日历表—ethan’
;

INSERT INTO TABLE dim_ethan_calendar
SELECT date_time
,to_char(date_time,‘yyyymmdd’) AS date_id
,to_char(date_time,‘yyyy-mm-dd’) AS date
,to_char(date_time,‘yyyymm’) AS month_id
,to_char(date_time,‘yyyy-mm’) AS month_date
,to_char(date_time,‘yyyy’) AS year_id
,quarter(date_time) AS quarter
,weekday(date_time)+1 AS week_day
,to_char(
dateadd(date_time,-weekday(date_time),‘dd’)
,‘yyyy-mm-dd’
) AS week_start_day
,to_char(
dateadd(date_time,6-weekday(date_time),‘dd’)
,‘yyyy-mm-dd’
) AS week_end_day
,to_char(
dateadd(date_time,-weekday(dateadd(date_time,1,‘dd’)),‘dd’)
,‘yyyy-mm-dd’
) AS week_start_day_sunday_start
,to_char(
dateadd(date_time,6-weekday(dateadd(date_time,1,‘dd’)),‘dd’)
,‘yyyy-mm-dd’
) AS week_end_day_sunday_start
,to_char(
dateadd(date_time,-weekday(dateadd(date_time,2,‘dd’)),‘dd’)
,‘yyyy-mm-dd’
) AS week_start_day_saturday_start
,to_char(
dateadd(date_time,6-weekday(dateadd(date_time,2,‘dd’)),‘dd’)
,‘yyyy-mm-dd’
) AS week_end_day_saturday_start
FROM date_rk
;

–4⃣️ 回头再解决这个问题
第一步【便于查看所以写了临时表cn01】create table cn01 AS
SELECT
s.product_id,
d.date_time,
d.year_id,
s.average_daily_sales
FROM 83_Sales s join dim_ethan_calendar d
on d.date_time >=s. period_start and d.date_time <=s.period_end;
第二步 得到结果
SELECT
t2.product_name,
t1.product_id,
t1.report_year,
t1.total_amount
from
(SELECT
product_id,year_id as report_year ,count(date_time)*(min(average_daily_sales)) total_amount
from cn01
group by product_id,year_id ) t1
LEFT join 83_Product t2
on t1.product_id= t2.product_id;