开发者社区> 问答> 正文

PostgreSQL 支持SQL Server 的PIVOT用法吗

PostgreSQL 有没有对应的用法?
如下:

下面的代码示例生成一个两列四行的表。
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

下面是结果集:
DaysToManufacture          AverageCost
0                          5.0885
1                          223.88
2                          359.1082
4                          949.4105
没有定义 DaysToManufacture 为 3 的产品。
以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天,即使结果为 NULL。
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

下面是结果集:
Cost_Sorted_By_Production_Days    0         1         2           3       4       
AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

展开
收起
德哥,digoal 2015-12-23 11:19:06 8595 0
1 条回答
写回答
取消 提交回答
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    支持,使用tablefunc插件来实现,如下:

    一般在提交报表的时候,可能会使用到对数据表的行列变换的需求。
    如以下数据:
    digoal=> select * from tbl_sellers_info order by seller,se_year,se_month;
      seller  | se_year | se_month | se_amount 
    ----------+---------+----------+-----------
     卡洛斯   |    2011 |        3 |        12
     卡洛斯   |    2011 |        4 |        45
     卡洛斯   |    2011 |        5 |        56
     德哥     |    2010 |        1 |    123456
     德哥     |    2010 |        2 |    234567
     德哥     |    2010 |        3 |    345678
     德哥     |    2010 |        4 |    345678
     德哥     |    2010 |        5 |    567890
     德哥     |    2010 |        6 |    123456
     德哥     |    2010 |        7 |    234567
     德哥     |    2010 |        8 |    345678
     德哥     |    2010 |        9 |    345678
     德哥     |    2010 |       10 |    567890
     德哥     |    2010 |       11 |    123456
     德哥     |    2010 |       12 |    234567
     德哥     |    2011 |        1 |    123456
     德哥     |    2011 |        2 |    234567
     德哥     |    2011 |        3 |    345678
     德哥     |    2011 |        4 |    345678
     德哥     |    2011 |        5 |    567890
     罗纳尔多 |    2011 |        2 |        20
     罗纳尔多 |    2011 |        3 |        30
     罗纳尔多 |    2011 |        4 |        40
     罗纳尔多 |    2011 |        5 |        50
     贝克汉姆 |    2010 |       11 |        12
     贝克汉姆 |    2010 |       12 |        23
     贝克汉姆 |    2011 |        1 |        12
     贝克汉姆 |    2011 |        2 |        23
     贝克汉姆 |    2011 |        3 |        34
     贝克汉姆 |    2011 |        4 |        45
     贝克汉姆 |    2011 |        5 |        56
    (31 rows)
    
     例如要按照每个SELLER出具每年的月度销售情况,可能要转换成:
    图1 
    
    在PostgreSQL9.1里面有一个比较好用的extension,tablefunc,可以轻松的完成行列转换。
    下面会有例子来简单的描述以下这个extension.
    
    1. 安装tablefunc extension.
    \c digoal postgres
    You are now connected to database "digoal" as user "postgres".
    digoal=# create extension tablefunc;
    CREATE EXTENSION
    digoal=# \c digoal digoal
    You are now connected to database "digoal" as user "digoal".
    #安装完后会创建几个TYPE,几个函数.
    type:
    CREATE TYPE tablefunc_crosstab_N AS (     row_name TEXT,     category_1 TEXT,     category_2 TEXT,         .         .         .     category_N TEXT );
     public | tablefunc_crosstab_2 | tablefunc_crosstab_2 | tuple |          | 
     public | tablefunc_crosstab_3 | tablefunc_crosstab_3 | tuple |          | 
     public | tablefunc_crosstab_4 | tablefunc_crosstab_4 | tuple |          | 
    
    function
    normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
    crosstab(text sql) crosstab(text sql, int N)
    crosstabN(text sql)
    crosstab(text source_sql, text category_sql)
    connectby(text relname, text keyid_fld, text parent_keyid_fld           [, text orderby_fld ], text start_with, int max_depth           [, text branch_delim ])
    
    2. 创建测试表
    create table tbl_sellers_info (seller text,se_year int,se_month int,se_amount int);
    insert into tbl_sellers_info values ('德哥',2011,01,123456);
    insert into tbl_sellers_info values ('德哥',2011,02,234567);
    insert into tbl_sellers_info values ('德哥',2011,03,345678);
    insert into tbl_sellers_info values ('德哥',2011,04,345678);
    insert into tbl_sellers_info values ('德哥',2011,05,567890);
    insert into tbl_sellers_info values ('贝克汉姆',2011,01,12);
    insert into tbl_sellers_info values ('贝克汉姆',2011,02,23);
    insert into tbl_sellers_info values ('贝克汉姆',2011,03,34);
    insert into tbl_sellers_info values ('贝克汉姆',2011,04,45);
    insert into tbl_sellers_info values ('贝克汉姆',2011,05,56);
    insert into tbl_sellers_info values ('卡洛斯',2011,03,12);
    insert into tbl_sellers_info values ('卡洛斯',2011,04,45);
    insert into tbl_sellers_info values ('卡洛斯',2011,05,56);
    insert into tbl_sellers_info values ('罗纳尔多',2011,02,20);
    insert into tbl_sellers_info values ('罗纳尔多',2011,03,30);
    insert into tbl_sellers_info values ('罗纳尔多',2011,04,40);
    insert into tbl_sellers_info values ('罗纳尔多',2011,05,50);
    insert into tbl_sellers_info values ('德哥',2010,01,123456);
    insert into tbl_sellers_info values ('德哥',2010,02,234567);
    insert into tbl_sellers_info values ('德哥',2010,03,345678);
    insert into tbl_sellers_info values ('德哥',2010,04,345678);
    insert into tbl_sellers_info values ('德哥',2010,05,567890);
    insert into tbl_sellers_info values ('德哥',2010,06,123456);
    insert into tbl_sellers_info values ('德哥',2010,07,234567);
    insert into tbl_sellers_info values ('德哥',2010,08,345678);
    insert into tbl_sellers_info values ('德哥',2010,09,345678);
    insert into tbl_sellers_info values ('德哥',2010,10,567890);
    insert into tbl_sellers_info values ('德哥',2010,11,123456);
    insert into tbl_sellers_info values ('德哥',2010,12,234567);
    insert into tbl_sellers_info values ('贝克汉姆',2010,11,12);
    insert into tbl_sellers_info values ('贝克汉姆',2010,12,23);
    
    3. 下面我们用crosstab(text source_sql, text category_sql) 来满足需求.
    select substr(seller,1,char_length(seller)-4) seller,substr(seller,char_length(seller)-3) se_year,jan  ,feb  ,mar  ,apr  ,may  ,jun  ,jul  ,aug  ,sep  ,oct  ,nov  ,dec from crosstab('select seller||se_year,se_month,se_amount from tbl_sellers_info order by 1', 
    'select distinct se_month from tbl_sellers_info order by 1') as (seller text,Jan numeric,feb numeric,mar numeric,apr numeric,may numeric,jun numeric,jul numeric,aug numeric,sep numeric,oct numeric,nov numeric,dec numeric) order by 1,2;
    
    # 分析一下这个函数:
    参数一:(这个是需要进行行列变换的源SQL)
    select seller||se_year,se_month,se_amount from tbl_sellers_info order by 1
    参数二:(这个在这里代表的是月份,也就是se_month的值)
    select distinct se_month from tbl_sellers_info order by 1
    或者
    select * from generate_series(1,12) order by 1
    出来的结果都是一样的。
    输出:
    (seller text,Jan numeric,feb numeric,mar numeric,apr numeric,may numeric,jun numeric,jul numeric,aug numeric,sep numeric,oct numeric,nov numeric,dec numeric)
    seller代表的是seller||se_year,这里必须这么做,因为需要按照这个来分组.
    后面的就是月份了,数据类型是和se_amount一致就行了。
    
    输出结果就是前面提到的图片。
    行记录中没有的月份在变换后该月数据为空.例如卡洛斯只有2011年的3,4,5月份有数据,那么其他月份就是空白的。
    
    # 另外一个要注意的是,输出结果的顺序是select * from generate_series(1,12) order by 1来决定的.
    # 例如
    select * from generate_series(1,12) order by 1 desc 那么输出的顺序就是反的,因此必须严格的排序.
    图2 
    
    # 另外的几个crosstab函数用法与此类似,只不过没有这个灵活。
    # 而normal_rand用于返回一系列正态分布值。connect_by用于做异构查询(前面我有写过用with rescursive来实现)。就不再演示。
    参考:
    http://www.postgresql.org/docs/9.5/static/tablefunc.html

    图1
    938156097377363289
    图2
    3856770130890170969

    2019-07-17 18:20:42
    赞同 1 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
2023云栖大会:和客户一起玩转PolarDB新特性 立即下载
2023云栖大会:PolarDB for AI 立即下载
2023云栖大会:AnalyticDB PostgreSQL 立即下载

相关镜像