Redshift中原始表无日汇总浏览量时如何计算30日滚动累计浏览量

嘿，我完全懂你的痛点——原始数据是一条条的浏览记录，没有现成的每日汇总，要算每篇文章每天往前推30天的累计浏览量确实得费点心思。我给你准备了两种最常用的解决方案，不管你是用数据库SQL处理，还是本地用Python Pandas跑数据，都能直接套用。

一、SQL 方案（适合数据库/数据仓库场景） #

假设你的表名叫page_views，核心字段是view_datetime（浏览发生的时间戳）和article_id（文章ID）。

思路拆解： #

1. 先把单条浏览记录按日期+文章ID聚合，算出每篇文章每天的浏览量——这一步能大幅减少后续滚动计算的数据量，效率更高
2. 用滚动时间窗口，对每篇文章的每日数据，计算过去30天（含当天）的总和

直接能用的代码： #

-- 先聚合出每篇文章的每日浏览量
WITH daily_article_views AS (
    SELECT
        DATE(view_datetime) AS view_date,
        article_id,
        COUNT(*) AS daily_views
    FROM page_views
    GROUP BY DATE(view_datetime), article_id
)
-- 计算每篇文章每日的过去30天累计浏览量
SELECT
    view_date,
    article_id,
    SUM(daily_views) OVER (
        PARTITION BY article_id
        ORDER BY view_date
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS rolling_30d_views
FROM daily_article_views
ORDER BY article_id, view_date;

小细节提醒： #

• 为啥用29 days？因为要包含当天，这样窗口就是当天+前29天，刚好30天。如果你的数据库不支持INTERVAL语法（比如MySQL），可以改成RANGE BETWEEN DATE_SUB(view_date, INTERVAL 29 DAY) AND view_date
• 如果某篇文章某天没有浏览记录，结果里不会出现那一行。要是需要补全所有日期（哪怕当天没浏览也显示0），可以先生成文章ID和所有日期的组合，再左连接上面的结果

二、Python Pandas 方案（适合本地处理数据集） #

假设你已经把数据读到了Pandas的DataFramedf里，包含view_datetime和article_id字段。

思路拆解： #

1. 先把时间戳转成日期格式，聚合出每日每篇文章的浏览量
2. 补全每个文章ID的所有日期（避免某天没数据导致滚动窗口计算错误）
3. 用滚动窗口计算过去30天的总和

代码示例： #

import pandas as pd

# 先确保时间字段是datetime类型
df['view_datetime'] = pd.to_datetime(df['view_datetime'])
df['view_date'] = df['view_datetime'].dt.date

# 第一步：聚合每日每篇文章的浏览量
daily_views = df.groupby(['view_date', 'article_id']).size().reset_index(name='daily_views')

# 第二步：补全所有日期（从数据的最早日期到最晚日期）
all_dates = pd.date_range(start=daily_views['view_date'].min(), end=daily_views['view_date'].max()).date
all_articles = daily_views['article_id'].unique()
full_index = pd.MultiIndex.from_product([all_dates, all_articles], names=['view_date', 'article_id'])
daily_views_full = daily_views.set_index(['view_date', 'article_id']).reindex(full_index, fill_value=0).reset_index()

# 第三步：计算滚动30天的累计浏览量
daily_views_full['rolling_30d_views'] = daily_views_full.groupby('article_id').rolling(
    window=30, on='view_date', closed='right'
)['daily_views'].sum().reset_index(level=0, drop=True)

# 查看结果
print(daily_views_full[['view_date', 'article_id', 'rolling_30d_views']].head())

小细节提醒： #

• closed='right'表示窗口包含当前日期，刚好是过去30天（当天+前29天）
• 补全日期这一步千万别省！要是某篇文章某天没数据，不补全的话滚动窗口会少算天数，结果就不准了

额外小贴士 #

• 数据量特别大的话（比如百万级以上），优先用SQL在数据库端处理，比本地Python快太多
• 注意时区！如果view_datetime带时区，统计日期时一定要统一时区，不然日期划分会出错
• 要是你想统计“过去30天不含当天”，SQL里把窗口改成RANGE BETWEEN INTERVAL '30 days' PRECEDING AND INTERVAL '1 day' PRECEDING，Pandas里把closed='right'改成closed='left'就行

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