如何在SQL已分组表中查询各区域财月最优渠道数据？

假设你的原查询结果包含字段：date_col（日期）、region（区域）、channel（渠道）、total_upgrades（总升级数）、total_disconnect（总断开数），可以通过窗口函数+CTE的方式实现需求，具体SQL如下：

-- 第一步：将原查询结果按财月、区域、渠道汇总（如果原查询已是该粒度，可跳过此CTE）
WITH aggregated_data AS (
    SELECT
        -- 替换为你的财月计算逻辑，示例：财年从7月开始，格式为FY24-Q1
        TO_CHAR(date_trunc('month', date_col) + interval '6 months', '"FY"YY-"Q"Q') AS fiscal_month,
        region,
        channel,
        SUM(total_upgrades) AS total_upgrades,
        SUM(total_disconnect) AS total_disconnect
    FROM (
        -- 此处替换为你的原查询语句
        SELECT date_col, region, channel, total_upgrades, total_disconnect
        FROM your_original_table
        -- 原查询的过滤条件（如有）
    ) original_data
    GROUP BY fiscal_month, region, channel
),
-- 第二步：筛选每个财月+区域内升级数最高的渠道
top_upgrade AS (
    SELECT
        fiscal_month,
        region,
        channel AS top_upgrade_channel,
        total_upgrades AS max_upgrades,
        RANK() OVER (PARTITION BY fiscal_month, region ORDER BY total_upgrades DESC) AS upgrade_rank
    FROM aggregated_data
),
-- 第三步：筛选每个财月+区域内断开数最高的渠道
top_disconnect AS (
    SELECT
        fiscal_month,
        region,
        channel AS top_disconnect_channel,
        total_disconnect AS max_disconnects,
        RANK() OVER (PARTITION BY fiscal_month, region ORDER BY total_disconnect DESC) AS disconnect_rank
    FROM aggregated_data
)
-- 合并结果，仅保留排名第一的记录
SELECT
    tu.fiscal_month,
    tu.region,
    tu.top_upgrade_channel,
    tu.max_upgrades,
    td.top_disconnect_channel,
    td.max_disconnects
FROM top_upgrade tu
JOIN top_disconnect td
    ON tu.fiscal_month = td.fiscal_month
    AND tu.region = td.region
WHERE tu.upgrade_rank = 1
    AND td.disconnect_rank = 1
ORDER BY tu.fiscal_month, tu.region;

关键说明 #

• 财月计算：根据你的实际财年规则调整fiscal_month的生成逻辑，比如自然月财月可改为TO_CHAR(date_col, 'YYYY-MM')。
• 并列处理：用RANK()而非ROW_NUMBER()，是因为如果多个渠道的升级/断开数并列最高，RANK()会给它们相同的排名（均为1），避免遗漏；如果只需要任意一个最高渠道，可替换为ROW_NUMBER()。
• 多渠道并列显示：如果需要将并列的渠道合并为一个字符串（比如渠道A,渠道B），可以修改top_upgrade和top_disconnect的逻辑，用STRING_AGG聚合：

top_upgrade AS (
    SELECT
        fiscal_month,
        region,
        STRING_AGG(channel, ', ') AS top_upgrade_channels,
        max_upgrades
    FROM (
        SELECT
            fiscal_month,
            region,
            channel,
            total_upgrades,
            MAX(total_upgrades) OVER (PARTITION BY fiscal_month, region) AS max_upgrades
        FROM aggregated_data
    ) sub
    WHERE total_upgrades = max_upgrades
    GROUP BY fiscal_month, region, max_upgrades
)

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