过滤过去 2 年每年至少处理 3 笔交易的客户 Presto/SQL

Question

我有一个名为cust_trans的客户交易表，其中客户进行的每笔交易都存储为一行。我有另一个名为visit_date的列，其中包含交易日期。我想过滤过去3年每年至少交易2次的客户。

数据如下所示

Id          visit_date    
----        ------        
1           01/01/2019     
1           01/02/2019     
1           01/01/2019      
1           02/01/2020      
1           02/01/2020      
1           03/01/2020      
1           03/01/2020      
2           01/02/2019 
3           02/04/2019     

我想知道过去两年每年至少访问3次的客户

即。我想要下面的输出。

id    
---       
1       

在客户表中，只有一个人在 2 年内至少访问了 3 次。

我尝试使用以下查询，但它只检查总访问量是否大于或等于 3

select id
from 
cust_scan
GROUP  by 
id
having count(visit_date) >= 3
and year(date(max(visit_date)))-year(date(min(visit_date))) >=2

我将不胜感激任何帮助、指导或建议

Answer 1

一种选择是生成一个不同id的列表，将其与过去两年交叉连接，然后使用left join的原始表。然后，您可以汇总以计算每个id每年的访问次数。最后一步是再次聚合，并使用having子句进行筛选

select i.id
from (
select i.id, y.yr, count(c.id) cnt
from (select distinct id from cust_scan) i
cross join (values 
(date_trunc('year', current_date)), 
(date_trunc('year', current_date) - interval '1' year) 
) as y(yr)
left join cust_scan c 
on  i.id = c.id
and c.visit_date >= y.yr
and c.visit_date <  y.yr + interval '1' year
group by i.id, y.yr
) t
group by i.id
having min(cnt) >= 3

另一种选择是使用两个相关的子查询：

select distinct id
from cust_scan c
where 
(
select count(*) 
from cust_scan c1 
where 
c1.id = c.id
and c1.visit_date >= date_trunc('year', current_date)
and c1.visit_date <  date_trunc('year', current_date) + interval '1' year
) >= 3
and (
select count(*) 
from cust_scan c1 
where 
c1.id = c.id
and c1.visit_date >= date_trunc('year', current_date) - interval '1' year
and c1.visit_date <  date_trunc('year', current_date)
) >= 3

Answer 2

我假设你的意思是日历年。 我想我会使用两个级别的聚合：

select ct.id
from (select ct.id, year(visit_date) as yyyy, count(*) as cnt
from cust_trans ct
where ct.visit_date >= '2019-01-01'  -- or whatever
group by ct.id
) ct
group by ct.id
having count(*) = 2 and  -- both year
min(cnt) >= 3;    -- at least three transactions

如果您想要最近两年的完整年份，只需更改子查询中的where子句即可。

你可以使用类似的想法 - 两个聚合 - 如果你想要相对于当前日期的过去两年。 这将是整整两年，而不是1年和本年度的一小部分。