Snowflake表过滤同名称供应商下电压范围重叠的行

问题回顾 #

需要从表中筛选出同一NAME+Provider组内，不与任何其他行电压区间重叠的记录。比如OSMO+Bell组中，前3行区间互相重叠，全部移除，仅保留无重叠的第4行；MASD+Tele组只有一行，直接保留。

错误分析 #

你之前尝试的窗口函数方案错误在于：用整个组的min/max电压来判断当前行是否在范围内，但当前行本身就是组的一部分，所以MIN_VOLT NOT BETWEEN set_min AND set_max永远不成立，导致返回空结果。正确逻辑应该是逐行与同组内其他行比较区间是否重叠。

正确SQL实现 #

方法1：使用EXISTS子查询（直观易懂） #

SELECT t1.NAME, t1.Provider, t1.Min_Volt, t1.Max_Volt
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.NAME = t1.NAME
      AND t2.Provider = t1.Provider
      -- 判断两个区间是否重叠：区间A的最小值 < 区间B的最大值，且区间B的最小值 < 区间A的最大值
      AND t2.Min_Volt < t1.Max_Volt
      AND t1.Min_Volt < t2.Max_Volt
      -- 排除当前行自身（避免和自己比较）
      AND (t2.Min_Volt != t1.Min_Volt OR t2.Max_Volt != t1.Max_Volt)
);

方法2：使用窗口函数（适合大数据量优化） #

如果表数据量较大，窗口函数可以减少重复扫描：

WITH ranked_data AS (
    SELECT 
        NAME,
        Provider,
        Min_Volt,
        Max_Volt,
        -- 标记是否存在前序重叠行
        MAX(CASE WHEN LAG(Max_Volt) OVER (PARTITION BY NAME, Provider ORDER BY Min_Volt) > Min_Volt THEN 1 ELSE 0 END) OVER (PARTITION BY NAME, Provider) AS has_prev_overlap,
        -- 标记是否存在后序重叠行
        MAX(CASE WHEN LEAD(Min_Volt) OVER (PARTITION BY NAME, Provider ORDER BY Min_Volt) < Max_Volt THEN 1 ELSE 0 END) OVER (PARTITION BY NAME, Provider) AS has_next_overlap
    FROM your_table
)
SELECT NAME, Provider, Min_Volt, Max_Volt
FROM ranked_data
WHERE has_prev_overlap = 0 AND has_next_overlap = 0;

注：方法2通过排序后检查前后行是否重叠，再用MAX窗口函数判断组内是否存在任何重叠。如果组内只有一行，前后行都为空，自然满足条件。

结果验证 #

运行上述SQL后，将得到你期望的结果：

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