如何在同表多行对比日期，筛选7天内多次交易的账户ID

需求说明 #

需要找出任意授权日期的7天窗口内有2次及以上交易的账户，返回这些账户对应的交易记录（仅保留符合条件的accountid的行）。

现有SQL代码 #

select 
  tr.dateauth,
  a.email,
  a.accountid,
  tr.transactionid

from
  userdata as ud
  join account as a on a.accountid = ud.accountid
  join crcc as cc on a.accountid = cc.accountid
  join trid as tr on tr.cardid = cc.cardid

where 
  tr.dateauth >= CURRENT_DATE - INTERVAL '7 day' 
  and ud.created >= CURRENT_DATE - INTERVAL '6 months' 

group by
  a.accountid,
  tr.dateauth,
  a.email,
  tr.transactionid

order by
  tr.date desc

示例数据与预期结果 #

以下是原始查询返回的结果，但正确结果应仅包含accountid为1256的行：

曾尝试的失败方案 #

曾在WHERE子句中添加EXISTS语句，但未达到预期效果：

where EXISTS(
  SELECT 1
    FROM accounts as t2
    join crcc as t3 on t2.accountid = t3.accountid
    join trid as t4 on t4.cardid = t3.cardid
      and a.accountid = t2.accountid
      AND tr.dateauthorized <> t4.dateauthorized
      AND tr.dateauthorized BETWEEN
               t4.dateauthorized - interval '7 day'
            AND
               t4.dateauthorized + interval '7 day'
      HAVING count(t2.accountid)>1
)

问题分析与正确解法 #

原EXISTS语句的问题在于：

• 未对内部查询分组就使用HAVING子句，语法逻辑错误；
• 关联条件写法易导致重复匹配，无法准确限定7天窗口内的交易次数。

可以使用窗口函数COUNT() OVER()高效实现需求，先计算每个账户在自身交易日期前后7天内的交易次数，再筛选出次数≥2的记录：

WITH account_transactions AS (
  SELECT 
    tr.dateauth,
    a.email,
    a.accountid,
    tr.transactionid,
    -- 统计当前账户在当前交易日期7天窗口内的交易总数
    COUNT(*) OVER (
      PARTITION BY a.accountid 
      ORDER BY tr.dateauth 
      RANGE BETWEEN INTERVAL '7 day' PRECEDING AND INTERVAL '7 day' FOLLOWING
    ) AS transaction_count
  FROM
    userdata as ud
    join account as a on a.accountid = ud.accountid
    join crcc as cc on a.accountid = cc.accountid
    join trid as tr on tr.cardid = cc.cardid
  WHERE 
    tr.dateauth >= CURRENT_DATE - INTERVAL '7 day' 
    AND ud.created >= CURRENT_DATE - INTERVAL '6 months'
)
SELECT dateauth, email, accountid, transactionid
FROM account_transactions
WHERE transaction_count >= 2
ORDER BY dateauth DESC;

这种方式通过窗口函数直接计算每个交易对应的7天窗口内交易次数，避免了低效自关联，逻辑清晰且性能更优。

内容的提问来源于stack exchange，提问作者CheeseItz