HQL - 查询首次下单后第二天连续下单的用户比率

水善利万物而不争，处众人之所恶，故几于道💦

题目：

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

解题：

这个是大杂烩，select 套select …相信没人想看…

with a as(
  select user_id from(
    select user_id,create_date,next_order_date from (
      select
      user_id,
      create_date,
      lead(create_date,1) over(partition by user_id order by create_date) next_order_date,
      rank() over(partition by user_id order by create_date) rk
      from order_info
      group by user_id,create_date
    ) t1 where rk=1
  ) t2 where datediff(date(next_order_date),date(create_date))=1
), b as(
  select count(*) next_order_user from a 
), c as(
  select user_id from order_info group by user_id
), d as(
  select count(*) all_user from c
)
 
select concat(next_order_user/all_user*100,'%') percentage from b,d 

但是我又优化了一下👍

with a as(
  select 
    user_id,
    create_date,
    rank() over(partition by user_id order by create_date) rk,  --排序，标号
    lead(create_date,1) over(partition by user_id order by create_date) next_order_date
    --查询create_date，下一行的日期，以user_id分组，create_date升序排序，重命名为 next_order_date
  from order_info group by user_id,create_date  --这里分组是为了去掉每天重复的记录，一天只算一次
), b as(
select user_id,create_date,next_order_date from a where rk=1 --取出首次下单的那一条
), c as(
select user_id from b where datediff(date(next_order_date),date(create_date))=1 --过滤出隔天还下单的用户id
), d as(
select count(*) next_order_user  from c  --统计隔天还下单的用户数
), e as(
select user_id from order_info group by user_id  --查出下单用户的id
), f as(
select count(*) all_user from e  --统计下单用户的人数
)
select concat(next_order_user/all_user*100,'%') percentage from d,f  --算出百分比

和上面那种等价的，只不过是条件一块过滤了，不用分两次过滤：

with a as (
    select 
        user_id,
        create_date,
        lead(create_date, 1) over (partition by user_id order by create_date) next_order_day,
        rank() over (partition by user_id order by create_date) rk 
    from order_info
), b as(
  select 
      user_id
  from a
  where rk = 1 and datediff(date(next_order_day), date(create_date)) = 1
), c as( 
select count(*) next_order_user from b
), d as(
select user_id from order_info group by user_id
), e as(
select count(*) all_user from d
)

select concat(next_order_user/all_user*100,'%') percentage from c,e

总结：

lead(col,n, default_val)：某一列往后数n条数据，lag()是往前数

date()：将一个字符串类型的日期转换为日期类型

datediff(date1,date2)：返回两个日期之间的日期差，参数可以是字符串，会隐式转换

concat(string A/col, string B/col…)：将两个或多个字符串按顺序连接成一个字符串

1.使用多层的嵌套子查询，会导致多次扫描同一个表，效率变低，可以将第一层子查询语句作为一个独立的 with 子句来实现，避免多次扫描同一个表，语法是：

with first_table as(
   --子查询
), second_table as(
  --子查询，可以基于first_table查
)

使用这种语句书写 sql 可以更好地组织查询的逻辑，并且避免重复的表扫描操作，从而提高查询的性能。

2.尽量使用 date() 函数避免隐式转换

在datediff()函数中，输入的值会被自动转换成日期类型。如果输入的不是日期类型，就会进行隐式类型转换，这会影响查询效率。我们可以使用date()函数将字符串转换为日期类型，从而避免隐式类型转换的开销。