如何高效地取连续三个月同一时间有交易的商户

[zzzain46]

流水表结构


cust_id,trx_dte,trx_amt


示例数据


'10086','2022-01-05',23.56

'10086','2022-02-05',23.56

'10086','2022-03-05',23.56

取 2021-05-01 到 2022-04-30 期间，连续三个月在同一天有同样金额交易的客户，例如上面这个 cust_id 为 10086 的客户就是我需要的客户。

[encro]

select count(*),cust_id,hour(trx_amt) from table group by cust_id,hour/day/minute(trx_amt) order by count(*) ?

[wolfie]

where
exists ( 下个月同一天 )
and
exists ( 下下个月同一天 )

同一商户可能重复显示多次。

[hay313955795]

连续三次在同一天消费的.那三个月内不是同一天消费的算吗?

[VensonEEE]

这个不是 sql 能解决的吧____

[zzzain46]

@hay313955795 不算

[liprais]

三个集合 join 是最简单的

[lookStupiToForce]

最快速的方法需要窗口函数，理论上扫一次表即可，不会回表二次查询，除非内存不够要写临时表，否则只剩内存计算

partition by 的条件根据需要的时间粒度去取
下面以需要{日-小时}一致为例

with cte_1 as
(select distinct user_id, 月, 日, 时 from table where ...), -- 在这里扫表一次
cte_2 as
(select user_id, 月, 日, 时,
lag(月, 1, null) over (partition by user_id, 日, 时 order by 月 asc) as last_月, -- 这里演示怎么抓到上一个相同 [日-小时] 记录的月份
case when lag(月, 1, null) over (partition by user_id, 日, 时 order by 月 asc) = 月 - 1 then true else false end as flag_1
case when lag(月, 2, null) over (partition by user_id, 日, 时 order by 月 asc) = 月 - 2 then true else false end as flag_2
from cte_1)
select * from cte_2 where flag_1 and flag_2;

如果需要{日-小时-分-秒}一致，
只需要把上面 distinct 后的和 partition by 后的 [日, 时] 替换成 [日, 时, 分, 秒] 即可

[lookStupiToForce]

@lookStupiToForce
补充一下，这里面的 [月] 得是 年月，就是类似这种 '2021-01' 包含年份形式的月份，否则会出问题

[oyasumi]

先把时间重复的过滤出来，再把日期重复（不包括月）的过滤出来，是不是数据量就少了

[zzzain46]

@liprais 流水量比较大，我想要的也是这个

[LemonK]

select a.cust_id, a.m, a.d from (select cust_id,month(trx_dte) as m, day(trx_dte) as d from table where trx_dte between '2022-01-01' and '2022-03-31' group by cust_id, m, d, trx_amt) as a group by a.cust_id, a.d HAVING count(a.m) = 3

外边再加层循环，每轮 between 窗口固定三个月，起始结束各增加一个月，把需要查的时段跑一轮。

[asmile1993]

-- 测试数据
drop table t;
create table t(
id int auto_increment primary key,
cust_id int,
trx_dte date,
trx_amt decimal(10, 2)
);

insert into t (cust_id, trx_dte, trx_amt) value(10086, '2022-01-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10086, '2022-02-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10086, '2022-03-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10086, '2022-04-05', 23.56);

insert into t (cust_id, trx_dte, trx_amt) value(10085, '2022-01-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10085, '2022-02-05', 23.56);

insert into t (cust_id, trx_dte, trx_amt) value(10010, '2021-01-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10010, '2021-02-05', 13.56);
insert into t (cust_id, trx_dte, trx_amt) value(10010, '2021-03-05', 33.56);
insert into t (cust_id, trx_dte, trx_amt) value(10010, '2021-04-05', 53.56);
insert into t (cust_id, trx_dte, trx_amt) value(10010, '2021-05-05', 23.56);

insert into t (cust_id, trx_dte, trx_amt) value(10020, '2021-01-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10020, '2021-02-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10020, '2021-04-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10020, '2021-05-05', 23.56);
insert into t (cust_id, trx_dte, trx_amt) value(10020, '2021-07-05', 23.56);



-- 递归调用生成 '2021-05-01' 到 '2022-05-30' 的日期表
with recursive Date_Ranges AS
(
select '2021-05-01' as Date
union all
select Date + interval 1 day
from Date_Ranges
where Date < '2022-05-30'
),
-- 生成月份相连，日期相同并且连续三个月的日期数组，例如：["2021-01-05", "2021-02-05", "2021-03-05"]
-- 注意这里限制了日期，因为过了 '2022-03-30' 后，就不再满足数据的过滤条件
date_array_ranges as (
select JSON_ARRAY(date, date_add(date, interval 1 month), date_add(date, interval 2 month)) json_array_trx_dte
from date_ranges
where date <= '2022-03-30'
),
-- 根据 cust_id ，trx_amt 进行聚合，并生成用户相同，金额相同的交易日期 json 数组
cust_trx_amt_dte_array as
(
select cust_id, trx_amt, JSON_ARRAYAGG(trx_dte) json_array_trx_dte
from t
group by cust_id, trx_amt
)
-- 由于是连续三个月，那么交易日期的 json 数组的数量肯定是大于等于 3
-- 在满足上述条件后，进一步判断交易日期 json 数组是否包含连续三个月日期相同的元素
-- 这里用 exists 来判断是为了避免数据重复
select *
from cust_trx_amt_dte_array a
where json_length(a.json_array_trx_dte) >= 3
and exists (select 1
from date_array_ranges b
where json_contains(a.json_array_trx_dte->'$', b.json_array_trx_dte)
);

-- 返回结果
+-----------+---------+----------------------------------------------------------+
| cust_id | trx_amt | json_array_trx_dte |
+-----------+---------+----------------------------------------------------------+
| 10086 | 23.56 | ["2022-01-05", "2022-02-05", "2022-03-05", "2022-04-05"] |
+---------------------+----------------------------------------------------------+

[asmile1993]

@lookStupiToForce
一天进行多次交易或一个月多次交易还能生效吗？ lag/lead 只是取前一条记录和后一条记录的值而已，并不能确保取得是上一个月和下一个月

[lookStupiToForce]

@asmile1993
你注意看，我那段 sql 最开始的 cte 里有 distinct （或者你用 group by 也可以），已经按照所需的时间粒度去重了，所以肯定是拿的是排序后之前月份的数，已去重了不可能拿到当前月份

[asmile1993]

@lookStupiToForce 为什么不可以，1 个 cust_id 又没限定一天只能交易一次，一天交易多次，你 lag order by 取的就不是下个月了吧，而是下一次交易的值

[lookStupiToForce]

@asmile1993 亲，你看懂了先好不？
cte_1 里 distinct 已经按照 [user_id ，月，日，时] 去重了，cte_2 里的 partition by 用的 [user_id ，日，时] ，两者只相差 [月] ，所以 lag 只能取到不同的月，懂了不？
还不懂也不用回我了，你自个儿测试去

[thinkingbullet]

@lookStupiToForce 老哥你的 sql 咋运行,小弟愚钝,还请指教 https://pic.imgdb.cn/item/628ee2a809475431298985c2.png

[lookStupiToForce]

@thinkingbullet #17 我咋感觉你回错人了，我没有用到 recursive cte ，你是不是要找 @asmile1993
你如果要用我那方法，回头有时间我用 pgsql 版本的测试一下，把通过的 sql 发出来，你再看着改算了

[asmile1993]

@thinkingbullet MySQL 版本得是 8.0 的