按月份统计截至每月初的买卖双方唯一组合数

这个需求确实很典型，核心是要抓住每个买卖组合的首次交易时间——只有首次出现的组合才会让累计数增长，之后的重复交易不会改变累计值。我来一步步帮你实现：

核心思路 #

1. 先找出每一对buyerid-sellerid第一次发生交易的月份，明确每个组合是从哪个月开始被计入累计的；
2. 生成你需要统计的所有月份序列（包括最早交易月份的前一个月，这样才能得到像jan 0这样的初始值）；
3. 对每个月份，统计首次交易时间早于该月初的组合数量，就是截至当月初的累计唯一组合数。

完整SQL实现（以PostgreSQL为例） #

WITH first_transactions AS (
    -- 步骤1：获取每个买卖组合的首次交易月份
    SELECT 
        buyerid,
        sellerid,
        DATE_TRUNC('month', MIN(transaction_date)) AS first_trans_month
    FROM transactions
    GROUP BY buyerid, sellerid
),
date_series AS (
    -- 步骤2：生成需要统计的月份序列，从最早交易月的前一个月开始
    SELECT 
        generate_series(
            (SELECT DATE_TRUNC('month', MIN(transaction_date)) - INTERVAL '1 month' FROM transactions),
            '2018-03-01'::DATE, -- 这里替换成你需要统计的最后一个月
            INTERVAL '1 month'
        ) AS month_start
)
-- 步骤3：计算每个月初的累计组合数
SELECT 
    TO_CHAR(month_start, 'mon') AS month,
    COUNT(first_trans_month) AS total_combinations_at_month_start
FROM date_series
LEFT JOIN first_transactions 
    ON first_trans_month < month_start
GROUP BY month_start
ORDER BY month_start;

适配MySQL的版本 #

如果用MySQL，需要把生成月份序列的部分换成递归CTE，其他逻辑一致：

WITH RECURSIVE first_transactions AS (
    SELECT 
        buyerid,
        sellerid,
        DATE_FORMAT(MIN(transaction_date), '%Y-%m-01') AS first_trans_month
    FROM transactions
    GROUP BY buyerid, sellerid
),
date_series AS (
    SELECT DATE_SUB(DATE_FORMAT(MIN(transaction_date), '%Y-%m-01'), INTERVAL 1 MONTH) AS month_start
    FROM transactions
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM date_series
    WHERE month_start < '2018-03-01' -- 替换为目标结束月份
)
SELECT 
    DATE_FORMAT(month_start, '%b') AS month,
    COUNT(first_trans_month) AS total_combinations_at_month_start
FROM date_series
LEFT JOIN first_transactions 
    ON STR_TO_DATE(first_trans_month, '%Y-%m-01') < month_start
GROUP BY month_start
ORDER BY month_start;

验证你的示例数据 #

用你提供的交易数据测试：

运行SQL后会得到完全符合你期望的结果：

补充说明 #

• 如果需要统计到更晚的月份，只需要调整日期序列的结束值即可；
• 这个方法的性能也不错，因为只需要先对买卖组合做一次分组聚合，之后的统计都是基于这个聚合结果，不会重复扫描原始交易数据。

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