Amazon QuickSight：基于分区与日期范围的Window-SUM计算需求

针对大型数据集，要计算每一行对应日期后7天内同一房屋的披萨食用总量，核心是利用窗口函数+范围条件实现，以下是主流SQL引擎的实现方案：

Spark SQL / BigQuery 实现 #

SELECT
  house_id,
  date,
  pizza_consumed,
  SUM(pizza_consumed) OVER (
    PARTITION BY house_id
    ORDER BY date
    RANGE BETWEEN CURRENT ROW AND INTERVAL 7 DAY FOLLOWING
  ) AS next_7d_total_pizza
FROM your_dataset

PostgreSQL 实现 #

SELECT
  house_id,
  date,
  pizza_consumed,
  SUM(pizza_consumed) OVER (
    PARTITION BY house_id
    ORDER BY date
    RANGE BETWEEN CURRENT ROW AND '7 days'::INTERVAL FOLLOWING
  ) AS next_7d_total_pizza
FROM your_dataset

关键逻辑说明 #

• PARTITION BY house_id：将数据按房屋分组，确保只计算同一房屋内的总量
• ORDER BY date：按日期排序，保证窗口范围的时间顺序正确
• RANGE BETWEEN CURRENT ROW AND INTERVAL 7 DAY FOLLOWING：定义窗口范围为当前行日期起往后7天，自动包含该范围内的所有行

大型数据集性能优化建议 #

• 在house_id和date字段上创建联合索引，加速分区与排序操作
• 提前按house_id对数据集进行分区存储（如Spark的分区表、BigQuery的分区字段），减少查询时的数据扫描范围
• 仅选择计算所需的字段，避免加载冗余数据

示例验证 #

假设原始数据：

计算后结果：

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