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

Counting running total on time series where condition is X – Postgresql

I have a SQL table of date entries with three columns: date, item, and status. The table appears like this:

date item status
2023-01-01 A on
2023-01-01 B on
2023-01-01 C off
2023-01-02 A on
2023-01-02 B off
2023-01-02 C off
2023-01-02 D on
2023-01-03 A on
2023-01-03 B off
2023-01-03 C off
2023-01-03 D off

Looking at the most recent entries, I need grouped by item, the latest date and status, and a count on the running total of entries where status has not changed. For example, the output I am looking for would be:

latest_date item current_status number_of_days_on_current
2023-01-03 A on 3
2023-01-03 B off 2
2023-01-03 C off 3
2023-01-03 D off 1

How would I get the output I want in PostgreSQL 13.7?

This returns the latest date, item, and current status, but does not correctly count the number of days the item has been on the current status:

WITH CTE AS (
  SELECT 
    item, 
    date, 
    status, 
    LAG(status) OVER (PARTITION BY item ORDER BY date) AS prev_status, 
    ROW_NUMBER() OVER (PARTITION BY item ORDER BY date DESC) AS rn
  FROM 
    schema.table
)
SELECT 
  MAX(date) AS latest_date, 
  item, 
  status AS current_status, 
  SUM(CASE WHEN prev_status = status THEN 0 ELSE 1 END) 
    OVER (PARTITION BY item ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS number_of_days
FROM 
  CTE 
WHERE 
  rn = 1 
GROUP BY
item, status, prev_status, date
ORDER BY 
  item
Tags:

2

Answers


  1. 0

    Using a cte to build the runs of consecutive statuses:

    with recursive cte(s_date, date, item, status, s_count, result) as (
    select e.date, e.date, e.item, e.status, 1, '[]'::jsonb from entries e 
    left join entries e1 on e1.item = e.item and e.date - interval '1 day' = e1.date where e1.date is null
    union all
    select c.s_date, e.date, c.item, e.status, 
      case when e.status = c.status then c.s_count + 1 else 1 end, 
      case when e.status = c.status then c.result else c.result || jsonb_build_object('s', c.status, 'c', c.s_count) end 
    from cte c join entries e on e.item = c.item and c.date + interval '1 day' = e.date
)
select date(t1.md), t1.item, e.status, (select max(((v -> 'c')#>>'{}')::int) 
      from jsonb_array_elements(r::jsonb) v where (v -> 's')#>>'{}' = e.status) 
    from (select t.s_date, t.item, max(t.date) md, max(t.result::text) r 
 from (select c.s_date, c.date, c.item, c.result || jsonb_build_object('s', c.status, 'c', c.s_count) result from cte c) t
group by t.s_date, t.item) t1
join entries e on e.item = t1.item and date(e.date) = date(t1.md)

    See fiddle.

    Login or Signup to reply.
  2. 0

    According to your comment, you want to find the max count of consecutive statuses where status = last status value, this became a gaps and islands problem. This can be solved using a difference between two row_numbers and the last_value function as the following:

    with last_status as
(
  select *, 
      last_value(status) over (partition by item order by date_ 
        range between unbounded preceding and unbounded following) current_status,
      max(date_) over (partition by item) latest_date,
      row_number() over (partition by item order by date_) -
      row_number() over (partition by item, status order by date_) grp
  from table_name
),
consecutive_status_counts as
(
  select latest_date, item, current_status, status, count(*) cnt
  from last_status 
  where current_status = status
  group by latest_date, item, current_status, status, grp 
)
select latest_date, 
       item,
       current_status, 
       max(cnt) number_of_days_on_current
from consecutive_status_counts
group by latest_date, item, current_status
order by item

    See demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search