如何使用SQL验证分组内基于时间戳的列值顺序合规性？

Alright, let's break down how to solve this problem. You need to verify that for each group in colA, the timestamps follow the order colB='a' < colB='b' < colB='c' < colB='d' < colB='e'. Below are a few practical approaches depending on your specific scenario:

Approach 1: Conditional Aggregation (Single Record per colA+colB) #

If each combination of colA and colB has exactly one record, this method directly compares the timestamps for each colB value in the group:

SELECT 
    colA,
    -- Check if a's timestamp is before b's
    CASE WHEN MAX(CASE WHEN colB = 'a' THEN colC END) < MAX(CASE WHEN colB = 'b' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS a_before_b_check,
    -- Check if b's timestamp is before c's
    CASE WHEN MAX(CASE WHEN colB = 'b' THEN colC END) < MAX(CASE WHEN colB = 'c' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS b_before_c_check,
    -- Check if c's timestamp is before d's
    CASE WHEN MAX(CASE WHEN colB = 'c' THEN colC END) < MAX(CASE WHEN colB = 'd' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS c_before_d_check,
    -- Check if d's timestamp is before e's
    CASE WHEN MAX(CASE WHEN colB = 'd' THEN colC END) < MAX(CASE WHEN colB = 'e' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS d_before_e_check,
    -- Overall validation result
    CASE 
        WHEN MAX(CASE WHEN colB = 'a' THEN colC END) < MAX(CASE WHEN colB = 'b' THEN colC END)
             AND MAX(CASE WHEN colB = 'b' THEN colC END) < MAX(CASE WHEN colB = 'c' THEN colC END)
             AND MAX(CASE WHEN colB = 'c' THEN colC END) < MAX(CASE WHEN colB = 'd' THEN colC END)
             AND MAX(CASE WHEN colB = 'd' THEN colC END) < MAX(CASE WHEN colB = 'e' THEN colC END)
        THEN 'All checks passed'
        ELSE 'Some checks failed'
    END AS overall_validation
FROM tableA
GROUP BY colA;

Note: If a group is missing a colB value (e.g., no 'a' records), the comparison will return Fail by default. To handle missing values explicitly, add null checks to the CASE statements.

Approach 2: Conditional Aggregation (Multiple Records per colA+colB) #

If there are multiple records for the same colA and colB, use this method to ensure all records of a colB value are before the next one:

SELECT 
    colA,
    -- Ensure all a's latest timestamp is before all b's earliest timestamp
    CASE WHEN MAX(CASE WHEN colB = 'a' THEN colC END) < MIN(CASE WHEN colB = 'b' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS a_all_before_b_check,
    -- Ensure all b's latest timestamp is before all c's earliest timestamp
    CASE WHEN MAX(CASE WHEN colB = 'b' THEN colC END) < MIN(CASE WHEN colB = 'c' THEN colC END) THEN 'Pass' ELSE 'Fail' END AS b_all_before_c_check,
    -- Repeat for c→d and d→e
    CASE 
        WHEN MAX(CASE WHEN colB = 'a' THEN colC END) < MIN(CASE WHEN colB = 'b' THEN colC END)
             AND MAX(CASE WHEN colB = 'b' THEN colC END) < MIN(CASE WHEN colB = 'c' THEN colC END)
             AND MAX(CASE WHEN colB = 'c' THEN colC END) < MIN(CASE WHEN colB = 'd' THEN colC END)
             AND MAX(CASE WHEN colB = 'd' THEN colC END) < MIN(CASE WHEN colB = 'e' THEN colC END)
        THEN 'All checks passed'
        ELSE 'Some checks failed'
    END AS overall_validation
FROM tableA
GROUP BY colA;

This approach guarantees that no record of an earlier colB value comes after any record of a later colB value in the same colA group.

Approach 3: Window Functions (Strict Sequential Order) #

If you need to validate that the entire sequence of timestamps in a group strictly follows the a→b→c→d→e order (even if there are multiple records per colB), use window functions to compare expected vs. actual order:

WITH ranked_data AS (
    SELECT 
        colA,
        colB,
        colC,
        -- Assign an expected order number to each colB value
        CASE colB 
            WHEN 'a' THEN 1 
            WHEN 'b' THEN 2 
            WHEN 'c' THEN 3 
            WHEN 'd' THEN 4 
            WHEN 'e' THEN 5 
        END AS expected_order,
        -- Get the actual order of records sorted by timestamp
        ROW_NUMBER() OVER (PARTITION BY colA ORDER BY colC) AS actual_order
    FROM tableA
)
SELECT 
    colA,
    -- Check if all records match the expected order
    CASE WHEN COUNT(CASE WHEN expected_order != actual_order THEN 1 END) = 0 THEN 'Pass' ELSE 'Fail' END AS order_validation
FROM ranked_data
GROUP BY colA;

This works by sorting records in each colA group by timestamp, then checking if their colB values align with the predefined sequence.

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