Amazon Redshift SQL查询：统计快照表中客户从0到1的状态转换次数

嘿，我来帮你理清这个逻辑！其实核心思路就是追踪每个客户的状态变化序列，找出从0到1的每一次切换，用Redshift支持的窗口函数就能轻松实现。

第一步：先明确数据结构假设 #

我先假设你的快照表大概长这样（如果实际结构不同，你对应调整字段名就行）：

CREATE TABLE customer_snapshots (
    customer_id INT,
    status INT, -- 0=不满足条件，1=满足条件
    snapshot_timestamp TIMESTAMP -- 快照的时间戳，用来按时间排序状态序列
);

第二步：用窗口函数获取上一次的状态 #

我们需要按customer_id分组，按snapshot_timestamp排序，然后用LAG()窗口函数拿到每个客户上一次快照的状态——这是追踪状态转换的关键：

SELECT
    customer_id,
    status,
    snapshot_timestamp,
    -- 同一客户内，按时间排序后获取上一行的状态
    LAG(status) OVER (PARTITION BY customer_id ORDER BY snapshot_timestamp) AS previous_status
FROM customer_snapshots;

举个例子，如果某客户的状态序列是0→1→0→1，那么对应的previous_status就是NULL→0→1→0，这样我们就能清晰看到每一次状态的前后变化。

第三步：统计0→1的转换次数 #

接下来，我们只需要筛选出当前状态是1，且上一个状态是0的行，再按客户分组统计这些行的数量，就是该客户从“不满足”到“满足”的转换次数：

SELECT
    customer_id,
    COUNT(*) AS zero_to_one_conversion_count
FROM (
    SELECT
        customer_id,
        status,
        LAG(status) OVER (PARTITION BY customer_id ORDER BY snapshot_timestamp) AS previous_status
    FROM customer_snapshots
) AS status_changes
WHERE status = 1 AND previous_status = 0
GROUP BY customer_id
ORDER BY customer_id;

特殊情况的处理说明 #

• 如果某个客户第一次快照就是状态1，previous_status会是NULL，这不会被统计，完全符合逻辑（毕竟没有从0转换过来的过程）。
• 如果出现连续的1（比如0→1→1），只会统计第一次从0到1的转换，后面的连续1不会重复计数——这也合理，因为连续相同状态不算“转换”。
• 如果需要统计所有客户的总转换次数，去掉GROUP BY customer_id和customer_id字段，直接COUNT(*)即可。

验证示例 #

比如有如下测试数据：

运行上面的查询会得到：

完全符合预期的转换次数统计！

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