SQL语句编写：筛选流媒体观看时长持续递减的客户

嘿，我来帮你搞定这个需求！要找出那些每次观看流媒体的时长都比上一次短的客户，核心是先给每个客户的观看记录按时间排序，再验证每一条记录的时长是否都小于前一次。我准备了两种贴合不同场景的方案：

方案一：筛选所有观看时长持续递减的客户 #

这个方案会找出所有观看记录按时间排序后，每一次时长都比前一次短的客户（可以选择排除只有1次观看的用户）。

首先用CTE给每个客户的观看记录按时间编号，再通过NOT EXISTS检查是否存在任何一次观看时长不小于前一次的情况：

WITH customer_watches AS (
    SELECT 
        customer_Id,
        minutes_streamed,
        watch_date,
        -- 按客户分组，按观看时间排序生成观看顺序号
        ROW_NUMBER() OVER (PARTITION BY customer_Id ORDER BY watch_date) AS watch_order
    FROM Customer
)
SELECT DISTINCT c.customer_Id
FROM customer_watches c
-- 检查该客户没有任何一次观看时长 >= 前一次的记录
WHERE NOT EXISTS (
    SELECT 1
    FROM customer_watches prev_c
    WHERE prev_c.customer_Id = c.customer_Id
      AND prev_c.watch_order = c.watch_order - 1
      AND c.minutes_streamed >= prev_c.minutes_streamed
)
-- 可选：如果需要排除只有1次观看的客户，取消下面的注释
-- AND (SELECT COUNT(*) FROM customer_watches WHERE customer_Id = c.customer_Id) >= 2;

方案二：筛选至少有三次连续递减观看的客户 #

如果你的需求只是找出存在三次连续观看、且时长依次递减的客户（不需要所有观看都满足），可以用两次自连接的方式：

SELECT DISTINCT c1.customer_Id
FROM Customer c1
-- 关联第n-1次观看：时间在c1之后，时长更短
JOIN Customer c2 
    ON c1.customer_Id = c2.customer_Id 
    AND c2.watch_date > c1.watch_date
    AND c2.minutes_streamed < c1.minutes_streamed
-- 关联第n次观看：时间在c2之后，时长更短
JOIN Customer c3 
    ON c2.customer_Id = c3.customer_Id 
    AND c3.watch_date > c2.watch_date
    AND c3.minutes_streamed < c2.minutes_streamed;

小提示 #

• 注意替换watch_date为你实际表中记录观看时间的字段名（比如view_timestamp）；
• 如果表中有重复的观看时间，ROW_NUMBER()可能会分配相同的序号，这种情况可以改用RANK()或者结合其他字段（比如观看记录ID）来排序，保证顺序的唯一性。

内容的提问来源于stack exchange，提问作者I-ILI