基于购物账户钱包余额统计资金从充值到耗尽的天数

问题描述 #

现有数据表包含customerId、date、storedvalue（客户钱包余额）三列，需统计客户钱包从充值到余额耗尽为0的间隔天数：

• 计算规则：2024-01-01充值、2024-01-05耗尽，天数计为4
• 余额清零后再次充值则重新开始统计，每次余额清零为一个统计周期节点

示例数据表 #

期望结果 #

错误脚本分析 #

你提供的原脚本仅筛选了storedvalue=0的行，无法获取每个周期的起始充值日期，导致分组逻辑失效，无法得到正确的周期间隔。

正确解决方案 #

以下SQL脚本通过标记周期编号，实现按周期统计间隔天数：

WITH cycle_marking AS (
    SELECT
        customerId,
        date,
        storedvalue,
        -- 标记每个记录所属的周期：累计当前行之前的余额清零次数
        SUM(CASE WHEN storedvalue = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY customerId 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS cycle_id
    FROM your_table_name
)
SELECT
    customerId,
    DATEDIFF(day, MIN(date), MAX(date)) AS `date diff`
FROM cycle_marking
-- 过滤掉无结束节点的周期（如果存在未清零的余额）
WHERE EXISTS (
    SELECT 1 
    FROM cycle_marking cm 
    WHERE cm.customerId = cycle_marking.customerId 
      AND cm.cycle_id = cycle_marking.cycle_id 
      AND cm.storedvalue = 0
)
GROUP BY customerId, cycle_id
ORDER BY customerId, MIN(date);

逻辑说明 #

1. 周期标记：使用窗口函数SUM(CASE...)，对每个客户按日期排序，累计当前行之前的余额清零次数，以此作为周期编号。每次清零后的所有记录都会被分配到同一个新周期。
2. 分组统计：按customerId和cycle_id分组，取每个周期的最小日期（充值起始日）和最大日期（余额清零日），计算两者的天数差。
3. 过滤无效周期：通过EXISTS子句过滤掉那些还未清零的周期（如果数据表中存在客户当前余额不为0的记录）。

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