如何判断第n值小于前n-1值并筛选逐次减时的观影客户？

First, let’s assume we’re working with a standard SQL database (like PostgreSQL, MySQL 8+, SQL Server, etc.) that supports window functions—these are essential for analyzing sequential customer data. Let’s start by formalizing the sample table for clarity:

CREATE TABLE Orders (
    Order_id VARCHAR(2),
    customer_Id VARCHAR(2),
    purchaseDate DATE,
    movie_Id VARCHAR(2),
    minutesStreamed INT
);

INSERT INTO Orders VALUES
('01', 'C1', '2000-01-01', 'P1', 100),
('02', 'C2', '2002-01-01', 'P2', 90),
('03', 'C3', '2002-04-01', 'P3', 93),
('04', 'C4', '2003-04-01', 'P1', 99),
('05', 'C4', '2006-01-01', 'P2', 99),
('06', 'C1', '2006-05-01', 'P5', 89),
('07', 'C4', '2017-12-01', 'P5', 89),
('08', 'C3', '2018-03-03', 'P1', 145),
('09', 'C4', '2018-03-03', 'P6', 147);

1. How to Check if the nth Value is Less Than Previous Values #

First, we need to order each customer’s viewing history by purchaseDate to establish their sequence of orders. We’ll use window functions to compare each entry to prior ones.

Option A: Compare to the immediate previous (n-1th) value #

This checks if each subsequent viewing is shorter than the immediately prior one:

SELECT
    Order_id,
    customer_Id,
    purchaseDate,
    minutesStreamed,
    LAG(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate) AS prev_minutes,
    CASE
        WHEN LAG(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate) IS NULL THEN 'First order'
        WHEN minutesStreamed < LAG(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate) THEN 'Shorter than previous'
        ELSE 'Not shorter than previous'
    END AS comparison_result
FROM Orders
ORDER BY customer_Id, purchaseDate;

Option B: Compare to all previous n-1 values #

This verifies if the nth viewing is shorter than every prior viewing the customer had:

SELECT
    Order_id,
    customer_Id,
    purchaseDate,
    minutesStreamed,
    MAX(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_prev_minutes,
    CASE
        WHEN MAX(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) IS NULL THEN 'First order'
        WHEN minutesStreamed < MAX(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 'Shorter than all previous'
        ELSE 'Not shorter than all previous'
    END AS comparison_result
FROM Orders
ORDER BY customer_Id, purchaseDate;

2. Filter Customers Where Every Viewing is Shorter Than the Previous One #

We need customers where all subsequent viewings have a shorter duration than the immediate prior one. Here’s a straightforward approach:

Step-by-Step Query #

WITH customer_order_sequence AS (
    SELECT
        customer_Id,
        minutesStreamed,
        LAG(minutesStreamed) OVER (PARTITION BY customer_Id ORDER BY purchaseDate) AS prev_minutes
    FROM Orders
),
non_decreasing_customers AS (
    SELECT DISTINCT customer_Id
    FROM customer_order_sequence
    WHERE prev_minutes IS NOT NULL AND minutesStreamed >= prev_minutes
)
SELECT DISTINCT customer_Id
FROM Orders
WHERE customer_Id NOT IN (SELECT customer_Id FROM non_decreasing_customers);

Result Explanation #

Against your sample data:

• C1: 100 → 89 (both decreasing) → qualifies
• C2: Only one order → qualifies (no subsequent order to violate the rule)
• C3: 93 → 145 (longer) → excluded
• C4: 99 →99 (equal) → 89 →147 (longer) → excluded

The final result will be C1 and C2.

Alternative Approach Using Self-Join #

If window functions aren’t available, you can use a self-join:

SELECT DISTINCT o1.customer_Id
FROM Orders o1
LEFT JOIN Orders o2 ON o1.customer_Id = o2.customer_Id 
    AND o2.purchaseDate = (
        SELECT MIN(purchaseDate) 
        FROM Orders 
        WHERE customer_Id = o1.customer_Id AND purchaseDate > o1.purchaseDate
    )
GROUP BY o1.customer_Id
HAVING ALL(
    CASE 
        WHEN o2.minutesStreamed IS NOT NULL THEN o2.minutesStreamed < o1.minutesStreamed 
        ELSE TRUE 
    END
);

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