SQL如何按参数B的阈值标记筛选后续行并生成两类结果集

现有一张包含date_time、parameter、value三列的表，需要实现两类业务需求： #

需求1：筛选符合规则的行子集 #

判定逻辑如下：

• 当遇到parameter='B'且value>5时，保留该行及后续所有非B参数的行
• 当遇到parameter='B'且value<=5时，保留该行但忽略后续所有非B参数的行，直到下一个参数B的行出现重新判定
  对应伪代码逻辑：

include_flag = 0
result_set = empty table

for row in rows:
    if parameter = B and value > 5:
        result_set.append(row)
        include_flag = 1
    elif parameter = B and value <= 5:
        result_set.append(row)
        include_flag = 0
    elif parameter <> B:
        if include_flag = 1:
            result_set.append(row)
        elif include_flag = 0:
            skip(row)

需求2：保留全表行数的结果集 #

所有行均保留，不符合筛选规则的行value字段设为NaN即可。 #

已实现的中间查询逻辑如下，需要将中间结果中B_over_limit的空值填充为最近的非空0或1值，用LAST_VALUE窗口函数实现未成功：

SELECT t.parameter, t.date_time, t.value, B_over_limit = CASE 
   WHEN t.parameter = 'B' AND t.value > 5.0 THEN 1 
   WHEN t.parameter = 'B' AND t.value <= 5.0 THEN 0 
   ELSE null END
FROM table t ORDER BY t.date_time

通用兼容版实现（支持所有带窗口函数的数据库，如MySQL8.0+、PostgreSQL、Oracle等） #

核心逻辑是先按B行划分区间，每个区间包含一个B行和到下一个B行之前的所有非B行，再统一计算每个区间的保留规则。

需求1（筛选行子集）实现 #

WITH marked_data AS (
    -- 第一步：生成B分组ID，同一个分组内包含1个B行和后续到下一个B行前的所有行
    SELECT 
        parameter,
        date_time,
        value,
        SUM(CASE WHEN parameter = 'B' THEN 1 ELSE 0 END) OVER (ORDER BY date_time) AS b_group
    FROM your_table
),
group_flag AS (
    -- 第二步：计算每个分组的保留标记，取分组内B行的判定结果
    SELECT 
        *,
        MAX(CASE WHEN parameter = 'B' AND value >5 THEN 1 WHEN parameter='B' THEN 0 END) OVER (PARTITION BY b_group) AS include_flag
    FROM marked_data
)
-- 筛选符合要求的行：所有B行默认保留，非B行仅标记为1时保留
SELECT parameter, date_time, value
FROM group_flag
WHERE parameter = 'B' OR include_flag =1
ORDER BY date_time;

需求2（全表保留、不符合规则值置空）实现 #

和需求1核心逻辑完全一致，仅最后输出逻辑调整：

WITH marked_data AS (
    SELECT 
        parameter,
        date_time,
        value,
        SUM(CASE WHEN parameter = 'B' THEN 1 ELSE 0 END) OVER (ORDER BY date_time) AS b_group
    FROM your_table
),
group_flag AS (
    SELECT 
        *,
        MAX(CASE WHEN parameter = 'B' AND value >5 THEN 1 WHEN parameter='B' THEN 0 END) OVER (PARTITION BY b_group) AS include_flag
    FROM marked_data
)
-- 不做行过滤，仅不符合要求的行value置空
SELECT 
    parameter, 
    date_time,
    CASE WHEN parameter = 'B' OR include_flag =1 THEN value ELSE NULL END AS value
FROM group_flag
ORDER BY date_time;

LAST_VALUE填充空值的正确写法 #

你之前用LAST_VALUE未成功是因为没有加IGNORE NULLS参数和正确的窗口范围，支持IGNORE NULLS的数据库（PostgreSQL、BigQuery、Oracle等）可以用以下写法实现标记填充：

WITH mid_data AS (
    SELECT 
        parameter, 
        date_time, 
        value, 
        CASE 
            WHEN parameter = 'B' AND value > 5.0 THEN 1 
            WHEN parameter = 'B' AND value <= 5.0 THEN 0 
            ELSE NULL 
        END AS B_over_limit
    FROM your_table 
)
SELECT 
    parameter,
    date_time,
    value,
    LAST_VALUE(B_over_limit IGNORE NULLS) OVER (ORDER BY date_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_flag
FROM mid_data
ORDER BY date_time;

• 底层判定逻辑完全一致，差异仅在最后一步的输出处理
• 需求1是行过滤逻辑，符合条件的行才输出，结果行数小于等于原表行数
• 需求2不做行过滤，仅对不符合条件的行的value字段做置空处理，结果行数和原表完全一致

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