sql - Postgres 生成系列

Question

我想要的是对表格进行统计，为此我使用了 generate_series();

这是我正在做的:

SELECT x.month, amount
FROM (SELECT generate_series(
                 min(date_trunc('month', date)),
                 max(date_trunc('month', date)),
                 '1 month'
      ) AS month
      FROM table
      WHERE user_id = 55 AND ...
) x
LEFT JOIN (
      SELECT SUM(amount) AS amount, date_trunc('month', date) AS month
      FROM table
      WHERE user_id = 55 AND ...
      GROUP BY month
) q ON q.month = x.month
ORDER BY month

这很好用，但是当我想应用过滤器(例如获取特定用户的数量)时，我必须应用它们两次。有没有办法避免过滤两次，或者以更有效的方式重写它，因为我不确定这样做是否正确？

Answer 1

你可以写成WITH query为此:

WITH month_amount AS
(
    SELECT
        sum(amount) AS amount,
        date_trunc('month', date) AS month
    FROM Amount
    WHERE user_id = 55 -- AND ...
    GROUP BY month
)
SELECT month, amount
FROM
    (SELECT generate_series(min(month), max(month), '1 month') AS month
    FROM month_amount) x
LEFT JOIN month_amount
USING (month)
ORDER BY month;

示例结果:

SELECT * FROM amount WHERE user_id = 55;
 amount_id | user_id | amount |    date    
-----------+---------+--------+------------
         3 |      55 |      7 | 2011-03-16
         4 |      55 |      5 | 2011-03-22
         5 |      55 |      2 | 2011-05-07
         6 |      55 |     18 | 2011-05-27
         7 |      55 |      4 | 2011-06-14
(5 rows)

WITH month_amount ..
         month          | amount 
------------------------+--------
 2011-03-01 00:00:00+01 |     12
 2011-04-01 00:00:00+02 |       
 2011-05-01 00:00:00+02 |     20
 2011-06-01 00:00:00+02 |      4
(4 rows)