PostgreSQL generating missing records and group them with source table

Question

I'm creating a PostgreSQL query and I'd like to have the missing records for each day filled in automatically.

It occurred to me that I could generate a table that contained zero values and then join a source table to it.

So I created this query, but the result still does not contain missing days, only existing records from the source database table. For example, records from "2021-08-01 00:00:00", "2021-08-07 00:00:00" or "2021-08-08 00:00:00" are missing.

SELECT
  s."Date",
  s."PowerOn",
  s."Idle",
  s."Run",
  CONCAT_WS('%', ROUND(NULLIF(s."Run"::numeric, 0) / NULLIF(s."PowerOn"::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
    SELECT d."Date", bigint '0' AS "PowerOn", bigint '0' AS "Idle", bigint '0' AS "Run", text '0 %' AS "Effectivity" 
    FROM (
        SELECT generate_series(timestamp '2021-08-01 00:00:00'
                        , NOW()
                        , interval  '1 day')::timestamp
) d("Date")) f
JOIN "Absolute_OEE" s ON s."Machine" = 'Machine01'
WHERE
  s."Date" > '2021-08-01 00:00:00'
GROUP BY s."Date",s."PowerOn", s."Idle", s."Run"
ORDER BY s."Date"

Result:

Can you please advise me how I can group the records and add zero values for days that are not recorded?

Thanks for your advice and tips.

I'd try to generate the date series with dates only and have an OUTER JOIN with the real data – which would fill up the missing data with NULL values, something like SELECT generateseries(...) AS some_name LEFT OUTER JOIN your_table ON .... — Aconcagua
– Aconcagua, Commented Aug 24, 2021 at 6:29
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Aug 24, 2021 at 6:44

Serg · Accepted Answer · 2021-08-24 06:41:04Z

1

You can use LEFT JOIN and COALESCE

SELECT
  d."Date",
  coalesce(s."PowerOn", bigint '0') AS "PowerOn",
  coalesce(s."Idle", bigint '0') AS "Idle",
  coalesce(s."Run", bigint '0') AS "Run",
  CONCAT_WS('%', ROUND(NULLIF(coalesce(s."Run", bigint '0')::numeric, 0) / NULLIF(coalesce(s."PowerOn", bigint '0')::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
     SELECT generate_series(timestamp '2021-08-01 00:00:00'
                     , NOW()
                     , interval  '1 day')::timestamp
     ) d
LEFT JOIN "Absolute_OEE" s ON d."Date"= s."Date" 
   AND s."Machine" = 'Machine01'
   AND s."Date" > '2021-08-01 00:00:00' 
GROUP BY  d."Date",
  coalesce(s."PowerOn", bigint '0'),
  coalesce(s."Idle", bigint '0'),
  coalesce(s."Run", bigint '0')
ORDER BY d."Date"

answered Aug 24, 2021 at 6:41

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shawn.X · Accepted Answer · 2021-08-24 06:29:41Z

0

Just use left join, the example such as below :

with base_data as (
select 
    generate_series(timestamp '2021-08-01 00:00:00', now(), interval  '1 day')::date as the_date,
    0 as col
)
,your_real_table as ( 
select
    the_date,
    count(1) as col
from
    table_name
where
    the_date >= '2021-08-01'::date 
group by
    the_date
)
select
    b.the_date,
    coalesce(r.col,b.col) as col
from
    base_data b
left join
    your_real_table r on b.the_date = r.the_date
order by
    b.the_date

answered Aug 24, 2021 at 6:29

Shawn.X

1,3637 silver badges17 bronze badges

Comments

53epo · Accepted Answer · 2021-08-24 06:32:26Z

0

I think your JOIN is not correct... you are getting a cartesian product which you then need to remove with the GROUP BY.

Make the following changes

Give the generate_series() column an alias e.g. SELECT generate_series(...)::timestamp AS spine
Alter your JOIN clause to a LEFT JOIN e.g. `LEFT JOIN "Absolute_OEE" s ON s."Date" = f.spine
You then should not need the GROUP BY any more

answered Aug 24, 2021 at 6:32

53epo

9197 silver badges8 bronze badges

Collectives™ on Stack Overflow

PostgreSQL generating missing records and group them with source table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related