如何按月份分组统计商品均价，且按有效日期范围复用每行数据？

现有两张业务表：
locations表（门店信息）：

id | name | prices_checked_at
1 | "los angeles" | 2023-02-02 00:00:01
2 | "new york" | 2023-02-02 00:00:01

items表（商品价格记录）：

id | name | price | location_id | updated_at
1 | "burger" | 2.90 | 1 | 2023-01-01 00:00:01
2 | "burger" | 3.45 | 2 | 2022-11-01 00:00:01

现在要统计名称为burger的商品月均价格，但直接用updated_at按月份分组有问题：每个价格的有效期不是仅updated_at所在月份，而是从updated_at持续到对应门店的prices_checked_at。比如item 1的2.90美元，有效期覆盖2023年1月全月和2023年2月前2天，需要让这条价格数据参与这两个月份的均价计算。预期输出如下：

month | price
2022-11 | 3.45
2022-12 | 3.45
2023-01 | 3.18
2023-02 | 3.18

核心思路就是先把所有需要统计的月份列出来，再计算每个价格在对应月份的有效天数，最后用加权平均的方式算出月均价。下面以PostgreSQL为例给出实现代码：

-- 生成所有要统计的月份起始日期
WITH months AS (
    SELECT generate_series(
        DATE_TRUNC('month', MIN(updated_at)),
        DATE_TRUNC('month', MAX(prices_checked_at)),
        INTERVAL '1 month'
    ) AS month_start
),
-- 关联表获取每个burger价格的有效时间区间
price_ranges AS (
    SELECT
        i.price,
        i.updated_at AS range_start,
        l.prices_checked_at AS range_end
    FROM items i
    JOIN locations l ON i.location_id = l.id
    WHERE i.name = 'burger'
),
-- 计算每个价格在每个月份的有效天数
price_month_days AS (
    SELECT
        TO_CHAR(m.month_start, 'YYYY-MM') AS month,
        pr.price,
        -- 取价格区间和月份区间的交集，计算重叠天数
        GREATEST(
            0,
            DATE_PART(
                'day',
                LEAST(pr.range_end, m.month_start + INTERVAL '1 month')
                - GREATEST(pr.range_start, m.month_start)
            )
        ) AS valid_days
    FROM months m
    CROSS JOIN price_ranges pr
    -- 过滤掉完全不重叠的情况
    WHERE pr.range_start < m.month_start + INTERVAL '1 month'
      AND pr.range_end > m.month_start
)
-- 按月份做加权平均计算均价
SELECT
    month,
    ROUND(SUM(price * valid_days) / SUM(valid_days), 2) AS price
FROM price_month_days
GROUP BY month
ORDER BY month;

逻辑拆解 #

1. 生成月份序列：用generate_series自动生成从最早的商品价格更新时间到最晚的门店价格检查时间的所有月份，确保不会漏统计任何涉及的月份。
2. 获取价格有效区间：把商品表和门店表关联，拿到每个burger价格的生效起始点（updated_at）和结束点（门店的prices_checked_at）。
3. 计算有效天数：对每个月份，判断价格区间和月份区间是否重叠，如果有重叠就算出重叠的天数——比如2023年2月，item1的价格只在前2天有效，有效天数就是2天。
4. 加权算均价：每个月份的均价不是简单的价格平均，而是用“价格×有效天数”的总和，除以该月份所有价格的有效天数总和，这样能体现每个价格在当月的影响权重。

其他数据库适配 #

如果用MySQL，可以用递归CTE来生成月份序列替代generate_series；SQL Server则用DATEADD结合递归生成月份。核心逻辑完全一致，都是先搭月份框架，再算每个价格的有效权重，最后加权平均。

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