I'm attempting to do a full outer join in Postgres to see how data changes between the past 24 hours and the 24 hours before that ("today" and "yesterday" euphemistically). Some values only exist on one side of the join, in that case I'd like to use the value from the other side of the join.
Current result with below query:
Today Type | Today Count | Yesterday Type | Yesterday Count |
---|---|---|---|
REPORT_A | 2 | REPORT_A | 5 |
REPORT_B | 4 | ||
REPORT_C | 6 |
What I would like the output to be:
Today Type | Today Count | Yesterday Type | Yesterday Count |
---|---|---|---|
REPORT_A | 2 | REPORT_A | 5 |
REPORT_B | 4 | REPORT_B | 0 |
REPORT_C | 0 | REPORT_C | 6 |
For REPORT_B I'd like its values propagated to the right side and for REPORT_C I'd like its values propagated to the left side. How do I do this?
WITH
yesterday_report_count AS (
SELECT
"reports"."report"."type" AS "type",
"reports"."report"."status" AS "status",
COUNT(*) AS "count"
FROM
"reports"."report"
WHERE
"reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
GROUP BY
"reports"."report"."type",
ORDER BY
"count" DESC,
"reports"."report"."type" ASC,
)
SELECT
yesterday_report_count.type as "Today Type",
yesterday_report_count.count "Today Count",
today_report_count.type as "Yesterday Type",
today_report_count.count as "Yesterday Count"
FROM
yesterday_report_count
FULL JOIN (
SELECT
"reports"."report"."type" AS "type",
COUNT(*) AS "count"
FROM
"reports"."report"
WHERE
1=1
AND "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '48 HOURS' AND NOW() - INTERVAL '24 HOURS'
GROUP BY
"reports"."report"."type",
ORDER BY
"count" DESC,
"reports"."report"."type" ASC,
) AS today_report_count
ON today_report_count.type = yesterday_report_count.type
I tried using coalesce()
in the select statement but it seems that the "missing" values are not actually considered null
.
Use COALESCE()
. For example:
select
coalesce(t.a, u.a) as a,
coalesce(t.b, 0) as tb,
coalesce(t.a, u.a) as a, -- no need to repeat this, but if you must...
coalesce(u.b, 0) as ub
from t
full join u on u.a = t.a
Result:
a tb a ub
-- ---- -- ---
A 100 A 120
B 200 B 0
C 0 C 300
See running example at db<>fiddle.