Calculate first value based on condition-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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;