Блог

Мне нужно удалить некоторые строки в наборе данных, из которых значение speed равно нулю и длится более N раз (предположим, что N равно 2). Структура таблицы demo выглядит следующим образом:

Затем я надеюсь создать таблицу, подобную приведенной ниже, используя window_function :

Тогда я могу легко исключить эти строки, используя WHERE NOT (speed = 0 AND lasting > 2)

Поместите код, который я пробовал здесь, но он не вернул ожидаемое значение, и я предполагаю, что это FROM (SELECT ... FROM (SELECT ... может быть не лучшая практика для решения проблемы:

 SELECT g3.*, count(id) OVER (PARTITION BY car, cumsum ORDER BY id) as num   
  FROM (SELECT g2.*, sum(grp2) OVER (PARTITION BY car ORDER BY id) AS cumsum             
    FROM (SELECT g1.*, (CASE ne0 WHEN 0 THEN 0 ELSE 1 END) AS grp2                            
      FROM (SELECT g.*, speed - lag(speed, 1, 0) OVER (PARTITION BY car) AS ne0              
        FROM (SELECT *, row_number() OVER (PARTITION BY car) AS grp FROM demo) g ) g1 ) g2 ) g3                                                                                       
ORDER BY id;
 

Вы можете использовать функцию window LAG() для проверки предыдущего speed значения для каждой строки и SUM() функцию window для создания групп для непрерывных значений.
Затем с COUNT() помощью функции window вы можете подсчитать количество строк в каждой группе, чтобы отфильтровать строки с 0 speed в группах, которые имеют более 2 строк:

 SELECT id, car, speed, time
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY car, grp) counter
  FROM (
    SELECT *, SUM(flag::int) OVER (PARTITION BY car ORDER BY time) grp
    FROM (
      SELECT *, speed <> LAG(speed, 1, speed - 1) OVER (PARTITION BY car ORDER BY time) flag
      FROM demo
    ) t  
  ) t
) t
WHERE speed <> 0 OR counter <= 2
ORDER BY id;
 

Смотрите демонстрацию.