数仓建模中计算累计销量

在数仓建模中计算累计销量，通常需要结合时间维度和业务逻辑设计合理的模型与计算逻辑。以下是分步骤的实现思路和示例：

1. 模型设计

累计销量的计算通常基于星型模型或雪花模型，核心结构包括：

• 事实表：记录每一笔销售交易（如 sales_fact），包含以下字段：
  • product_id（产品ID）
  • sale_date（销售日期，关联日期维度表）
  • sale_amount（单笔销量）
• 日期维度表（dim_date）：提供日期粒度（如年、月、日等）。

2. 计算方法

方法1：窗口函数（实时计算）

直接在查询时使用窗口函数，按时间顺序累加销量。适用于小数据量或实时性要求高的场景。

SELECT
    product_id,
    sale_date,
    SUM(sale_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM sales_fact;

优点：灵活，无需预计算。
缺点：大数据量时性能较差。

方法2：预计算累计快照表

提前计算每日累计销量并存储到汇总表中（如 cumulative_sales_daily），适合高频查询。

步骤：

1. 创建日粒度汇总表：

   CREATE TABLE cumulative_sales_daily (
       product_id INT,
       sale_date DATE,
       daily_sales INT,
       cumulative_sales INT
   );
   

2. 每日增量更新：

   INSERT INTO cumulative_sales_daily
   SELECT
       product_id,
       sale_date,
       SUM(sale_amount) AS daily_sales,
       SUM(SUM(sale_amount)) OVER (
           PARTITION BY product_id
           ORDER BY sale_date
           ROWS UNBOUNDED PRECEDING
       ) AS cumulative_sales
   FROM sales_fact
   WHERE sale_date = '2023-10-01'  -- 按天增量更新
   GROUP BY product_id, sale_date;
   

优点：查询极快。
缺点：需要维护ETL任务。

方法3：物化视图

使用数据库的物化视图（Materialized View）自动维护累计销量。

CREATE MATERIALIZED VIEW mv_cumulative_sales
REFRESH ON COMMIT  -- 或定时刷新
AS
SELECT
    product_id,
    sale_date,
    SUM(sale_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
    ) AS cumulative_sales
FROM sales_fact;

优点：自动刷新，平衡性能与实时性。
缺点：依赖数据库特性，可能增加存储成本。

3. 处理多粒度需求

若需支持多种时间粒度（如按月累计）：

• 方案1：在日期维度表中添加 year_month 字段，按需聚合：

  SELECT
      product_id,
      d.year_month,
      SUM(sale_amount) AS monthly_sales,
      SUM(SUM(sale_amount)) OVER (
          PARTITION BY product_id
          ORDER BY d.year_month
      ) AS cumulative_sales
  FROM sales_fact f
  JOIN dim_date d ON f.sale_date = d.date_key
  GROUP BY product_id, d.year_month;
  

• 方案2：预建多张汇总表（如日、月、年累计）。

4. 性能优化建议

• 分区与索引：对事实表按时间分区，并为 product_id 和 sale_date 建立索引。
• 预聚合：针对常用查询提前计算中间结果（如每日销量）。
• 使用MPP数据库：如 Redshift、BigQuery，利用分布式计算加速窗口函数。

5. 示例场景

假设需要查询产品A在2023年10月的累计销量：

SELECT
    product_id,
    sale_date,
    cumulative_sales
FROM cumulative_sales_daily
WHERE product_id = 'A'
    AND sale_date BETWEEN '2023-10-01' AND '2023-10-31';

总结

• 小数据量/实时查询：优先用窗口函数。
• 大数据量/高频查询：预计算汇总表或物化视图。
• 多时间粒度：通过日期维度表灵活聚合。

根据业务需求和数据规模选择合适方案，并在计算效率与存储成本之间权衡。