查找出至少连续3天下单的用户。
第一步:将订单表按UserId分组根据日期Date排序
第二步:用日期Date减去对应的排序号Num,若日期是连续的,则相减的结果Datedif相等。
第三步:按UserId,Datedif分组计数,得到各用户的连续下单天数。
第四步:筛选连续下单天数≥n的用户
表结构
SQL
按company分组,根据日期day排序。用日期减去对应的排序号rn,若日期是连续的,则相减的结果a 相等
各公司的连续亏损天数
with t1 as (select * from t_value where value < 0)
, t2 as (select *, row_number() over(partition by company order by day) as rn from t1)
, t3 as (select *, (day - (rn || ' day')::interval) as a from t2)
, t4 as (select *, count(1) over(partition by company, a order by day) as num_day from t3)
select * from t4 where num_day > 2;