如何基于起止日期计算时间轴上的累计经常性收入？

核心思路 #

要实现「任意时间点当前总收入」的需求，不能直接用默认的累计求和（running sum）——它会忽略订阅的结束日期持续累加。正确的做法是为每个日期单独计算当前处于服务期内的订阅金额总和，具体可通过两种方式实现：

方法一：使用Data Studio数据融合（适配非SQL数据源，如Google Sheets） #

1. 生成日期维度表

在Google Sheets中创建连续日期序列，覆盖你的最早服务开始日期到最晚服务结束日期：

• 单元格A1输入最早日期（如2020-01-01），单元格B1输入最晚日期（如2023-12-31）
• 单元格C1输入公式生成连续日期：=SEQUENCE(DATEDIF(A1,B1,"D")+1,1,A1)
• 将该表命名为「日期维度表」并同步到Data Studio

2. 添加常量关联键

在原始订阅数据表和日期维度表中，各新增一个字段关联键，固定值设为1（用于实现交叉连接，让每个日期匹配所有订阅记录）

3. 数据融合

在Data Studio中创建新的数据融合：

• 选择「原始订阅表」和「日期维度表」作为两个数据源
• 关联条件设置为「原始订阅表.关联键 = 日期维度表.关联键」

4. 创建有效金额计算字段

在融合后的数据源中，创建计算字段有效金额：

CASE
  WHEN PARSE_DATE("%Y年%m月%d日", 服务开始日期) <= 日期维度表.日期 
       AND PARSE_DATE("%Y年%m月%d日", 服务结束日期) >= 日期维度表.日期
  THEN CAST(REGEXP_REPLACE(订阅金额, "[^0-9.]", "") AS NUMBER)
  ELSE 0
END

5. 构建时间图

• X轴选择「日期维度表.日期」
• Y轴选择「有效金额」的总和（不要选累计求和，直接用默认的SUM）

方法二：用SQL预处理数据（适配BigQuery等SQL数据源） #

如果数据存储在BigQuery中，可直接用SQL生成每个日期的有效收入，再导入Data Studio：

WITH 日期序列 AS (
  SELECT DATE_ADD('2020-01-01', INTERVAL 序号 DAY) AS 统计日期
  FROM UNNEST(GENERATE_ARRAY(0, DATEDIFF('2023-12-31', '2020-01-01'))) AS 序号
),
订阅数据预处理 AS (
  SELECT 
    账户,
    PARSE_DATE("%Y年%m月%d日", 服务开始日期) AS 开始日期,
    PARSE_DATE("%Y年%m月%d日", 服务结束日期) AS 结束日期,
    CAST(REGEXP_REPLACE(订阅金额, "[^0-9.]", "") AS FLOAT64) AS 订阅金额
  FROM `你的项目.你的数据集.你的表`
)
SELECT
  统计日期,
  SUM(CASE WHEN 开始日期 <= 统计日期 AND 结束日期 >= 统计日期 THEN 订阅金额 ELSE 0 END) AS 当前经常性收入
FROM 日期序列, 订阅数据预处理
GROUP BY 统计日期
ORDER BY 统计日期

将查询结果作为Data Studio数据源，直接创建时间图即可。

效果验证 #

用上述方法计算后，结果会完全匹配你的预期值：比如2021年2月1日，Account 002的旧订阅已过期，仅计算新续订的$2500，加上其他有效订阅金额后总和为$7000，符合需求。

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