优化SQL查询：筛选达标账户交易并统计双向交易次数

需求回顾 #

• 筛选有效交易：转出账户acct_sending和转入账户acct_receiving必须存在于accounts表，且两者的balance均大于指定阈值
• 统计双向交易总次数：将账户对(A,B)和(B,A)视为同一组，计算该组的交易总数

无临时表高效实现方案 #

方案1：直接JOIN+分组聚合（通用型） #

通过两次JOIN关联账户表过滤合规账户，再用LEAST()/GREATEST()标准化账户对，避免重复统计双向交易：

-- 替换1000为你的指定余额阈值，或改用参数（如@min_balance）
SELECT
    LEAST(t.acct_sending, t.acct_receiving) AS account_pair_first,
    GREATEST(t.acct_sending, t.acct_receiving) AS account_pair_second,
    COUNT(*) AS total_two_way_transactions
FROM transactions t
-- 关联转出账户，确保存在且余额达标
JOIN accounts send_acct 
  ON t.acct_sending = send_acct.acct_id 
  AND send_acct.balance > 1000
-- 关联转入账户，确保存在且余额达标
JOIN accounts recv_acct 
  ON t.acct_receiving = recv_acct.acct_id 
  AND recv_acct.balance > 1000
GROUP BY 
    LEAST(t.acct_sending, t.acct_receiving),
    GREATEST(t.acct_sending, t.acct_receiving);

如果需要保留每笔交易的明细，同时附加该账户对的总次数，去掉GROUP BY，改用窗口函数：

SELECT
    t.*,
    COUNT(*) OVER (
        PARTITION BY 
            LEAST(t.acct_sending, t.acct_receiving),
            GREATEST(t.acct_sending, t.acct_receiving)
    ) AS total_two_way_transactions
FROM transactions t
JOIN accounts send_acct 
  ON t.acct_sending = send_acct.acct_id 
  AND send_acct.balance > 1000
JOIN accounts recv_acct 
  ON t.acct_receiving = recv_acct.acct_id 
  AND recv_acct.balance > 1000;

方案2：CTE辅助过滤（适合复杂场景） #

如果业务逻辑需要分步处理，用CTE替代临时表，避免磁盘写入开销：

WITH valid_trans AS (
    SELECT t.*
    FROM transactions t
    JOIN accounts send_acct 
      ON t.acct_sending = send_acct.acct_id 
      AND send_acct.balance > 1000
    JOIN accounts recv_acct 
      ON t.acct_receiving = recv_acct.acct_id 
      AND recv_acct.balance > 1000
)
SELECT
    LEAST(acct_sending, acct_receiving) AS account_pair_first,
    GREATEST(acct_sending, acct_receiving) AS account_pair_second,
    COUNT(*) AS total_two_way_transactions
FROM valid_trans
GROUP BY 
    LEAST(acct_sending, acct_receiving),
    GREATEST(acct_sending, acct_receiving);

性能优化关键 #

• 索引加持：
  • 给accounts表建联合索引：CREATE INDEX idx_acct_id_balance ON accounts(acct_id, balance);（让JOIN和余额过滤一步完成）
  • 给transactions表建(acct_sending, acct_receiving)联合索引，加速关联和分组
• 避免冗余：用LEAST()/GREATEST()统一账户对顺序，不用额外逻辑区分双向交易
• 参数化：把余额阈值改成参数（如@min_balance），提升查询缓存命中率

为什么比临时表更优？ #

• 省掉临时表的磁盘写入和读取开销，所有计算可在内存中完成（取决于数据库内存配置）
• 数据库优化器能更好地优化JOIN和分组逻辑，生成更高效的执行计划
• 减少内存占用：仅处理符合条件的交易，不用存储全量临时数据

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