CODEWITHSUNDEEP
sqlpostgresqljoin
Ondřej Moldan
Ondřej Moldan

Reputation: 139

PostgreSQL generating missing records and group them with source table

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:

enter image description here

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.

Upvotes: 1

Views: 231

Answers (3)

Serg
Serg

Reputation: 22811

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"

Upvotes: 1

53epo
53epo

Reputation: 909

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

Upvotes: 0

Shawn.X
Shawn.X

Reputation: 1353

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

Upvotes: 0

Related Questions