SQL（Teradata/BigQuery）分区内数据基于前置分区状态排序方案

问题说明 #

需按ts字段分区，重排序每个分区内的state值，使当前分区的第一个state与上一分区的最后一个state匹配。首个分区的第一行state值预先指定（可变），最终要生成idx字段用于PARTITION BY ts ORDER BY idx，且必须在SQL VIEW中实现，不允许使用脚本。

初始数据 #

期望结果 #

约束条件 #

• 每个分区仅包含1行或2行数据
• 首个分区的第一行state值预先确定，可灵活变更

此前尝试的局限 #

之前的代码仅适用于特定数据组合，且依赖上一分区已完成正确排序，无法覆盖所有场景：

-- go 1 or 2 row(s) up to get last row of previous ts. If same state, then 1 (right order) else 2
case when state=(    -- check if first or 2nd row    
    case when row_number() over (partition by ts order by 1)  = 1
        -- read the state of the last row of previous partition
        then lag(state) over (order by ts, state) 
        else lag(state, 2) over (order by ts, state) end
        )
    then 1 else 2 end as idx

解决方案 #

核心思路是通过递归CTE逐个处理每个分区：从首个分区开始按指定初始值排序并记录末尾state，后续分区以此末尾值为基准调整内部排序，确保衔接匹配。递归CTE在Teradata和BigQuery的VIEW中均支持，满足需求。

Teradata 实现 #

CREATE VIEW sorted_state_view AS
WITH RECURSIVE sorted_partitions AS (
    -- 处理首个分区：按指定初始state排序
    SELECT 
        ts,
        state,
        CASE WHEN state = 'AL' THEN 1 ELSE 2 END AS idx,
        -- 记录首个分区的最后state（2行时取非初始值，1行时取自身）
        CASE 
            WHEN COUNT(*) OVER (PARTITION BY ts) = 1 THEN state
            ELSE (SELECT state FROM your_table WHERE ts = (SELECT MIN(ts) FROM your_table) AND state != 'AL')
        END AS last_state
    FROM your_table
    WHERE ts = (SELECT MIN(ts) FROM your_table)
    
    UNION ALL
    
    -- 递归处理后续分区
    SELECT 
        curr.ts,
        curr.state,
        -- 匹配上一分区末尾state的行排前面
        CASE WHEN curr.state = prev.last_state THEN 1 ELSE 2 END AS idx,
        -- 记录当前分区的最后state
        CASE 
            WHEN COUNT(*) OVER (PARTITION BY curr.ts) = 1 THEN curr.state
            ELSE (SELECT state FROM your_table WHERE ts = curr.ts AND state != prev.last_state)
        END AS last_state
    FROM your_table curr
    JOIN sorted_partitions prev 
        ON curr.ts = (SELECT MIN(ts) FROM your_table WHERE ts > prev.ts)
)
SELECT ts, state, idx
FROM sorted_partitions
ORDER BY ts, idx;

BigQuery 实现 #

CREATE OR REPLACE VIEW `your_project.your_dataset.sorted_state_view` AS
WITH RECURSIVE sorted_partitions AS (
    -- 处理首个分区：按指定初始state排序
    SELECT 
        ts,
        state,
        IF(state = 'AL', 1, 2) AS idx,
        -- 记录首个分区的最后state
        IF(
            COUNT(*) OVER (PARTITION BY ts) = 1, 
            state, 
            (SELECT state FROM `your_project.your_dataset.your_table` WHERE ts = (SELECT MIN(ts) FROM `your_project.your_dataset.your_table`) AND state != 'AL')
        ) AS last_state
    FROM `your_project.your_dataset.your_table`
    WHERE ts = (SELECT MIN(ts) FROM `your_project.your_dataset.your_table`)
    
    UNION ALL
    
    -- 递归处理后续分区
    SELECT 
        curr.ts,
        curr.state,
        -- 匹配上一分区末尾state的行排前面
        IF(curr.state = prev.last_state, 1, 2) AS idx,
        -- 记录当前分区的最后state
        IF(
            COUNT(*) OVER (PARTITION BY curr.ts) = 1, 
            curr.state, 
            (SELECT state FROM `your_project.your_dataset.your_table` WHERE ts = curr.ts AND state != prev.last_state)
        ) AS last_state
    FROM `your_project.your_dataset.your_table` curr
    JOIN sorted_partitions prev 
        ON curr.ts = (SELECT MIN(ts) FROM `your_project.your_dataset.your_table` WHERE ts > prev.ts)
)
SELECT ts, state, idx
FROM sorted_partitions
ORDER BY ts, idx;

使用说明 #

1. 替换代码中的'AL'为实际的首个分区初始state值
2. 替换your_table（Teradata）或your_project.your_dataset.your_table（BigQuery）为实际的表路径
3. 生成的idx字段即可用于PARTITION BY ts ORDER BY idx实现排序需求

内容的提问来源于stack exchange，提问作者yan-hic