SQL查询实现：筛选与相同收款账户交互3次及以上的付款账户

核心判定逻辑：先找出两两之间共同交易过至少3个相同收款账户的付款账户集合，再提取这个集合里所有付款账户都发生过交易的收款账户组，最后返回两类账户交叉对应的全部原始交易记录即可。

分步实现逻辑 #

1. 交易关系去重 #

首先对ordering account（付款账户）和beneficiary account（收款账户）的配对做去重，避免同一对账户的多笔重复交易干扰共同收款账户的计数，只保留“是否发生过交易”的关系即可：

WITH dedup_relation AS (
    SELECT DISTINCT `ordering account`, `beneficiary account`
    FROM TBL_ACCOUNTS
)

2. 筛选符合共同收款数要求的付款账户对 #

将去重后的交易关系自连接，以相同收款账户为关联条件，统计每两个付款账户的共同交互收款账户数量，只保留共同收款数≥3的账户对，同时通过大小比较避免(A,B)、(B,A)这类重复配对：

, qualified_payer_pair AS (
    SELECT
        a.`ordering account` AS payer_a,
        b.`ordering account` AS payer_b,
        COUNT(DISTINCT a.`beneficiary account`) AS common_benefit_cnt
    FROM dedup_relation a
    JOIN dedup_relation b
      ON a.`beneficiary account` = b.`beneficiary account`
      AND a.`ordering account` < b.`ordering account`
    GROUP BY payer_a, payer_b
    HAVING common_benefit_cnt >= 3
)

3. 提取合格的付款账户、收款账户集合 #

• 把上一步得到的账户对里的所有付款账户去重，得到全部符合要求的付款账户列表
• 再反查这些付款账户的交易记录，筛选出被所有合格付款账户都交易过、且总数量≥3的收款账户列表

, valid_payer AS (
    SELECT payer_a AS payer FROM qualified_payer_pair
    UNION
    SELECT payer_b AS payer FROM qualified_payer_pair
),
valid_beneficiary AS (
    SELECT `beneficiary account` AS beneficiary
    FROM dedup_relation r
    JOIN valid_payer p ON r.`ordering account` = p.payer
    GROUP BY `beneficiary account`
    HAVING COUNT(DISTINCT r.`ordering account`) = (SELECT COUNT(*) FROM valid_payer)
       AND COUNT(*) >= 3
)

4. 拉取最终需要的全部交易记录 #

用得到的合格付款账户、合格收款账户关联原始交易表，就能返回所有符合要求的交易明细：

SELECT ori.*
FROM TBL_ACCOUNTS ori
JOIN valid_payer vp ON ori.`ordering account` = vp.payer
JOIN valid_beneficiary vb ON ori.`beneficiary account` = vb.beneficiary;

样例匹配验证 #

针对给出的测试场景：

• 付款账户A、B、C都和收款账户1、2、3发生过交易，两两配对的共同收款数都是3，会被全部纳入合格付款账户；收款账户1、2、3都被A/B/C三个账户交易过，会被纳入合格收款账户，最终返回三者交叉的全部9条记录
• H、K、Z、W这类仅和1个收款账户有交互的付款账户，无法和其他付款账户形成共同收款数≥3的配对，不会出现在最终结果里，完全匹配需求。

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