描述
11月结束后,小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
11月份销售数据表sales_tb如下(其中,sales_date表示销售日期,user_id指用户编号,item_id指货号,sales_num表示销售数量,sales_price表示结算金额):
sales_date | user_id | item_id | sales_num | sales_price |
2021-11-01 | 1 | A001 | 1 | 90 |
2021-11-01 | 2 | A002 | 2 | 220 |
2021-11-01 | 2 | B001 | 1 | 120 |
2021-11-02 | 3 | C001 | 2 | 500 |
2021-11-02 | 4 | B001 | 1 | 120 |
2021-11-03 | 5 | C001 | 1 | 240 |
2021-11-03 | 6 | C002 | 1 | 270 |
2021-11-04 | 7 | A003 | 1 | 180 |
2021-11-04 | 8 | B002 | 1 | 140 |
2021-11-04 | 9 | B001 | 1 | 125 |
2021-11-05 | 10 | B003 | 1 | 120 |
2021-11-05 | 10 | B004 | 1 | 150 |
2021-11-05 | 10 | A003 | 1 | 180 |
2021-11-06 | 11 | B003 | 1 | 120 |
2021-11-06 | 10 | B004 | 1 | 150 |
请你统计连续2天及以上在该店铺购物的用户及其对应的次数(若有多个用户,按user_id升序排序),以上例子的输出结果如下:
user_id | days_count |
10 | 2 |
解题思路:
1. 需要考虑如何求出连续天数的购买用户,所以需要考虑窗口函数来判断
2. 由于一个日期可能某用户会有多个购买记录,所以使用窗口函数时用dense_rank函数
3. 如何判断是否连续,考虑DATE_SUB或者DATE_ADD函数来判断,例如某用户11-4和11-5有购买记录,则11-4记为rk1,11-5记为rk2,通过DATE_SUB(date, INTERVAL rk DAY)来进行判断连续,11-4减1为11-3,11-5减2为11-3(如果连续,date_sub函数出来的日期应该是一样的,都为11-3,所以可以根据这个进行判断)
4. 将判断连续的函数放在group by 里面进行分组,统计用户的连续购买天数
5. 最后用HAVING筛选连续两天及以上购买的用户id
代码如下:
SELECT user_id, COUNT(*) AS days_count
FROM (SELECT DISTINCT sales_date,
user_id,
dense_rank()over(partition by user_id order by sales_date) as rk
FROM sales_tb) t1
group by user_id, DATE_SUB(sales_date, INTERVAL rk day)
HAVING count(*)>=2