如何编写SQL查询按双优先级条件筛选Top 100万客户

原SQL的问题分析 #

你的原始查询存在三个关键问题：

• 无排序逻辑：完全没有按照要求的优先级（条件2>条件1）排序，返回的结果是无序的，根本无法筛选出符合规则的Top客户。
• 语法错误：AND Criterion2 ('A','B','C','D','E') 缺少IN关键字，正确写法是AND Criterion2 IN ('A','B','C','D','E')。
• 截断分组：直接用LIMIT 1000000会生硬截断结果，无法保证包含第100万客户所在的完整分组。

需求核心回顾 #

• 优先级：条件2（A-E，A最优）> 条件1（1-5，1最优），即A组所有客户优先级高于B组所有客户（哪怕A组的5级客户也比B组的1级客户优）。
• 结果要求：返回前100万客户，且必须包含第100万客户所在分组的全部客户（允许结果略超100万）。

高效可行的SQL方案 #

针对1000万级别的数据，推荐先分组统计再筛选的方式，性能更优：

WITH group_counts AS (
    -- 统计每个（条件2,条件1）分组的客户数量，并按优先级排序
    SELECT 
        Criterion2,
        Criterion1,
        COUNT(*) AS group_count
    FROM CUSTOMER_POPULATION
    WHERE Criterion1 IN (1,2,3,4,5)
      AND Criterion2 IN ('A','B','C','D','E')
    GROUP BY Criterion2, Criterion1
    ORDER BY Criterion2 ASC, Criterion1 ASC  -- A在前，同组内1在前
),
cumulative_groups AS (
    -- 计算到当前分组为止的累计客户总数
    SELECT 
        *,
        SUM(group_count) OVER (ORDER BY Criterion2 ASC, Criterion1 ASC) AS cumulative_total
    FROM group_counts
),
selected_groups AS (
    -- 筛选需要保留的分组：所有累计数≤100万的分组 + 第一个累计数超100万的分组
    SELECT Criterion2, Criterion1
    FROM cumulative_groups
    WHERE cumulative_total <= 1000000
    UNION ALL
    SELECT Criterion2, Criterion1
    FROM cumulative_groups
    WHERE cumulative_total > 1000000
    ORDER BY Criterion2 ASC, Criterion1 ASC
    LIMIT 1
)
-- 取出所有选中分组的客户，并按优先级排序
SELECT cp.*
FROM CUSTOMER_POPULATION cp
JOIN selected_groups sg 
    ON cp.Criterion2 = sg.Criterion2 
    AND cp.Criterion1 = sg.Criterion1
ORDER BY cp.Criterion2 ASC, cp.Criterion1 ASC;

方案说明 #

1. group_counts：先按优先级分组统计客户数，避免直接处理1000万行数据，提升性能。排序规则确保A组在前，同组内1级客户在前。
2. cumulative_groups：用窗口函数计算累计客户数，快速定位哪一个分组会跨过100万的阈值。
3. selected_groups：筛选出所有优先级更高的分组，加上那个跨过阈值的分组（必须完整保留）。
4. 最后关联原表，取出所有符合分组的客户并排序，得到符合要求的结果。

替代方案（逐行计算，适合小数据量） #

如果数据库性能足够，也可以用逐行累计的方式：

WITH sorted_customers AS (
    -- 按优先级排序所有符合条件的客户
    SELECT *
    FROM CUSTOMER_POPULATION
    WHERE Criterion1 IN (1,2,3,4,5)
      AND Criterion2 IN ('A','B','C','D','E')
    ORDER BY Criterion2 ASC, Criterion1 ASC
),
cumulative_counts AS (
    -- 计算每个客户的累计位置
    SELECT 
        *,
        SUM(1) OVER (ORDER BY Criterion2 ASC, Criterion1 ASC) AS running_total
    FROM sorted_customers
),
target_group AS (
    -- 找到第100万客户所在的分组
    SELECT Criterion2, Criterion1
    FROM cumulative_counts
    WHERE running_total >= 1000000
    LIMIT 1
)
-- 取出所有优先级≥目标分组的客户
SELECT sc.*
FROM sorted_customers sc
WHERE sc.Criterion2 < (SELECT Criterion2 FROM target_group)
   OR (sc.Criterion2 = (SELECT Criterion2 FROM target_group) AND sc.Criterion1 <= (SELECT Criterion1 FROM target_group))
ORDER BY sc.Criterion2 ASC, sc.Criterion1 ASC;

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