2.6.1 题目需求
从订单明细表(order_detail)统计每个商品销售首年的年份,销售数量和销售总额。期望结果如下:
1、查询order_detail表
2、分析题目并写代码
--1 需求中的日期精确到年,而表中的日期是精确到日,因此需要我们从日期中提取出年,这就需要用到year(日期字段)函数。
select sku_id,
year(create_date) year,
price,
sku_num
from order_detail od;
展示结果如下:
--2 题目中要求每个商品的信息,显然要求我们按照shu_id进行分组查询,并且要求查出首年的销售情况,那如何让体现出首年呢?如果某个商品第一次销售是在2020年,那就可以忽略后面的2021年。因此可以对每件商品按照年份进行排名,第一年标记为1,以后的年份不等于1,这样条件过滤排名等于1的即可。
因此补充排名代码:
select sku_id,
year(create_date) year,
price,
sku_num,
rank() over (partition by sku_id order by year(create_date)) rk
from order_detail od;
查询结果展示如下:
可见每个商品第一次销售的年份被排名为1。
--3 现在我们继续思考,题目中要求我们按照年份统计出商品的销售情况,因此我们可以利用sum()函数对价格和数量进行求和,注意:年销售额是 sum(price*sku_num)。因此继续补充代码如下:
select
sku_id,
year,
sum(sku_num) total_num,
sum(price*sku_num) total_price,
rank() over (partition by sku_id order by year) rk
from (select sku_id,
year(create_date) year,
price,
sku_num
from order_detail od) t1
group by sku_id,year;
查询结果如下:
很好,每件商品的年总销量和总销售额已经出来了,接下来我们只需要选出每件商品排名为1的信息即可,补充代码如下:
select sku_id,
year,
total_num,
total_price
from (select
sku_id,
year,
sum(sku_num) total_num,
sum(price*sku_num) total_price,
rank() over (partition by sku_id order by year) rk
from (select sku_id,
year(create_date) year,
price,
sku_num
from order_detail od) t1
group by sku_id,year)t2
where rk=1;
查询结果如下:
完美!
3、总结
关键字:
首年:排名为1
补充:精确到年,即可用year(时间)来提取出 年
销售总和:sum求和