如何简便校验多日期序列合理性？SQL/DAX实现优化咨询

针对你提到的事件阶段时间完整性与递增性校验需求，以下是更简洁的SQL和DAX实现方案，避免冗长的CASE WHEN分支：

SQL 实现 #

合并完整性与时间递增校验 #

通过逻辑判断的递进关系，一次性覆盖所有规则：

SELECT 
    event_id,
    CASE 
        -- 完整性规则：若后续阶段有值，前置所有阶段必须非空
        WHEN (start_time IS NULL AND (first_time IS NOT NULL OR second_time IS NOT NULL OR third_time IS NOT NULL OR end_time IS NOT NULL))
             OR (first_time IS NULL AND (second_time IS NOT NULL OR third_time IS NOT NULL OR end_time IS NOT NULL))
             OR (second_time IS NULL AND (third_time IS NOT NULL OR end_time IS NOT NULL))
             OR (third_time IS NULL AND end_time IS NOT NULL)
        THEN '存在前置阶段时间缺失'
        -- 时间递增规则：已存在的阶段必须按顺序递增
        WHEN (first_time IS NOT NULL AND start_time >= first_time)
             OR (second_time IS NOT NULL AND first_time >= second_time)
             OR (third_time IS NOT NULL AND second_time >= third_time)
             OR (end_time IS NOT NULL AND third_time >= end_time)
        THEN '时间序列不符合递增要求'
        ELSE '校验通过'
    END AS compliance_result
FROM your_event_table;

如果使用支持数组操作的SQL方言（如PostgreSQL），还可以用数组来简化完整性判断：

WITH stage_arrays AS (
    SELECT 
        event_id,
        ARRAY[start_time, first_time, second_time, third_time, end_time] AS ordered_stages
    FROM your_event_table
)
SELECT 
    event_id,
    CASE 
        -- 检查非空值是否是数组的连续前缀（无中间空值）
        WHEN array_position(ordered_stages, NULL) IS NOT NULL 
             AND array_length(array_remove(ordered_stages, NULL), 1) != array_position(ordered_stages, NULL) - 1
        THEN '存在前置阶段时间缺失'
        -- 检查所有非空的连续阶段是否递增
        WHEN (start_time IS NOT NULL AND first_time IS NOT NULL AND start_time >= first_time)
             OR (first_time IS NOT NULL AND second_time IS NOT NULL AND first_time >= second_time)
             OR (second_time IS NOT NULL AND third_time IS NOT NULL AND second_time >= third_time)
             OR (third_time IS NOT NULL AND end_time IS NOT NULL AND third_time >= end_time)
        THEN '时间序列不符合递增要求'
        ELSE '校验通过'
    END AS compliance_result
FROM stage_arrays;

DAX 实现 #

通过定义变量简化重复判断，逻辑更清晰：

Compliance Result = 
VAR HasStart = NOT(ISBLANK(your_table[start_time]))
VAR HasFirst = NOT(ISBLANK(your_table[first_time]))
VAR HasSecond = NOT(ISBLANK(your_table[second_time]))
VAR HasThird = NOT(ISBLANK(your_table[third_time]))
VAR HasEnd = NOT(ISBLANK(your_table[end_time]))

-- 完整性校验：后续阶段存在则前置阶段必须存在
VAR IntegrityOK = 
    AND(
        OR(NOT(HasFirst), HasStart),
        AND(
            OR(NOT(HasSecond), HasFirst),
            AND(
                OR(NOT(HasThird), HasSecond),
                OR(NOT(HasEnd), HasThird)
            )
        )
    )

-- 时间递增校验：存在的阶段必须按顺序递增
VAR TimeOrderOK = 
    AND(
        OR(NOT(HasFirst), your_table[start_time] < your_table[first_time]),
        AND(
            OR(NOT(HasSecond), your_table[first_time] < your_table[second_time]),
            AND(
                OR(NOT(HasThird), your_table[second_time] < your_table[third_time]),
                OR(NOT(HasEnd), your_table[third_time] < your_table[end_time])
            )
        )
    )

RETURN
    SWITCH(
        TRUE(),
        NOT(IntegrityOK), "存在前置阶段时间缺失",
        NOT(TimeOrderOK), "时间序列不符合递增要求",
        "校验通过"
    )

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