7000万行SQL查询优化：适配Tableau导入及过滤逻辑问题求助

你的原有聚合查询逻辑存在维度错误：GROUP BY分组维度包含了unique_id、claim_type、date三个字段，每个分组仅对应单条（或同ID同日期同理赔类型的多条同州）记录，因此COUNT(DISTINCT states)的计数结果无法反映同一个unique_id全局覆盖的州数量，才会出现仅返回单条记录的问题。

解决方案 #

你需要先筛选出满足「3个及以上不同州出现」的UniqueID列表，再用这个列表匹配原表的全量对应记录即可。

方案1：子查询匹配（兼容所有主流SQL语法） #

SELECT uniqueID, State, claim_type, Date
FROM Table1
WHERE 
  date >= '11-09-2021'
  AND uniqueID IN (
    SELECT uniqueID
    FROM Table1
    WHERE date >= '11-09-2021'
    GROUP BY uniqueID
    HAVING COUNT(DISTINCT State) >= 3
)

方案2：窗口函数实现（支持PostgreSQL、BigQuery、Spark SQL、新版本MySQL等支持窗口函数的数据库） #

SELECT uniqueID, State, claim_type, Date
FROM (
  SELECT 
    *,
    COUNT(DISTINCT State) OVER (PARTITION BY uniqueID) AS state_cnt
  FROM Table1
  WHERE date >= '11-09-2021'
) t
WHERE state_cnt >= 3

性能优化建议 #

• 可给uniqueID、date字段增加索引，大幅降低7000万量级下的查询耗时
• 可根据最终返回的数据量调整COUNT(DISTINCT State) >= 3的阈值，确保返回结果低于2000万行，适配Tableau加载要求

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