PostgreSQL：為表中的每個組生成一系列日期

我balances在 PostgreSQL 9.3 中有一個如下所示的表：

CREATE TABLE balances (
 user_id INT
, balance INT
, as_of_date DATE
);

INSERT INTO balances (user_id, balance, as_of_date) VALUES
 (1, 100, '2016-01-03')
, (1,  50, '2016-01-02')
, (1,  10, '2016-01-01')
, (2, 200, '2016-01-01')
, (3,  30, '2016-01-03');

它僅包含使用者進行交易的日期的餘額。我需要它為每個使用者在給定日期範圍內的每個日期包含一行餘額。

• 如果使用者在範圍內的給定日期沒有一行，我需要使用他們前一天的餘額。
• 如果使用者在範圍內的給定日期之後創建了他們的帳戶，我需要避免為該使用者/日期組合創建一行。

我可以參考一個accounts表格來獲取使用者的create_date：

CREATE TABLE accounts (
 user_id INT
, create_date DATE
);

INSERT INTO accounts (user_id, create_date) VALUES
 (1, '2015-12-01')
, (2, '2015-12-31')
, (3, '2016-01-03');

我想要的結果如下所示：

+---------+---------+--------------------------+
| user_id | balance |        as_of_date        |
+---------+---------+--------------------------+
|       1 |     100 | 2016-01-03T00:00:00.000Z |
|       1 |      50 | 2016-01-02T00:00:00.000Z |
|       1 |      10 | 2016-01-01T00:00:00.000Z |
|       2 |     200 | 2016-01-03T00:00:00.000Z |
|       2 |     200 | 2016-01-02T00:00:00.000Z |
|       2 |     200 | 2016-01-01T00:00:00.000Z |
|       3 |      30 | 2016-01-03T00:00:00.000Z |
+---------+---------+--------------------------+

請注意，已為使用者 2 添加了2016-01-02和的行，從;2016-01-03結轉之前的餘額。2016-01-01並且沒有為創建於 的使用者 3 添加任何行2016-01-03。

要在日期範圍內生成一系列日期，我知道我可以使用：

SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d

…但我正在努力LEFT JOIN處理該系列，每組行都按user_id.

1.CROSS JOIN子LEFT JOIN LATERAL查詢

SELECT a.user_id, COALESCE(b.balance, 0) AS balance, d.as_of_date
FROM   (
  SELECT d::date AS as_of_date  -- cast to date right away
  FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
  ) d
JOIN   accounts a ON a.create_date <= d.as_of_date
LEFT   JOIN LATERAL (
  SELECT balance
  FROM   balances
  WHERE  user_id = a.user_id
  AND    as_of_date <= d.as_of_date
  ORDER  BY as_of_date DESC
  LIMIT  1
  ) b ON true
ORDER  BY a.user_id, d.as_of_date;

返回您想要的結果 - 除了在您的範例中as_of_date是實際的date，而不是timestamp類似的。那應該更合適。

已創建但還沒有任何交易的使用者以餘額 0 列出。您沒有定義如何處理極端情況。

而是使用timestamp輸入generate_series()：

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

使用多列索引支持這一點對於性能至關重要：

CREATE INDEX balances_multi_idx ON balances (user_id, as_of_date DESC, balance);

就在本週，關於 SO 的案例非常相似：

• 每週匯總最近加入的記錄

在那裡找到更多解釋。

2. CROSS JOIN, LEFT JOIN , 視窗函式

SELECT user_id
    , COALESCE(max(balance) OVER (PARTITION BY user_id, grp
                                  ORDER BY as_of_date), 0) AS balance
    , as_of_date
FROM  (
  SELECT a.user_id, b.balance, d.as_of_date
       , count(b.user_id) OVER (PARTITION BY user_id ORDER BY as_of_date) AS grp
  FROM   (
     SELECT d::date AS as_of_date  -- cast to date right away
     FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
     ) d
  JOIN   accounts a ON a.create_date <= d.as_of_date
  LEFT   JOIN balances b USING (user_id, as_of_date)
  ) sub
ORDER  BY user_id, as_of_date;

結果相同。如果您有上面提到的多列索引並且可以從中獲得僅索引掃描，那麼第一個解決方案很可能更快。

主要功能是對值進行計數以形成組。由於count()不計算值，所有沒有餘額的日期與最近的餘額NULL屬於同一組 ( )。grp然後在相同的視窗框架上使用一個簡單max()的擴展grp來複製懸空間隙的最後一個餘額。

有關的：

• 選擇最長的連續序列
• 如何獲得最大的行序列？

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