Presto按优先级统计多表关联数据时计数结果不符求助

原SQL的核心错误 #

1. 表名冲突导致逻辑完全偏离
   你把CTE命名为a，和原表A同名（Presto默认大小写不敏感），导致后续所有CTE里的from a都指向了第一个CTE的结果（也就是1st table的ID集合），而不是原表A。比如b子查询里的from a inner join second table，实际是从1st table的ID里找存在于2nd table的记录，这完全不符合你“从原表A中筛选属于2nd table且不属于1st table的记录”的需求。

2. 重复去重逻辑干扰计数
   except操作本身会自动去重，你在子查询里又加了distinct a.id，如果原表A里存在重复ID，会把同一ID的多条记录合并成一条，导致计数不准。

修正后的SQL方案 #

推荐用更直观的优先级判断法，直接给每条记录标记归属，再分组统计，避免绕弯：

方案1：用EXISTS判断优先级（逻辑清晰）

SELECT
  priority,
  COUNT(*) AS record_count
FROM (
  SELECT
    a.id,
    CASE
      -- 优先判断是否属于1st table
      WHEN EXISTS (SELECT 1 FROM first_table t1 WHERE t1.id = a.id) THEN '1st table'
      -- 不属于1st的情况下，判断是否属于2nd table
      WHEN EXISTS (SELECT 1 FROM second_table t2 WHERE t2.id = a.id) THEN '2nd table'
      -- 不属于前两个的情况下，判断是否属于3rd table
      WHEN EXISTS (SELECT 1 FROM third_table t3 WHERE t3.id = a.id) THEN '3rd table'
      -- 最后归属4th table
      ELSE '4th table'
    END AS priority
  FROM A a
) marked_records
GROUP BY priority;

方案2：用LEFT JOIN优化性能（适合大表）

如果原表数据量极大（1.2亿条），EXISTS可能性能不足，可以用LEFT JOIN加过滤的方式，减少子查询开销：

SELECT
  COALESCE(t1.priority, t2.priority, t3.priority, '4th table') AS priority,
  COUNT(*) AS record_count
FROM A a
-- 关联1st table，标记优先级
LEFT JOIN (SELECT id, '1st table' AS priority FROM first_table) t1 
  ON a.id = t1.id
-- 只关联那些不属于1st table的记录到2nd table
LEFT JOIN (SELECT id, '2nd table' AS priority FROM second_table) t2 
  ON a.id = t2.id AND t1.id IS NULL
-- 只关联那些不属于前两个表的记录到3rd table
LEFT JOIN (SELECT id, '3rd table' AS priority FROM third_table) t3 
  ON a.id = t3.id AND t1.id IS NULL AND t2.id IS NULL
GROUP BY COALESCE(t1.priority, t2.priority, t3.priority, '4th table');

验证逻辑 #

用你给的示例数据测试，这两个方案都会得到正确的统计结果：

• 1st table: 3条
• 2nd table: 1条
• 3rd table: 2条
• 4th table: 2条

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