如何在DAX中获取工单被阻塞前的状态并统计次数？

方法一：通用SQL实现（兼容多数数据库） #

通过两次CTE分别处理状态排序和阻塞事件关联，最终统计次数：

WITH ranked_history AS (
    SELECT 
        key,
        status,
        date,
        ROW_NUMBER() OVER (PARTITION BY key ORDER BY date) AS rn
    FROM History
    WHERE status != 'Block'
),
block_events AS (
    SELECT 
        key,
        date AS block_date,
        (SELECT MAX(rn) FROM ranked_history rh WHERE rh.key = h.key AND rh.date < h.date) AS prev_rn
    FROM History h
    WHERE status = 'Block'
)
SELECT 
    rh.status AS Status,
    COUNT(*) AS Value
FROM block_events be
JOIN ranked_history rh ON be.key = rh.key AND be.prev_rn = rh.rn
GROUP BY rh.status
ORDER BY Value DESC;

逻辑说明：

• ranked_history：给每个工单的有效状态（非Block）按时间排序，生成行号标记顺序
• block_events：提取所有阻塞事件，并找到对应工单在阻塞发生前的最后一条有效状态的行号
• 关联两个CTE，统计各状态被触发阻塞的次数

方法二：窗口函数简化实现（支持LAG+IGNORE NULLS的数据库，如PostgreSQL、MySQL 8.0+） #

利用LAG窗口函数跳过Block记录，直接获取前置有效状态：

WITH history_with_prev_status AS (
    SELECT 
        key,
        status,
        date,
        LAG(CASE WHEN status != 'Block' THEN status END) IGNORE NULLS OVER (PARTITION BY key ORDER BY date) AS prev_non_block_status
    FROM History
)
SELECT 
    prev_non_block_status AS Status,
    COUNT(*) AS Value
FROM history_with_prev_status
WHERE status = 'Block'
GROUP BY prev_non_block_status
ORDER BY Value DESC;

逻辑说明：

• history_with_prev_status：对每个工单的所有记录按时间排序，用LAG...IGNORE NULLS跳过Block记录，直接取最近的有效状态
• 过滤出所有阻塞事件行，统计对应前置状态的出现次数

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