填寫組內缺少的日期

我有下表，其中包含從2014-01-01到的不同站點的值2014-01-04。數據有一些我想填補的日期空白，留下valueas NULL，並將缺失的日期分配給每個站點。我正在使用 PostgreSQL 10.9

這是我的桌子：

CREATE TABLE stations (station_id text, value integer, date date);
INSERT INTO stations (station_id, value, date) VALUES 
('001', 10, '2014-01-01'),
('001', 30, '2014-01-03'),
('002', 40, '2014-01-01'),
('002', 50, '2015-01-02'),
('003', 20, '2014-01-01'),
('003', 10, '2015-01-02'),
('003', 70, '2015-01-04');

我還有一張桌子，裡面有帶有標識符的唯一電台。

我想要這樣的東西：

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| 001     | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| 001     | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| 002     | NULL  | 2014-01-03 |
| 002     | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| 003     | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

在一些 DBA Exchange (questions) 1之後，我嘗試了 aLEFT JOIN與 a的組合LATERAL JOIN：

WITH complete_dates_station AS (
   select station_id,
          generate_series(DATE '2014-01-01', DATE '2014-12-31', INTERVAL '1 day')::DATE as dt
   FROM stations
   GROUP by station_id
   ), temp_join AS (
       SELECT station_id,
              dt,
              s.value
       FROM complete_dates_station
           LEFT JOIN LATERAL (
               SELECT s.value
               FROM stations s
               WHERE s.station_id = complete_dates_station.station_id
               AND s.date = complete_dates_station.dt
               ORDER by s.station_id, date desc
               LIMIT 1) as s on TRUE
            ORDER BY station_id, dt
        ) SELECT * from temp_join

這就像一個魅力，但是對於我的完整表來說，這個連接真的很慢，它有超過 200 萬行並且日期範圍超過 18 年（我在執行 4 小時後停止了）。我通過使用正常嘗試了一種更簡單的方法LEFT JOIN，但是該表將未加入的組輸出為缺失：

WITH complete_dates_station AS (
   SELECT station_id,
          generate_series(date '2014-01-01', date '2014-12-31', interval '1 day')::date as dt
   from stations
   GROUP BY station_id)
SELECT s.station_id,
      c.dt,
      s.value
FROM complete_dates_station c
   left outer join stations s
   on c.station_id = s.station_id and
   c.dt = s.date;

產生以下結果：

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| NULL    | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

有什麼方法可以優化第一個查詢，或者使用更簡單的方法來填補我station在第二個查詢中的空白？我已經嘗試在源表中使用多列索引，但查詢仍然需要很多時間。

您還有一個表格，其中包含帶有標識符的唯一電台，可能如下所示：

CREATE TABLE uniq_stations (station_id text);
INSERT INTO uniq_stations VALUES
('001'),
('002'),
('003');

會有更多的列，與我們無關。

這應該會快得多：

SELECT station_id, s.value, date
FROM   uniq_stations u
CROSS  JOIN (
  SELECT generate_series (timestamp '2014-01-01'
                        , timestamp '2014-01-04'
                        , interval  '1 day')::date
  ) d(date)
LEFT   JOIN stations s USING (station_id, date)
ORDER  BY station_id, date;  -- optional

db<>在這裡擺弄

您根本不需要LATERAL加入，每個站點的日期系列都是相同的。只有 aCROSS JOIN建構完整的站和天數的笛卡爾積，然後 aLEFT [OUTER] JOIN到表中的現有組合stations（其內容的不幸表名，順便說一句。）。LATERAL連接很棒，在需要時。但是普通連接更快。

此外，這會填充所有天數缺失的電台，如果沒有uniq_stations. 您可能有也可能沒有這種情況。

這個難題中昂貴的部分之一是辨識獨特的電台。如果我們可以使用添加的uniq_stations提供我們需要的東西，我們可以完全跳過一個任務。否則，我們可能會使用DISTINCT ON遞歸 CTE 來使用匹配索引。看：

• 優化 GROUP BY 查詢以檢索每個使用者的最新行
• 在每個 GROUP BY 組中選擇第一行？

仍然比從表中讀取唯一行更昂貴，但已經比你擁有的要快得多*。*坦率地說，這是對 CPU 週期的極大浪費。

最後，即使是大表，多列索引stations (station_id, date)也應該提供一流的性能stations。（使用該表中更高百分比的行會使索引不那麼重要。）

我使用是有原因的generate_series (timestamp, timestamp, interval)：

• 在 PostgreSQL 中生成兩個日期之間的時間序列

有關的：

• 從 generate_series() 填寫記錄集中缺失的日期
• 在 postgres 中加入對 generate_series 的計數查詢，並將 Null 值檢索為“0”

旁白：您station_id可能應該輸入integer. 如果數字超過 999，則比 更快text，也更小。

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