Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE channel_subscribe (
  id int
, dt timestamp
, session_id int
, agent_id int
, state int
);

INSERT INTO channel_subscribe VALUES
  (8, '2024-01-25 23:00:00', 5, 3148, 1)  -- open session (!)
, (7, '2024-01-25 22:26:57', 4, 3148, 0)
, (7, '2024-01-25 22:24:57', 4, 3148, 1)
, (6, '2024-01-25 22:23:57', 2, 3148, 0)
, (5, '2024-01-25 15:53:30', 1, 3148, 0)
, (4, '2024-01-25 15:53:30', 3, 3148, 0)
, (3, '2024-01-25 13:53:02', 3, 3148, 1)
, (2, '2024-01-25 12:43:10', 2, 3148, 1)
, (1, '2024-01-25 12:30:02', 1, 3148, 1)

, (10, '2024-01-26 01:00:00', 6, 666, 1)   -- other agent
, (11, '2024-01-26 02:00:00', 7, 666, 0)
;

CREATE TABLE



INSERT 0 11


-- SQL solution
SELECT agent_id
     , min(dt) AS start_dt
     , max(dt) FILTER (WHERE sum_state = 0) AS end_dt  -- !
FROM  (
   SELECT *, count(*) FILTER (WHERE sum_state = 0) OVER (PARTITION BY agent_id ORDER BY dt DESC, id DESC) AS island
   FROM  (
      SELECT *, sum(CASE state WHEN 0 THEN -1 ELSE 1 END) OVER (PARTITION BY agent_id ORDER BY dt, id) sum_state
      FROM   channel_subscribe
      ) sub1
   ) sub2
GROUP  BY agent_id, island
ORDER  BY agent_id, island DESC;


agent_id
start_dt
end_dt


666
2024-01-26 01:00:00
2024-01-26 02:00:00

3148
2024-01-25 12:30:02
2024-01-25 22:23:57

3148
2024-01-25 22:24:57
2024-01-25 22:26:57

3148
2024-01-25 23:00:00
null



SELECT 4


-- PL/pgSQL solution
CREATE OR REPLACE FUNCTION f_combined_sessions()
  RETURNS TABLE (agent_id int, start_dt timestamp, end_dt timestamp)
  LANGUAGE plpgsql AS
$func$
DECLARE
   r            record;
   _new_session bool;
   _agent_id    int;
BEGIN
   FOR r IN
      SELECT c.agent_id, c.dt
           , sum(CASE c.state WHEN 0 THEN -1 ELSE 1 END) OVER (PARTITION BY c.agent_id ORDER BY c.dt, c.id) AS sum_state
      FROM   channel_subscribe c
      ORDER  BY c.agent_id,  c.dt, c.id
   LOOP
      IF agent_id = r.agent_id THEN  -- same agent
         IF _new_session = true THEN
            start_dt     := r.dt;
            _new_session := false;
         END IF;
      ELSE                           -- new agent
         agent_id     := r.agent_id;
         start_dt     := r.dt;
         _new_session := false;
      END IF;
      
      IF r.sum_state = 0 THEN        -- end session
         end_dt := r.dt;
         RETURN NEXT;
         _new_session := true;
      END IF;
   END LOOP;

   -- return open session?
   IF _new_session = false THEN
      end_dt := null;
      RETURN NEXT;
   END IF;
END
$func$;

SELECT * FROM f_combined_sessions();

CREATE FUNCTION




agent_id
start_dt
end_dt


666
2024-01-26 01:00:00
2024-01-26 02:00:00

3148
2024-01-25 12:30:02
2024-01-25 22:23:57

3148
2024-01-25 22:24:57
2024-01-25 22:26:57

3148
2024-01-25 23:00:00
null



SELECT 4

agent_id	start_dt	end_dt
666	2024-01-26 01:00:00	2024-01-26 02:00:00
3148	2024-01-25 12:30:02	2024-01-25 22:23:57
3148	2024-01-25 22:24:57	2024-01-25 22:26:57
3148	2024-01-25 23:00:00	null