如何实现非零起始值的SQL时间维度累计计数

一、SQL实现方案 #

核心思路是先补全所有连续日期（包括无销售记录的日期），统计每日销售数量后，通过窗口函数计算累计和再加上初始值50。以MySQL为例：

WITH date_range AS (
    -- 获取数据中的首尾日期，生成连续日期序列
    SELECT MIN(Sale_Date) AS start_date, MAX(Sale_Date) AS end_date
    FROM sales
    UNION ALL
    SELECT DATE_ADD(start_date, INTERVAL 1 DAY), end_date
    FROM date_range
    WHERE start_date < end_date
),
daily_counts AS (
    -- 统计每日销售数量，无销售的日期计数为0
    SELECT dr.start_date AS Sale_Date, COUNT(s.Rep) AS daily_count
    FROM date_range dr
    LEFT JOIN sales s ON dr.start_date = s.Sale_Date
    GROUP BY dr.start_date
)
-- 计算累计和并加上初始值50
SELECT 
    Sale_Date,
    50 + SUM(daily_count) OVER (ORDER BY Sale_Date) AS Count
FROM daily_counts
ORDER BY Sale_Date;

二、Python Pandas实现方案 #

通过补全连续日期索引，统计每日销售数后计算累计和，再叠加初始值：

import pandas as pd

# 原始数据
data = {
    'Rep': ['Bill', 'Jim', 'Bob'],
    'Sale Date': ['6/1/24', '6/1/24', '6/3/24']
}
df = pd.DataFrame(data)

# 转换日期格式为可处理的datetime类型
df['Sale Date'] = pd.to_datetime(df['Sale Date'], format='%m/%d/%y')

# 生成覆盖首尾日期的连续日期序列
date_min = df['Sale Date'].min()
date_max = df['Sale Date'].max()
date_range = pd.date_range(start=date_min, end=date_max)

# 统计每日销售数，补全缺失日期并填充0
daily_counts = df.groupby('Sale Date').size().reindex(date_range, fill_value=0)

# 计算累计计数并加上初始值50
result = pd.DataFrame({
    'Sale Date': daily_counts.index.strftime('%m/%d/%y'),
    'Count': 50 + daily_counts.cumsum()
})

print(result)

运行后会输出符合预期的结果：

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