按分类-子分类获取TOP3供应商及分类总计SQL查询需求

需求分析 #

需要统计每个分类、子分类的总支出，同时在每个子分类下找出总支出排名前三的供应商，剩余供应商的金额合并为“Others”，最终按层级展示统计结果。

数据库表结构说明 #

假设我们有expenses表，包含字段：category（分类）、subcategory（子分类）、vendor（供应商）、amount（支出金额）。

实现SQL查询 #

-- 1. 统计子分类下各供应商的总支出并排名
WITH vendor_stats AS (
    SELECT
        category,
        subcategory,
        vendor,
        SUM(amount) AS total_amount,
        RANK() OVER (PARTITION BY category, subcategory ORDER BY SUM(amount) DESC) AS rnk
    FROM expenses
    GROUP BY category, subcategory, vendor
),
-- 2. 合并TOP3和Others
vendor_agg AS (
    SELECT
        category,
        subcategory,
        vendor,
        total_amount
    FROM vendor_stats
    WHERE rnk <= 3
    UNION ALL
    SELECT
        category,
        subcategory,
        'Others' AS vendor,
        SUM(total_amount) AS total_amount
    FROM vendor_stats
    WHERE rnk > 3
    GROUP BY category, subcategory
),
-- 3. 计算子分类总金额
subcat_total AS (
    SELECT
        category,
        subcategory,
        SUM(total_amount) AS total_amount
    FROM vendor_agg
    GROUP BY category, subcategory
),
-- 4. 计算分类总金额
cat_total AS (
    SELECT
        category,
        SUM(total_amount) AS total_amount
    FROM subcat_total
    GROUP BY category
)
-- 5. 整合所有结果并按层级排序输出
SELECT
    CONCAT(
        CASE
            WHEN level = 1 THEN ''
            WHEN level = 2 THEN '    '
            WHEN level = 3 THEN '        '
        END,
        name, ' ', FORMAT(total_amount, 2)
    ) AS result
FROM (
    -- 分类层级
    SELECT category AS name, total_amount, 1 AS level, category, '' AS subcategory, '' AS vendor
    FROM cat_total
    UNION ALL
    -- 子分类层级
    SELECT subcategory AS name, total_amount, 2 AS level, category, subcategory, '' AS vendor
    FROM subcat_total
    UNION ALL
    -- 供应商层级
    SELECT vendor AS name, total_amount, 3 AS level, category, subcategory, vendor
    FROM vendor_agg
) AS combined
ORDER BY category, subcategory, level, 
    CASE WHEN vendor = 'Others' THEN 1 ELSE 0 END, 
    total_amount DESC;

代码解释 #

1. vendor_stats：用窗口函数RANK()给每个子分类下的供应商按总支出降序排名，方便筛选TOP3。
2. vendor_agg：将TOP3供应商数据与剩余供应商的合并金额（命名为Others）整合到同一数据集。
3. subcat_total/cat_total：分别计算子分类、分类的总支出，用于层级展示的汇总行。
4. 最终查询：通过UNION ALL整合分类、子分类、供应商三个层级的数据，用CASE语句添加缩进区分层级，排序时保证分类、子分类的顺序，同时让Others排在TOP3供应商之后。

预期输出 #

Paper 515.54
    Napkins 314.99
        Paper LLC 110.49
        Dunder Mifflin 70.99
        Brawny 60.02
        Others 73.49
    Towels 200.55
        Acme LLC 52.01
        Michigan Paper 41.23
        Cascades LLC 40.55
        Others 66.76
Food 205.54
    Apples 178.76
        Orchards LLC 97.10
        Northstar Orchard 41.23
        Apples LLC 40.43
    Pizza 26.78
        Dominos 16.53
        Pizza Hut 10.25

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