基于条件计算tick_lower首个有效值tick_lower_position的SQL求解

现有数据表ticks #

需求说明 #

需要生成tick_lower_position字段：

• 取值为满足tick_lower <= lowest_tick条件的首个tick_lower值
• 后续行沿用该值，直到出现下一次满足条件的新值

期望结果表 #

原方案问题分析 #

你尝试的基于lag()的分组方案逻辑不符合当前需求，错误触发了分组条件，导致第三行的tick_lower_position取值错误。

正确解决方案 #

核心思路：

1. 标记所有满足tick_lower <= lowest_tick的行作为"锚点"
2. 基于锚点分组，让锚点后的所有行归属到最近的上一个锚点分组
3. 在每个分组内取锚点的tick_lower值作为全组的tick_lower_position

对应的SQL语句：

SELECT 
  t.*,
  FIRST_VALUE(tick_lower) OVER (PARTITION BY group_id ORDER BY datetime) AS tick_lower_position
FROM (
  SELECT 
    *,
    SUM(CASE WHEN tick_lower <= lowest_tick THEN 1 ELSE 0 END) OVER (ORDER BY datetime) AS group_id
  FROM ticks
) t
ORDER BY datetime;

逻辑解释 #

• 内层子查询通过SUM(CASE...) OVER(ORDER BY datetime)生成group_id：每遇到一个满足条件的锚点行，分组ID递增，后续不满足条件的行继承当前分组ID，直到下一个锚点出现。
• 外层通过FIRST_VALUE(tick_lower)在每个分组内取首个锚点的tick_lower值，作为该组所有行的tick_lower_position。

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