BigQuery预付费用户new_tarif_allownance计算逻辑实现求助

需求梳理 #

• 给定字段：subscriber_no、bill_start_date、bill_end_date、gift_given、gift_received、Data_allowed
• 派生字段ROW_NO：按用户分组、账单日期排序生成（row_number() over (partition by subscriber_no order by bill_start_date)）
• new_tarif_allownance规则：
  • 当gift_given或gift_received不为0时，取值为Data_allowed - gift_given + gift_received
  • 无礼品变动时，沿用同一用户上一行的计算值

实现代码 #

以下是结合示例数据的完整BigQuery SQL：

-- 示例数据替换为你的实际表即可
WITH sample_data AS (
  SELECT
    '1001' AS subscriber_no,
    DATE('2024-01-01') AS bill_start_date,
    DATE('2024-01-31') AS bill_end_date,
    0 AS gift_given,
    0 AS gift_received,
    10 AS Data_allowed
  UNION ALL
  SELECT
    '1001' AS subscriber_no,
    DATE('2024-02-01') AS bill_start_date,
    DATE('2024-02-29') AS bill_end_date,
    2 AS gift_given,
    1 AS gift_received,
    10 AS Data_allowed
  UNION ALL
  SELECT
    '1001' AS subscriber_no,
    DATE('2024-03-01') AS bill_start_date,
    DATE('2024-03-31') AS bill_end_date,
    0 AS gift_given,
    0 AS gift_received,
    10 AS Data_allowed
  UNION ALL
  SELECT
    '1002' AS subscriber_no,
    DATE('2024-01-01') AS bill_start_date,
    DATE('2024-01-31') AS bill_end_date,
    1 AS gift_given,
    0 AS gift_received,
    15 AS Data_allowed
  UNION ALL
  SELECT
    '1002' AS subscriber_no,
    DATE('2024-02-01') AS bill_start_date,
    DATE('2024-02-29') AS bill_end_date,
    0 AS gift_given,
    3 AS gift_received,
    15 AS Data_allowed
),
numbered_data AS (
  SELECT
    *,
    -- 按用户+账单日期生成ROW_NO
    ROW_NUMBER() OVER (PARTITION BY subscriber_no ORDER BY bill_start_date) AS ROW_NO,
    -- 仅在礼品变动时计算新值，第一行无变动时用Data_allowed作为初始值
    CASE 
      WHEN gift_given != 0 OR gift_received != 0 THEN Data_allowed - gift_given + gift_received
      WHEN ROW_NUMBER() OVER (PARTITION BY subscriber_no ORDER BY bill_start_date) = 1 THEN Data_allowed
    END AS reset_value,
    -- 按礼品变动点分组，同一组内共享最新计算值
    SUM(CASE WHEN gift_given != 0 OR gift_received != 0 THEN 1 ELSE 0 END) OVER (
      PARTITION BY subscriber_no ORDER BY bill_start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS reset_group
  FROM sample_data
)
SELECT
  subscriber_no,
  bill_start_date,
  bill_end_date,
  gift_given,
  gift_received,
  Data_allowed,
  ROW_NO,
  -- 取当前分组的初始计算值，实现"沿用上次值"逻辑
  FIRST_VALUE(reset_value) OVER (PARTITION BY subscriber_no, reset_group ORDER BY bill_start_date) AS new_tarif_allownance
FROM numbered_data
ORDER BY subscriber_no, bill_start_date;

逻辑说明 #

1. numbered_data阶段：

   • 生成用户维度的行序号ROW_NO，确保行顺序符合账单时间线
   • 标记reset_value：仅在礼品变动时计算新值，用户第一行无变动时默认用Data_allowed作为初始配额
   • 生成reset_group：通过累积求和，将每个用户的行按礼品变动点分割为连续分组，每个分组从一个新计算值的行开始

2. 最终查询阶段：

   • 用FIRST_VALUE()提取每个分组的初始计算值，让分组内所有行共享该值，实现无礼品变动时沿用上次计算值的需求

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