如何避免重复SELECT与UNION ALL实现QuickSight所需数据结果？

当前在Amazon QuickSight中为满足透视表数据组织需求，使用多段重复的UNION ALL SQL实现，当需要处理的problem字段增多时，SQL会变得冗长且维护成本高——各子查询仅problem名称和对应的计数逻辑不同，过滤条件、分组逻辑完全重复。

目标透视表效果 #

最终结果可按brand_group拆分为三个子表格：

优化方案 #

核心思路是抽离重复逻辑为基础子查询，通过横向展开问题项的方式一次性计算所有计数，避免多次扫描原表和重复编写过滤条件。

通用优化SQL（适用于多数SQL兼容数据库）

WITH base_data AS (
    -- 抽离所有重复的过滤、brand_group计算逻辑
    SELECT 
        CASE
            WHEN descriptor = 'Fast Food' THEN 'Base'
            WHEN descriptor IN ('Sit Down','Bar','Eatery') THEN 'Premium'
            WHEN descriptor IN ('Full Service','Boutique') THEN 'Deluxe'
            ELSE NULL
        END AS brand_group,
        accuracy_of_bill_yn,
        cleanliness_yn,
        service_yn,
        value_yn
    FROM surveys
    WHERE responsedate >= CURRENT_DATE - INTERVAL '12 months'
      AND surveyid NOT IN (SELECT surveyid FROM excluded)
      AND region IN (1,2,3,4,5)
),
problem_definitions AS (
    -- 定义所有问题名称和对应的标志字段映射
    SELECT 'Accuracy of bill' AS problem, accuracy_of_bill_yn AS flag FROM base_data UNION ALL
    SELECT 'Cleanliness' AS problem, cleanliness_yn AS flag FROM base_data UNION ALL
    SELECT 'Service' AS problem, service_yn AS flag FROM base_data UNION ALL
    SELECT 'Value' AS problem, value_yn AS flag FROM base_data
)
SELECT 
    bd.brand_group,
    pd.problem,
    COUNT(CASE WHEN pd.flag = 'Yes' THEN 1 END) AS problem_count
FROM base_data bd
JOIN problem_definitions pd ON TRUE
WHERE bd.brand_group IS NOT NULL
GROUP BY bd.brand_group, pd.problem
ORDER BY bd.brand_group, pd.problem;

更简洁的数组展开方案（支持数组/UNNEST的数据库，如PostgreSQL、Redshift）

如果使用的数据库支持数组和UNNEST函数，可进一步简化：

WITH base_data AS (
    SELECT 
        CASE
            WHEN descriptor = 'Fast Food' THEN 'Base'
            WHEN descriptor IN ('Sit Down','Bar','Eatery') THEN 'Premium'
            WHEN descriptor IN ('Full Service','Boutique') THEN 'Deluxe'
            ELSE NULL
        END AS brand_group,
        -- 将问题和对应标志字段打包为数组
        ARRAY[
            ('Accuracy of bill', accuracy_of_bill_yn),
            ('Cleanliness', cleanliness_yn),
            ('Service', service_yn),
            ('Value', value_yn)
        ] AS problem_flags
    FROM surveys
    WHERE responsedate >= CURRENT_DATE - INTERVAL '12 months'
      AND surveyid NOT IN (SELECT surveyid FROM excluded)
      AND region IN (1,2,3,4,5)
)
SELECT 
    brand_group,
    (unnest(problem_flags)).f1 AS problem,
    COUNT(CASE WHEN (unnest(problem_flags)).f2 = 'Yes' THEN 1 END) AS problem_count
FROM base_data
WHERE brand_group IS NOT NULL
GROUP BY brand_group, (unnest(problem_flags)).f1
ORDER BY brand_group, (unnest(problem_flags)).f1;

优化优势 #

• 重复逻辑仅编写一次，后续新增problem字段时，只需在problem_definitions或数组中添加对应项即可
• 原方案扫描4次原表，优化后仅扫描1次，提升查询性能
• 代码结构更清晰，降低维护和出错成本

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