加入表並返回計數，即使它是 0

我有這個查詢：

select settings.customer_id, current::jsonb->'alarms' as alarms, from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
where res.alarms is not null;

返回

+------------------+
|customer_id|alarms|
|1          |{...} |
|2          |{...} |
|3          |{...} |
+------------------+

然後我有另一個實體registration，其中欄位customer_id和date位置date是類型date，並且我以格式儲存值YYYY-MM-DD。

我想要實現的是加入registration實體customer_id併計算registration給定單個日期 fe 的記錄'2018-11-20'。但是（這是吸引人的部分）我想以某種方式檢索它，因此如果該日期沒有註冊記錄，則返回 0，否則返回實際數字。

使用到目前為止我嘗試過的解決方案，我只會得到具有alarmsAND 註冊的記錄，就好像我有這個：

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(regs.count, 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join (select customer_id, count(customer_id) from registration where date='2018-11-20' group by customer_id) as regs on settings.customer_id=reg.customer_id
where res.alarms is not null;

或者

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(count(regs.customer_id), 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join registration as regs on settings.customer_id=regs.customer_id
where res.alarms is not null and regs.date='2018-10-10'
group by settings.customer_id, settings.current;

如果我換一種說法：獲取具有alarms對象的記錄，並獲取給定日期的註冊數量，無論它是 0 還是 > 0。

LEFT JOIN是關鍵，就像 ypercube 評論的那樣。

而且您可能不需要自加入 - 假設settings.customer_id是unique not null.

如果您的謂詞是選擇性的（多於幾行沒有警報），我建議計入LATERAL子查詢：

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
    , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN LATERAL (
  SELECT count(*) AS reg_ct
  FROM   registration
  WHERE  customer_id = s.customer_id
  AND    date = '2018-10-10'
  ) r ON true
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

否則，一個普通的子查詢應該更快：

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
    , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN (
  SELECT customer_id, count(*) AS reg_ct
  FROM   registration
  WHERE  date = '2018-10-10'
  GROUP  BY 1
  ) r USING (customer_id)
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

無論哪種方式，在加入之前聚合通常更快且更不容易出錯。

有關的：

• 了解聚合的多表連接
• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

引用自：https://dba.stackexchange.com/questions/222980