在PostgreSQL中动态生成列

概述我已经看到有一些像这样的类似问题,但我不知道如何自己编码.请记住,我只是这个领域的初学者. 基本上我想像这样转动表格： zoom | day | point zoom | 2015-10-01 | 2015-10-02 | ......------+-----------+------- ---> ------+------------+------------ 我已经看到有一些像这样的类似问题,但我不知道如何自己编码.请记住,我只是这个领域的初学者.

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

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 pointFROM provinceWHERE 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 stringRETURN 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.我想接下来的问题将是如何自动执行函数,这也是在同一个链接上提到的,但是在前面的步骤中被引用了.

解决方法 您的示例的基本交叉表查询很简单：

SELECT * FROM crosstab(       'SELECT zoom,day2 int);

但不是动态列名称或动态列数.
作为妥协,您可以拥有固定数量的列,只填充前导列.基本：

> PostgreSQL Crosstab Query

动态？

crosstab_hash不会帮助您使用动态列名.它可以在不键入列定义列表的情况下重复使用,但不能用于动态列名.例子：

> Dynamically generate columns for crosstab in PostgreSQL
> Sql: Transposing rows into columns

对于真正动态的列名,您需要两次往返服务器.是使用第一个查询检索列名以构建第二个查询,还是创建游标或临时表或预准备语句.无论您尝试什么,都需要两次往返. sql希望在调用时知道返回类型.

我最接近“动态”调用的是我在此相关答案中定义的自定义crosstab_n()函数：

> Dynamic alternative to pivot with CASE and GROUP BY

或者你放弃了一个完全动态的交叉表查询的想法(因为,你知道,这是不可能的)并使用两步工作流程,如上所述.

>让函数生成交叉表查询文本.您可以使用此处提供的功能(并根据您的需要进行调整！)：

> Execute a dynamic crosstab query

特别是,删除GROUP BY 1,2,因为您不在交叉列表之前聚合行.
>执行生成的功能.

为了完整起见,Postgres 9.6(刚刚发布)中还有新的\crosstabview metacommand in psql – 具有类似的功能,它可以显示动态列名称(附加动态名称发生在psql客户端,而不是Postgres服务器中).

总结

以上是内存溢出为你收集整理的在PostgreSQL中动态生成列全部内容，希望文章能够帮你解决在PostgreSQL中动态生成列所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。