如何在PostgreSQL中生成一个虚拟表来生成日期序列?

日期列表

使用 generate_series function 要获取可添加到日期中以获取日期列表的数字列表,请执行以下操作:

SELECT CURRENT_DATE + s.a AS dates 
  FROM generate_series(0,14,7) as s(a);

结果:

dates
------------
2004-02-05
2004-02-12
2004-02-19

旋转

问题的后一部分处理结果集的数据透视——将行数据转换为列数据。Pivot和UnPivot是ANSI,但我不认为它们是 supported by PostgreSQL currently . 最一致支持的查询透视方法是使用聚合函数:

   SELECT t.account,
          SUM(CASE WHEN t.date = '2010-01-01' THEN t.amount END) AS '2010-01-01',
          SUM(CASE WHEN t.date = '2010-01-02' THEN t.amount END) AS '2010-01-02',
          SUM(CASE WHEN t.date = '2010-01-03' THEN t.amount END) AS '2010-01-03',
          SUM(t.amount) AS Balance
     FROM (SELECT CURRENT_DATE + s.a AS dates 
             FROM generate_series(0,14,7) as s(a)) x
LEFT JOIN TRANSACTIONS y ON y.date = x.date
 GROUP BY t.account

动态列

…意味着 dynamic SQL .