刷题随笔-sql连续下单问题

4月22日：

查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：

user_id <string>
101

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

思路：

连续时间问题，可以用函数  lead（字段1，往后数N行）over（）

举例，101用户，其中一单create_date为2021-09-30，按时间排序后，这一单往后数2行，如果连续的话，即为2021-09-28。

判断第三行的下单是否为2021-09-28即可，如果是，则连续3天下单用户。

图示：

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
3	101	2021-09-30	29000.00
2	101	2021-09-29	29000.00
1	101	2021-09-28	29000.00
15	103	2020-10-02	28000.00
14	103	2020-09-29	28000.00
13	103	2020-09-28	28000.00

解题：按用户分组，按下单时间倒叙排序，lead取后3行，date_sub取某日期的前3天

SELECT DISTINCT
  aa.user_id
from
  (
    SELECT
      user_id,
      lead (create_date, 2) over ( PARTITION by user_id order by create_date desc) as diff_date_1,
      date_sub (create_date, 2) as diff_date_2
    from
      order_info
  ) aa
where
  aa.diff_date_1 = aa.diff_date_2

4月23日

从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，

期望结果如下：

percentage <string>
70.0%

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

思路一：

用户占比：1、分母：统计全部用户数，去重。2、分子：统计第二天也下单的用户数。这一步是难点，用户分组正序排序，拿到第一行，为首次下单日期，再看第二行下单日期是否连续。

解题：

第二行和第一行是否连续，可以用datediff（date1，date2）函数比较。得到两个日期之差，如果是1，则连续。坑点：排序按用户、下单日期去重。

SELECT
  cast(cc.fenzi / dd.fenmu *100 as  decimal(10,1))||'%'
from
  (
    SELECT
      count(distinct (user_id)) as fenzi
    from
      (
        SELECT
          aa.user_id,
          datediff (
            lead (create_date, 1) over ( PARTITION by user_id order by create_date asc   ),
            create_date
          ) as nn
        from
          (
            SELECT
              dense_rank() over (  PARTITION by user_id,create_date order by create_date asc ) as cn,
              user_id,
              create_date
            from
              order_info
          ) aa
        where
          aa.cn = 1
      ) bb
    where
      nn = 1
  ) as cc
  left join (
    SELECT
      count(DISTINCT (user_id)) as fenmu
    from
      order_info
  ) as dd on 1 = 1

思路二：1、分母：统计全部用户数，去重。2、取用户下单最小的日期minDate，用minDate和每一个下单日期相减，如果存在日期差=1，则表示minDate的第二天也有订单。

SELECT
	cast(
	bb.fenzi / dd.fenmu * 100 AS DECIMAL ( 10, 1 ))|| '%' 
FROM
	(
	SELECT
		count(
		DISTINCT ( user_id )) AS fenzi 
	FROM
		( SELECT 
user_id, 
create_date,
 min( create_date ) over ( PARTITION BY user_id ) AS minDate 
FROM order_info GROUP BY user_id, create_date ) aa 
	WHERE
		datediff( aa.create_date, aa.minDate )= 1 
	) bb
	LEFT JOIN ( 
SELECT count( DISTINCT ( user_id )) AS fenmu FROM order_info ) AS dd ON 1 = 1

4月25日

看到了一个很有意思的题目。实话实说本人菜狗，不会做，偷看了答案，这解题思路很惊喜。值得记录。

从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。

期望结果如下：

user_id <string> (用户id)	start_date <string> (开始日期)	end_date <string> (结束日期)
101	2021-09-27	2021-09-30
102	2021-10-01	2021-10-02
106	2021-10-04	2021-10-05
107	2021-10-05	2021-10-06

需要用到的表：

登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)
101	180.149.130.161	2021-09-21 08:00:00
102	120.245.11.2	2021-09-22 09:00:00
103	27.184.97.3	2021-09-23 10:00:00

思路：用户分组，按登陆日期去重排序。拿登陆日期减排名，会得出一个很神奇的数字，按这个数分组，即可得到连续登陆的日期。例如：

user_id(用户id)	Ip_address(ip地址)	login_ts(登录时间)	排名	登陆时间-排名
101	180.149.130.161	2021-09-20	1	2021-09-19
101	180.149.130.161	2021-09-22	2	2021-09-20
101	180.149.130.161	2021-09-23	3	2021-09-20
101	180.149.130.161	2021-09-25	4	2021-09-21
101	180.149.130.161	2021-09-26	5	2021-09-21
101	180.149.130.161	2021-09-27	6	2021-09-21
103	180.149.130.161	2021-09-23	1	2021-09-22

如表所示【登陆时间-排名】相同的，登陆时间就是连续的。

解题：秒转成日期to_date()，按用户、日期去重排序row_number()，用户、登陆日期-排名分组date_sub()，输出取组内最大日期和最小日期。

SELECT
	user_id,
	end_date,
	start_date 
FROM
#按【登陆日期-排名】、用户名分组，取组内最大最小日期
	(
	SELECT
		t2.user_id,
		max( t2.tmp_date ) over ( PARTITION BY part, t2.user_id ) AS end_date,
		min( t2.tmp_date ) over ( PARTITION BY part, t2.user_id ) AS start_date 
	FROM
#登陆日期-排名
		(
		SELECT
			t1.user_id,
			t1.tmp_date,
			date_sub( t1.tmp_date, t1.cn ) AS part 
		FROM
#转日期，去重，排序
			(
			SELECT
				user_id,
				to_date ( login_ts ) AS tmp_date,
				rank () over ( PARTITION BY user_id ORDER BY to_date ( login_ts ) ASC ) AS cn 
			FROM
				user_login_detail 
			GROUP BY
				user_id,
			to_date ( login_ts )) t1 
		) t2 
	) t3 
WHERE
	end_date != start_date   #去掉不连续的日期
GROUP BY  #去重
	user_id,
	end_date,
	start_date