目录
相关表结构:
题目及思路解析:
第一题:每个商品销售首年的年份、销售数量和销售金额
第二题:筛选去年总销量小于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
说明: 返回结束日期减去开始日期的天数。
案例:
Pythonselect 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
案例:
Pythonselect date_format(current_date(),‘MM.dd’); 07.13
· 日期转年函数: year
语法: year(string date)
返回值: int
说明: 返回日期中的年。
案例:
Pythonhive> select year('2011-12-08 10:03:01') from iteblog; 2011 hive> select year('2012-12-08') from iteblog; 2012
还没有评论,来说两句吧...