SQL中GROUP BY+COUNT统计后如何用INNER JOIN匹配阶梯费率

核心思路 #

• 第一步先做基础聚合：从采购表中按「统计月份+采购来源」维度分组，计算每个分组下的交易笔数、总采购金额，得到月度统计中间结果。
• 第二步做非等值关联费率表：匹配同一来源下，所有「起始笔数小于等于当前分组交易笔数」的费率规则。
• 第三步筛选正确档位：阶梯费率的规则是“满足某档起始笔数后，直到下一档更高起始笔数之前都适用该档费率”，因此只需要取所有匹配规则中from_purchase值最大的那条，就是当前交易笔数对应的正确费率。
• 最后用匹配到的费率乘以总采购金额，算出总费用即可。

代码实现 #

以下是两种常用写法，兼容绝大多数关系型数据库：

写法1：通用兼容写法（支持所有SQL版本，无需窗口函数） #

-- 先聚合得到月度各来源的交易统计
WITH monthly_stats AS (
    SELECT
        DATE_FORMAT(`date`, '%Y-%m') AS `month`,
        source_name,
        COUNT(id) AS transaction_count,
        SUM(purchase_amount) AS total_purchase
    FROM purchase
    GROUP BY `month`, source_name
)
SELECT
    ms.`month`,
    ms.source_name,
    ms.transaction_count,
    c.fee,
    -- 费率如果是百分比单位就除以100，根据实际业务调整计算逻辑即可
    ROUND(ms.total_purchase * c.fee / 100, 2) AS total_fees
FROM monthly_stats ms
JOIN costs c 
    ON ms.source_name = c.source_name
    AND c.from_purchase <= ms.transaction_count
-- 过滤掉非最高档位的费率规则
WHERE NOT EXISTS (
    SELECT 1 
    FROM costs c2
    WHERE c2.source_name = c.source_name
      AND c2.from_purchase <= ms.transaction_count
      AND c2.from_purchase > c.from_purchase
);

写法2：窗口函数简化写法（支持MySQL8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH monthly_stats AS (
    SELECT
        DATE_FORMAT(`date`, '%Y-%m') AS `month`,
        source_name,
        COUNT(id) AS transaction_count,
        SUM(purchase_amount) AS total_purchase
    FROM purchase
    GROUP BY `month`, source_name
),
match_fee AS (
    SELECT
        ms.*,
        c.fee,
        -- 按来源、月份分组，起始笔数从高到低排序，排第一的就是正确档位
        ROW_NUMBER() OVER (
            PARTITION BY ms.`month`, ms.source_name
            ORDER BY c.from_purchase DESC
        ) AS rn
    FROM monthly_stats ms
    JOIN costs c 
      ON ms.source_name = c.source_name
      AND c.from_purchase <= ms.transaction_count
)
SELECT
    `month`,
    source_name,
    transaction_count,
    fee,
    ROUND(total_purchase * fee / 100, 2) AS total_fees
FROM match_fee
WHERE rn = 1;

注意事项 #

• 日期格式化函数可以根据你使用的数据库替换：SQL Server用FORMAT([date],'yyyy-MM')、Oracle用TO_CHAR(date,'YYYY-MM')，核心聚合和费率匹配逻辑不需要修改。
• 示例数据中存在2014-02-30这种非法日期，实际入库前需要做合法性校验，否则会导致月份统计出错。
• 现有costs表结构不需要调整，只要保证每个来源下的from_purchase按阶梯从小到大配置即可，不需要额外新增档位结束笔数字段，上述关联逻辑会自动匹配正确档位。

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