在PostgreSQL中动态生成列

基本上我想像这样转动表格：

zoom |    day     | point         zoom | 2015-10-01 |  2015-10-02 | ......
------+-----------+-------  ---> ------+------------+-------------+
   1 | 2015-10-01 |   201            1 |    201     |     685     |
   2 | 2015-10-01 |    43            2 |     43     |     346     | 
   3 | 2015-10-01 |    80            3 |     80     |     534     | 
   4 | 2015-10-01 |   324            4 |    324     |     786     | 
   5 | 2015-10-01 |    25            5 |     25     |     685     |
   1 | 2015-10-02 |   685 
   2 | 2015-10-02 |   346 
   3 | 2015-10-02 |   534 
   4 | 2015-10-02 |   555 
   5 | 2015-10-02 |   786
   :
   :
   :

时间可以变化.

左边的结果我得到：

SELECT 
zoom,to_char(date_trunc('day',time),'YYYY-MM-DD') AS day,count(*) as point
FROM province
WHERE time >= '2015-05-01' AND time < '2015-06-01'
GROUP BY to_char(date_trunc('day','YYYY-MM-DD'),zoom;

我已经读过如果我使用count会有一些问题,而且如果我使用CASE和GROUP BY会更好,但是我不知道如何使用CASE.

交叉表本身不支持动态创建列名,但如果我理解正确的话,可以使用crosstab_hash实现.

这可能是一个很好的解决方案：http://okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.html然而我一直坚持尝试自己编程.

我必须经常使用这种旋转,所以我会在其背后提供任何帮助和其他解释.

EDIT1

我试图弄清楚交叉表如何与日期一起工作,目前没有返回列的动态名称.稍后我将解释原因.这是主要问题.对于此示例,我仅使用2个日期.

基于@Erwin Brandstetter回答：

SELECT * FROM crosstab(
       'SELECT zoom,day,point
        FROM   province
        ORDER  BY 1,2',$$VALUES ('2015-10-01'::date),('2015-10-02')$$)
AS ct (zoom text,day1 int,day2 int);

返回的结果是：

zoom |    day1    |    day2     | 
-----+------------+-------------+
   1 |    201     |     685     |
   2 |     43     |     346     | 
   3 |     80     |     534     | 
   4 |    324     |     786     |

我想要得到这个

zoom | 2015-10-01 |  2015-10-02 | 
-----+------------+-------------+
   1 |    201     |     685     |
   2 |     43     |     346     | 
   3 |     80     |     534     | 
   4 |    324     |     786     |

但我的查询不起作用：

SELECT *
FROM crosstab(
      'SELECT *
       FROM province
       ORDER  BY 1,2')
AS ct (zoom text,"2015-10-01" date,"2015-10-02" date);

ERROR:  return and sql tuple descriptions are incompatible

Edit1,Q1.为什么这不起作用,我怎么能返回那样的结果呢？

我已经阅读了@Erwin Brandstetter为我提供的链接,特别是这个：Execute a dynamic crosstab query.我复制/粘贴了他的功能：

CREATE OR REPLACE FUNCTION pivottab(_tbl regclass,_row text,_cat text,_expr text,_type regtype)  
RETURNS text AS
$func$
DECLARE
   _cat_list text;
   _col_list text;
BEGIN
-- generate categories for xtab param and col definition list    
EXECUTE format(
 $$SELECT string_agg(quote_literal(x.cat),'),('),string_agg(quote_ident  (x.cat),%L)
   FROM  (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$$,' ' || _type || ',',_cat,_tbl)
INTO  _cat_list,_col_list;

-- generate query string
RETURN format(
'SELECT * FROM crosstab(
   $q$SELECT %I,%I,%s
      FROM   %I
      GROUP  BY 1,2
      ORDER  BY 1,2$q$,$c$VALUES (%5$s)$c$
   ) ct(%1$I text,%6$s %7$s)',_row,_expr,_tbl,_cat_list,_col_list,_type
);

END
$func$LANGUAGE plpgsql;

并用查询调用它

SELECT pivottab('province','zoom','day','point','date');

功能归还给我：

pivottab                         
----------------------------------------------------------
 SELECT * FROM crosstab(                                 +
    $q$SELECT zoom,point                           +
       FROM   province                                   +
       GROUP  BY 1,2                                    +
       ORDER  BY 1,2$q$                                +,$c$VALUES ('2015-10-01'),('2015-10-02')$c$         +
    ) ct(zoom text,"2015-10-02" date)
(1 row)

所以当我编辑查询并添加时; (很高兴;已经存在了)我得到了：

ERROR:  column "province.point" must appear in the GROUP BY clause or be used in an aggregate function

Edit1,Q2.任何想法如何解决这个问题？

Edit1,Q3.我想接下来的问题将是如何自动执行函数,这也是在同一个链接上提到的,但是在前面的步骤中被引用了.