…筛选连续三个月及以上有交易流水的用户…

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33  with tmp_1 as ( select cust_id from trx_act where trx_date between '2022-01-01' and '2022-01-31' group by cust_id ),tmp_2 as ( select cust_id from trx_act where trx_date between '2022-02-01' and '2022-02-29' group by cust_id ), ..... ,tmp_12 as ( select cust_id from trx_act where trx_date between '2022-12-01' and '2022-12-31' group by cust_id ) select a.cust_id from tmp_1 a left join tmp_2 b on a.cust_id = b.cust_id left join tmp_3 c on a.cust_id = c.cust_id where b.cust_id is not null and c.cust_id is not null .....

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24  -- 方法1 select cust_id,action,count(*) as times from ( select cust_id,action,(month - rank_num) as flag from ( select cust_id,action,month,rank() over(partion by cust_id,action order by month) as rank_num from tbl group by month,action,cust_id a )b ) group by cust_id,action,flag having times >= 3; -- 方法二 select distinct cust_id,action from ( select cust_id ,action ,month ,lag(month,2) over(partition by cust_id,action order by month) as lag_2_month from tbl group by month,cust,action )a where month - lag_2_month = 2; -- 某一行的月份 - 前两行的月份的差值等于2

 1 2 3 4  LAG([,offset[, default_value]]) OVER ( PARTITION BY expr,... ORDER BY expr [ASC|DESC],... ) 

### expression#

LAG()函数返回expression当前行之前的行的值，其值为offset 其分区或结果集中的行数。

### offset#

offset是从当前行返回的行数，以获取值。offset必须是零或文字正整数。如果offset为零，则LAG()函数计算expression当前行的值。如果未指定offset，则LAG()默认情况下函数使用一个。

### PARTITION BY 子句#

PARTITION BY子句将结果集中的行划分LAG()为应用函数的分区。如果省略PARTITION BY子句，LAG()函数会将整个结果集视为单个分区。

### ORDER BY 子句#

ORDER BY子句指定在LAG()应用函数之前每个分区中的行的顺序。

LAG()函数可用于计算当前行和上一行之间的差异。