HQL,SQL刷题，尚硅谷（中级）

目录

相关表结构：

题目及思路解析：

第一题：每个商品销售首年的年份、销售数量和销售金额

第二题：筛选去年总销量小于100的商品

第三题：查询每日新用户数

第四题：统计每个商品的销量最高的日期

总结归纳： 

知识补充：

---

相关表结构：

1、order_detail表

2、user_login_detail表

3、sku_info表

题目及思路解析：

第一题：每个商品销售首年的年份、销售数量和销售金额

题目需求

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

 题目分析：

要求统计每个商品销售首年的年份、销售数量、销售总额。

所谓首年即日期最小的，也就是分组topN类型的题目，这里就可以使用排序开窗函数

代码实现：

select
       sku_id,
       year(create_date) year,
       sum(sku_num)order_num,
       sum(price*sku_num)order_amount
from (
         select
                sku_id,
                price,
                sku_num,
                create_date,
                rank() over (partition by sku_id order by year(create_date)) rk
         from order_detail
     ) t1
where rk = 1
group by sku_id,year(create_date);

思路分析：

首先是进行排序开窗，获取所需的字段

之后进行筛选选出第一的行数据，同时统计对应的销售数量与销售金额

注意：

这里对于排序开窗函数的选择应选择rank(),因为rank()是并列不连续的排序，即当某个首年的销售月份或者天数有多个时才能被统计进去

代码结果： 

第二题：筛选去年总销量小于100的商品

题目需求

从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品，期望结果如下：

题目分析：

要求（两个要求条件）：统计 去年的总销售量 <100 的商品，同时需要排除上架时间小于1个月的商品

提示信息 ：日期’2022-01-10‘ ，由此可知 所求的去年即2021年

代码实现：

select t1.sku_id,
       name,
       order_num
from (
         select sku_id,
                sum(sku_num) order_num
         from order_detail
         where year(create_date) = '2021'
         and sku_id in (
             select sku_id
             from sku_info
             where datediff('2022-01-10', from_date) > 30 )
         group by sku_id
         having sum(sku_num) < 100 ) t1
left join sku_info t2
on t1.sku_id = t2.sku_id;

思路分析：

首先我们选出2021年的销售记录

同时就是 求出上架时间超过1个月的商品

之后就是（分组后）求出 销售量 小于100的

最后就是关联一下商品信息表获取商品的名称

注意：

1、这道题关键在于他没有使用开窗函数或UDF之类的函数

2、另外商品上架日期是在商品信息表里面有

3、本题用到了 datediff()、year()日期函数

代码结果： 

第三题：查询每日新用户数

题目需求：

从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。期望结果如下：

题目分析：

要求：统计每天的新增用户数。

信息：用户首次登录当天即新增用户。

这里要获取每个用户的首日登录时间，也就意味着需要获取最小的时间日期，所以，这里可以使用排序开窗函数 或者使用 min()聚合函数

代码实现1：

select
    login_date_first,
    count(*) user_count
from
(
    select
        user_id,
        min(date_format(login_ts,'yyyy-MM-dd')) login_date_first
    from user_login_detail
    group by user_id
)t1
group by login_date_first;

代码实现2：

select
    date_format(login_ts,'yyyy-MM-dd') ,
    count(*)
from (
    select
        user_id,
        login_ts,
        row_number() over (partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
    from user_login_detail)t1
where rk =1
group by  date_format(login_ts,'yyyy-MM-dd');

思路分析：

方法1 是使用排序函数开窗：

    首先进行开窗排序，获取对应的时间序列

    之后进行筛选 选出第一行的字段值

方法2 是直接使用 min（)聚合函数

    首先 使用min（）函数筛选出每个用户的最早登录日期

     之后统计对应的字段数据

代码结果： 

第四题：统计每个商品的销量最高的日期

题目需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。期望结果如下：

题目分析：

· 要求：   1、统计获取每种商品销量最高的日期（同时显示其销量），

                2、当销量并列时候，取日期最小的

· 求最高，其实就是类似于分组topN，也就是要进行排序，获取最高的

· 这里题目中的第二个要求其实一般是在排序时候进行设置的，即多个排序字段

代码实现：

select sku_id,
       create_date,
       sum_num
from (
         select sku_id,
                create_date,
                sum_num,
                row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn
         from (
                  select sku_id,
                         create_date,
                         sum(sku_num) sum_num
                  from order_detail
                  group by sku_id, create_date
              ) t1
     ) t2
where rn = 1;

思路分析：

首先是 统计每种商品的日销售量

之后 进行排序函数开窗，获取日销售量最高的数据信息（这里排序销量字段降序，而销售日期为升序）

最后对排序后的结果进行筛选即可，即选出第一的数据信息

总结归纳： 

1、方法可以灵活，思路要打开，不一定要使用开窗，UDF等高级复杂的函数来解决

2、这几题其实用排序开窗函数比较多，而有时开窗函数解决的题目也可以使用min()、max()函数解决

3、当要求某个字段值出现并列，按照另一个字段值大小选择时，此类要求一般是用于排序时候的排序字段选择 即order by 后面的字段

知识补充：

1、排序开窗函数（row_number/rank/dense_rank）

三个排序函数都是用来显示窗口内当前行的排名，但区别是：

 row_number() --无并列的递增序号。如：1-2-3-4-5

 rank() --可并列但不连续的排序。如：1-2-3-3-5-6

 dense_rank() --可并列并连续的排序。如：1-2-3-3-4-5

2、 日期函数：

· 日期比较函数： datediff

语法： datediff(string enddate, string startdate)

返回值： int

说明： 返回结束日期减去开始日期的天数。

案例：

select datediff('2012-12-08','2012-05-09') from iteblog;
213

· 指定格式返回时间函数：date_format

语法： date_format(date/timestamp/string ts, string fmt)

返回值： string

说明： 按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

案例：

select date_format(current_date(),‘MM.dd’);
07.13

· 日期转年函数： year

语法： year(string date)

返回值： int

说明： 返回日期中的年。

案例：

hive> select year('2011-12-08 10：03：01') from iteblog;
2011
hive> select year('2012-12-08') from iteblog;
2012