Postgres查询获取数据的日期

Question

我用的是PostgreSQL。
我有一个如下的表格:

ID   product_id   Date         Qty
-----------------------------------
1    12           2008-06-02   50
2     3           2008-07-12    5
3    12           2009-02-10   25
4    10           2012-11-01   22
5     2           2011-03-25    7

现在我想要的结果如下(即过去4年的产品数量字段的总和):

product_id
QTY(current_year)  
QTY( current year + last_year)  
QTY_last2_years 
QTY > 2 years

Answer 1

SELECT product_id
      ,sum(CASE mydate >=  x.t THEN qty END) AS qty_current_year
      ,sum(CASE mydate >= (x.t - interval '1 y') THEN qty END) AS qty_since_last_year
      ,sum(CASE mydate >= (x.t - interval '2 y')
            AND mydate <   x.t THEN qty END) AS qty_last_2_year
      ,sum(CASE mydate <  (x.t - interval '2 y') THEN qty END) AS qty_older
FROM   tbl
CROSS  JOIN (SELECT date_trunc('year', now()) AS t) x -- calculate once
GROUP  BY 1;

重新使用计算出的当前年份I CROSS JOIN的开头作为子查询x。