Power BI中使用GROUPBY度量合并距离区间并完整显示的方法

我有一张按距离维度展示特征的表格，部分特征值为NULL。需要实现：选择指定特征列后，将特征值相同的连续距离区间合并，用min/max计算合并后的起止距离，同时必须完整显示所有区间（包括含NULL值的区间）。

输入表格 #

预期输出 #

同时选择Col1和Col2时 #

仅选择Col1时 #

解决方案（SQL实现） #

核心逻辑 #

用「分组岛」思路识别连续相同特征的区间：先给每个连续相同特征的区间打唯一分组标识，再按分组聚合计算合并后的起止距离，同时保留特征列取值。

针对多列（Col1+Col2）的代码 #

假设表名为distance_features：

WITH ranked_data AS (
    SELECT
        *,
        -- 生成分组ID：当前行特征与上一行不同时，分组号+1
        SUM(
            CASE 
                WHEN (Col1 IS NOT DISTINCT FROM LAG(Col1) OVER (ORDER BY "Distance from"))
                     AND (Col2 IS NOT DISTINCT FROM LAG(Col2) OVER (ORDER BY "Distance from"))
                THEN 0 
                ELSE 1 
            END
        ) OVER (ORDER BY "Distance from") AS group_id
    FROM distance_features
)
SELECT
    MIN("Distance from") AS "Distance from",
    MAX("Distance to") AS "Distance to",
    Col1,
    Col2
FROM ranked_data
GROUP BY group_id, Col1, Col2
ORDER BY "Distance from";

针对单列（仅Col1）的代码 #

WITH ranked_data AS (
    SELECT
        *,
        SUM(
            CASE 
                WHEN Col1 IS NOT DISTINCT FROM LAG(Col1) OVER (ORDER BY "Distance from")
                THEN 0 
                ELSE 1 
            END
        ) OVER (ORDER BY "Distance from") AS group_id
    FROM distance_features
)
SELECT
    MIN("Distance from") AS "Distance from",
    MAX("Distance to") AS "Distance to",
    Col1
FROM ranked_data
GROUP BY group_id, Col1
ORDER BY "Distance from";

关键细节 #

• IS NOT DISTINCT FROM：专门处理NULL值的比较（SQL中NULL=NULL结果为UNKNOWN，这个运算符会把NULL视为相等）；
• LAG()：获取上一行的特征值，用来判断当前行是否属于上一个连续区间；
• SUM() OVER()：累计生成分组ID，每次特征变化时分组ID加1，确保连续相同特征的行被归为同一组。

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