Postgres 15

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

--https://stackoverflow.com/questions/74449229/referring-to-column-derived-earlier-in-same-select-statement-to-improve-readabil

-- table for storing data processing status
CREATE TABLE status_table (
  base_name  text      NOT NULL
, version    smallint  NOT NULL
, ref_time   int       NOT NULL
, processed  bool      NOT NULL
, processing bool      NOT NULL
, updated    int       NOT NULL  DEFAULT (extract(epoch from now()) / 60)
, PRIMARY KEY (base_name, version)
);
 
INSERT INTO status_table VALUES 
  ('abc', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 500)
, ('jkl', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 450)
, ('pqr', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 400)
, ('def', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 350)
, ('hft', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 300)
, ('ghi', 1, 27793600, 'f', 't', (extract(epoch from now()) / 60) - 275)
, ('wqt', 1, 27793600, 't', 'f', (extract(epoch from now()) / 60) - 250)
, ('rfe', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 225)
, ('mno', 1, 27793900, 'f', 't', (extract(epoch from now()) / 60) - 175)
, ('dpw', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 150)
, ('eqz', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 125)
, ('fwp', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 100)

CREATE TABLE



INSERT 0 12


SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS proc
  , round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 1) AS percent
  , round(round(count(*) FILTER (WHERE processed) * 1.0 / count(*), 1) * 100) AS rounded
  , count(*) FILTER (WHERE processed) = count(*) AS complete
  , max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
  , (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC


ref_time
max_updated
total
proc
percent
rounded
complete
settled
ready


27793300
27808599
2
2
100.0
100
t
t
t

27793000
27808499
3
3
100.0
100
t
t
t

27794200
27808799
2
2
100.0
100
t
f
f

27793900
27808749
3
2
66.7
70
f
f
f

27793600
27808649
2
1
50.0
50
f
t
f



SELECT 5


SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS proc
  , round(proc * 100.0 / total, 1) AS percent
  , round(round(proc * 1.0 / total, 1) * 100) AS rounded
  , processed = total AS complete
  , round(extract(epoch from now()) / 60) AS now_mins
  , 200 AS interval_mins
  , max_updated < now_mins - interval_mins AS settled
  , complete AND settled AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC

ERROR:  column "proc" does not exist
LINE 5:   , round(proc * 100.0 / total, 1) AS percent
                  ^


--https://stackoverflow.com/a/74449909/5298879
SELECT *
  , round(proc * 100.0 / total, 1) AS percent
  , round(round(proc * 1.0 / total, 1) * 100) AS rounded
  , proc = total AS complete
  , max_updated < now_mins - interval_mins AS settled
  --, complete AND settled AS ready
FROM (
    SELECT ref_time
      , max(updated) AS max_updated
      , count(*) AS total
      , count(*) FILTER (WHERE processed) AS proc
      , round(extract(epoch from now()) / 60) as now_mins
      , 200 AS interval_mins
    FROM status_table
    GROUP BY ref_time) a
ORDER BY --ready DESC, 
  rounded DESC, ref_time DESC


ref_time
max_updated
total
proc
now_mins
interval_mins
percent
rounded
complete
settled


27794200
27808799
2
2
27808899
200
100.0
100
t
f

27793300
27808599
2
2
27808899
200
100.0
100
t
t

27793000
27808499
3
3
27808899
200
100.0
100
t
t

27793900
27808749
3
2
27808899
200
66.7
70
f
f

27793600
27808649
2
1
27808899
200
50.0
50
f
t



SELECT 5


WITH 
 base_values as 
 (  SELECT ref_time
      , max(updated) AS max_updated
      , count(*) AS total
      , count(*) FILTER (WHERE processed) AS proc
      , round(extract(epoch from now()) / 60) as now_mins
      , 200 AS interval_mins
    FROM status_table
    GROUP BY ref_time )
,level1_values as 
 (  SELECT *
       , proc = total AS complete
       , max_updated < now_mins - interval_mins AS settled
    FROM base_values )
SELECT *
  , round(proc * 100.0 / total, 1) AS percent
  , round(round(proc * 1.0 / total, 1) * 100) AS rounded
  , complete AND settled AS ready
FROM level1_values
ORDER BY ready DESC, rounded DESC, ref_time DESC


ref_time
max_updated
total
proc
now_mins
interval_mins
complete
settled
percent
rounded
ready


27793300
27808599
2
2
27808899
200
t
t
100.0
100
t

27793000
27808499
3
3
27808899
200
t
t
100.0
100
t

27794200
27808799
2
2
27808899
200
t
f
100.0
100
f

27793900
27808749
3
2
27808899
200
f
f
66.7
70
f

27793600
27808649
2
1
27808899
200
f
t
50.0
50
f



SELECT 5

ref_time	max_updated	total	proc	percent	rounded	complete	settled	ready
27793300	27808599	2	2	100.0	100	t	t	t
27793000	27808499	3	3	100.0	100	t	t	t
27794200	27808799	2	2	100.0	100	t	f	f
27793900	27808749	3	2	66.7	70	f	f	f
27793600	27808649	2	1	50.0	50	f	t	f

ref_time	max_updated	total	proc	now_mins	interval_mins	percent	rounded	complete	settled
27794200	27808799	2	2	27808899	200	100.0	100	t	f
27793300	27808599	2	2	27808899	200	100.0	100	t	t
27793000	27808499	3	3	27808899	200	100.0	100	t	t
27793900	27808749	3	2	27808899	200	66.7	70	f	f
27793600	27808649	2	1	27808899	200	50.0	50	f	t