SQL实现季度数据按值平均拆分至对应月份的方法

核心需求 #

• 源表存储季度维度统计数据，需将粒度从季度拆分到自然月
• 单季度统计总值按平均规则分摊到季度覆盖的每个自然月，即单月值 = 季度值 / 季度包含月数（常规季度为3个月，直接三等分）

样例源表结构 #

源表共3个字段：

• start：季度起始日期，日期/时间戳类型
• end：季度截止日期，日期/时间戳类型
• value：季度统计数值，数值类型

样例数据如下：

实现逻辑 #

1. 通过递归公共表表达式（CTE）生成目标时间范围内的连续月度日期序列
2. 按时间范围关联季度表与月度序列，匹配每个季度覆盖的所有自然月
3. 用窗口函数按季度分区统计匹配到的月数，自动计算单月分摊值，无需硬编码除以3，可兼容非标准周期的季度数据自动均分

可运行参考代码 #

注：代码适配SQL Server语法，其他数据库可根据递归CTE、日期函数语法做对应调整

-- 临时测试表，实际使用替换为你的业务表即可
CREATE TABLE #your_tbl
    ("start_dt" timestamp, "end_dt" timestamp, "values" int)
;
    
INSERT INTO #your_tbl
    ("start_dt", "end_dt", "values")
VALUES
    ('2020-01-01 00:00:00', '2020-04-01 00:00:00', 114625),
    ('2020-04-01 00:00:00', '2020-07-01 00:00:00', 45216),
    ('2020-07-01 00:00:00', '2020-10-01 00:00:00', 513574)

-- 配置需要拆分的时间范围，若需处理全表数据可直接从源表取min(start_dt)、max(end_dt)赋值
DECLARE @datefrom datetime
DECLARE @dateto datetime
SET @datefrom='2020-01-01'
SET @dateto = '2020-10-01'

;WITH cte AS
(
-- 生成连续月度序列锚点
SELECT @datefrom as MyDate
UNION ALL
SELECT DATEADD(month,1,MyDate)
FROM cte
WHERE DATEADD(month,1,MyDate)<@dateto
),
combined AS (
  SELECT q.start_dt, q.end_dt, q.[values], m.MyDate as month_dt
  FROM #your_tbl q
  JOIN cte m
  -- 按时间范围准确匹配季度覆盖的自然月
  ON m.MyDate >= q.start_dt
  AND m.MyDate < q.end_dt
)
-- 计算单月分摊值
SELECT 
  start_dt,
  end_dt,
  month_dt,
  [values] as quarter_total_value,
  [values]/COUNT(1) OVER(PARTITION BY [start_dt], [end_dt]) as monthly_value
FROM combined

-- 清理测试临时表
DROP TABLE #your_tbl

返回结果说明 #

返回结果包含季度起止日期、对应月度日期、季度总值、单月分摊值四个字段，以上述测试数据为例，每个季度会返回3条月度记录，季度值自动三等分。

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