Вычислить первое значение на основе условия

У меня есть следующая таблица ticks

дата и время наименьший_тик tick_lower 2022-10-01 00:02:00 204406 204306 2022-10-01 00:03:00 204525 204425 2022-10-01 00:04:00 204414 204314 2022-10-01 00:05:00 204200 204100 2022-10-01 00:06:00 204220 204120 2022-10-01 00:07:00 204120 204020

Я хочу получить первое значение tick_lower_position для tick_lower, когда tick_lower <= lowest_tick

Таким образом, результирующая таблица должна выглядеть так

дата и время наименьший_тик tick_lower tick_lower_position 2022-10-01 00:02:00 204406 204306 204306 2022-10-01 00:03:00 204525 204425 204306 2022-10-01 00:04:00 204487 204387 204306 2022-10-01 00:05:00 204200 204100 204100 2022-10-01 00:06:00 204220 204120 204100 2022-10-01 00:07:00 204120 204020 204100

До сих пор я пытался применить решение, предоставленное @the-impaler, для других данных.

select y.*, first_value(tick_lower) 
  over(partition by g order by datetime) as tick_lower_position
from (
  select x.*, sum(i) over(order by datetime) as g
  from (
    select t.*, case when lowest_tick <
      lag(tick_lower) over(order by datetime) 
      then 1 else 0 end as i
    from t
  ) x
) y

Но это решение не работает для текущего примера. Как вы можете видеть в этом примере на db<>fidle я получил неправильное значение в 3-й строке. tick_lower_position в 3-й строке все равно должно быть равно 204306

Почему 204100 нижнее правое значение? Разве 204020 не меньше 204120?

Tim Biegeleisen 16.01.2023 10:17

@TimBiegeleisen, потому что 204100 меньше 204120

Ivan Roptanov 16.01.2023 10:54

Пожалуйста, правильно объясните свою цель.

Erwin Brandstetter 16.01.2023 10:55
What I want to get is to show the first value tick_lower_position for tick_lower when tick_lower <= lowest_tick ... пожалуйста, отредактируйте этот текст, чтобы отразить отставание, если это действительно ваша логика.
Tim Biegeleisen 16.01.2023 10:57
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
4
52
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Простой ответ, но вы могли бы просто использовать MIN() в качестве оконной функции вместе с выражением CASE:

SELECT *, MIN(CASE WHEN tick_lower < lowest_tick THEN tick_lower END)
              OVER (ORDER BY datetime) AS tick_lower_position
FROM yourTable
ORDER BY datetime;

Спасибо за ваш ответ! К сожалению, предложенное решение не дает желаемого результата. дб<>рабочий пример

Ivan Roptanov 16.01.2023 10:59

Пожалуйста, отредактируйте свой вопрос и четко объясните ожидаемый результат.

Tim Biegeleisen 16.01.2023 10:59
Ответ принят как подходящий

Проблема (насколько я понял из расплывчатого описания и комментариев) не поддается естественному использованию чистого SQL. Похоже на случай процедурного решения:

Создайте эту функцию один раз:

CREATE OR REPLACE FUNCTION my_func()
  RETURNS TABLE (datetime timestamp, lowest_tick int, tick_lower int, tick_lower_position int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   r  tbl;  -- use table type as row variable
   _tick_lower_position int;
BEGIN
   FOR r IN
      SELECT * FROM tbl t ORDER BY t.datetime
   LOOP
      IF r.lowest_tick > _tick_lower_position THEN
        -- do nothing
      ELSE
         _tick_lower_position := CASE WHEN r.tick_lower <= r.lowest_tick THEN r.tick_lower END;
      END IF;

      RETURN QUERY
      SELECT r.*, _tick_lower_position;  -- output row
   END LOOP;
END
$func$;

Затем позвоните:

SELECT * FROM my_func();

рабочий пример

Я разрешаю tick_lower_position по умолчанию NULL, если указанное вами условие tick_lower <= lowest_tick не выполняется.

Это невероятно! Большое вам спасибо за вашу помощь!

Ivan Roptanov 17.01.2023 21:55

Запрос, который вы пробовали, почти готов, вам просто нужен еще один слой, получающий least() предшествующее значение tick_lower_position и текущее значение tick_lower. Смотрите демо:

select z.datetime    
     , z.lowest_tick 
     , z.tick_lower 
     , least(lag(tlp) over(), tick_lower) tick_lower_position 
  from ( select y.*
              , first_value(tick_lower) over(partition by g order by datetime) as tlp
           from ( select x.*, sum(i) over(order by datetime) as g
                    from ( select ticks.*
                                , case when lowest_tick < lag(tick_lower) over(order by datetime) 
                                       then 1 else 0 end as i
                             from ticks 
                         ) x
                ) y
        ) z;

Спасибо за ваш ответ! Тем не менее, tick_lower_position для последней строки должно быть равно 204100, но в вашей демонстрации оно равно 204020.

Ivan Roptanov 17.01.2023 22:17

Другие вопросы по теме