中级HQL题6（商品首年的销量数据）

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求和