Question 90127 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate first value based on condition – Postgresql

I have the following table ticks

datetime lowest_tick 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

What I want to get is to show the first value tick_lower_position for tick_lower when tick_lower <= lowest_tick

So the resulting table should look like this

datetime 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

So far, I have tried to apply the solution provided by @the-impaler for other data.

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

But this solution doesn’t work for the current example.
As you may see in this example on db<>fidle I got the wrong value in the 3rd row. tick_lower_position in the 3rd row still should be equal to 204306

Tags:

3

Answers


  1. 0

    Simple answer, but you might be able to just use MIN() as a window function along with a CASE expression:

    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;
    Login or Signup to reply.
  2. 0

    The problem (from what I gather from the vague description and comments) does not lend itself naturally to pure SQL. Seems like a case for a procedural solution:

    Create this function once:

    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$;

    Then call:

    SELECT * FROM my_func();

    fiddle

    I let tick_lower_position default to NULL if your cited condition tick_lower <= lowest_tick is not met.

    Login or Signup to reply.
  3. 0

    The query you tried is almost there you just need another layer getting least() prior tick_lower_position and current tick_lower. See demo:

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search