HAWQ中的行列转置

        行列转置是ETL或报表系统中的常见需求,HAWQ提供的内建函数和过程语言编程功能,使行列转置操作的实现变得更为简单。

一、行转列

1. 固定列数的行转列

        原始数据如下:

test=# select * from score;
 name | subject | score 
------+---------+-------
 张三 | 语文    |    80
 张三 | 数学    |    70
 张三 | 英语    |    60
 李四 | 语文    |    90
 李四 | 数学    |   100
 李四 | 英语    |    80
(6 rows)

        要得到以下的结果:

 name | 语文 | 数学 | 英语 
------+------+------+------
 张三 |   80 |   70 |   60
 李四 |   90 |  100 |   80

(1)使用标准SQL实现

select name,   
       max(case when subject = '语文' then score else 0 end) as "语文",  
       max(case when subject = '数学' then score else 0 end) as "数学",  
       max(case when subject = '英语' then score else 0 end) as "英语"   
  from score  
 group by name order by name;

        执行结果如下所示:

test=# select name,   
test-#        max(case when subject = '语文' then score else 0 end) as "语文",  
test-#        max(case when subject = '数学' then score else 0 end) as "数学",  
test-#        max(case when subject = '英语' then score else 0 end) as "英语"   
test-#   from score  
test-#  group by name order by name;  
 name | 语文 | 数学 | 英语 
------+------+------+------
 张三 |   80 |   70 |   60
 李四 |   90 |  100 |   80
(2 rows)

        此方法简单并具有通用性,所有SQL数据库都支持。

(2)使用内建聚合函数实现

select name,
       split_part(split_part(tmp,',',3),':',2) as "语文",  
       split_part(split_part(tmp,',',1),':',2) as "数学",  
       split_part(split_part(tmp,',',2),':',2) as "英语"  
  from (select name,string_agg(subject||':'||score,',' order by subject) as tmp 
          from score 
         group by name) as t 
 order by name;

        执行结果如下所示:

test=# select name,
test-#        split_part(split_part(tmp,',',3),':',2) as "语文",  
test-#        split_part(split_part(tmp,',',1),':',2) as "数学",  
test-#        split_part(split_part(tmp,',',2),':',2) as "英语"  
test-#   from (select name,string_agg(subject||':'||score,',' order by subject) as tmp 
test(#           from score 
test(#          group by name) as t 
test-#  order by name;  
 name | 语文 | 数学 | 英语 
------+------+------+------
 张三 | 80   | 70   | 60
 李四 | 90   | 100  | 80
(2 rows)

        在子查询中按name列分组聚合,使用string_agg函数将同一name的subject和score按subject顺序连接成字符串。subject与score用‘:’连接,段分隔符为‘,’。子查询的结果为:

test=# select name,string_agg(subject||':'||score,',' order by subject) as tmp 
test-#   from score 
test-#  group by name;
 name |           tmp            
------+--------------------------
 张三 | 数学:70,英语:60,语文:80
 李四 | 数学:100,英语:80,语文:90
(2 rows)

        外层查询使用两个嵌套的split_part函数,将字符串分隔成列。内层split_part取得subject:score,外层split_part取得相应subject的score。这种方法利用了HAWQ内建的聚合函数,实现简洁。

2. 不定列数的行转列

        原始数据如下:

test=# select * from t1;
 c1 | c2 | c3 
----+----+----
  1 | 我 |  1
  1 | 是 |  2
  1 | 谁 |  3
  2 | 不 |  1
  2 | 知 |  2
  3 | 道 |  1
(6 rows)

        要得到以下的结果,其中列数是不定的:

 c1 | c2 | c3 | c4 
----+----+----+----
  1 | 我 | 是 | 谁
  2 | 不 | 知 | 
  3 | 道 |    | 

        因为结果集列数不固定,必须使用动态SQL实现(HAWQ不支持crosstab函数)。建立如下的PLPGSQL函数:

create or replace function fn_crosstab(refcursor) returns refcursor
as $body$  
declare 
    v_colnum int; 
    v_sqlstring varchar(2000) := 'select c1 '; 
begin
    -- 获得最大列数
    select max(c) into v_colnum from (select count(*) c from t1 group by c1) t;
    
    for i in 1 .. v_colnum loop
        v_sqlstring := v_sqlstring || ', split_part(tmp,'','',' || cast(i as varchar(2)) || ') c' || cast(i+1 as varchar(2)); 
    end loop;

    v_sqlstring := v_sqlstring || ' from (select c1,string_agg(c2,'','' order by c3) as tmp from t1 group by c1) t order by c1';

    -- raise notice '%', v_sqlstring;	
    open $1 for execute v_sqlstring;
    return $1;

end;
$body$ language plpgsql;

        调用函数:

begin;  
select fn_crosstab('cur1');  
fetch all in cur1;  
commit;

        服务器游标默认只能在一个事务中存在,事务结束自动销毁。如果没用BEGIN开启一个事务,任何一条语句都是一个事务,所以select fn_crosstab('cur1')所建立的游标立即被销毁。执行结果如下所示:

test=# begin;  
BEGIN
test=# select fn_crosstab('cur1');  
 fn_crosstab 
-------------
 cur1
(1 row)

test=# fetch all in cur1;  
 c1 | c2 | c3 | c4 
----+----+----+----
  1 | 我 | 是 | 谁
  2 | 不 | 知 | 
  3 | 道 |    | 
(3 rows)

test=# commit;  
COMMIT

二、列转行

1. 单行变多行

        原始数据如下:

test=# select * from book;
 id | name |   tag    
----+------+----------
  1 | Java | aa,bb,cc
  2 | C++  | dd,ee
(2 rows)

        要得到以下的结果:

 name | tag  | rn 
------+------+----
 Java | aa   |  1
 Java | bb   |  2
 Java | cc   |  3
 C++  | dd   |  1
 C++  | ee   |  2

        HAWQ 2.1.1.0基于PostgreSQL 8.2.15,因此还不包含generate_subscripts()、array_length()、unnest(array) with ordinality等函数功能。为了给每个name的tag按原始位置增加序号,需要建立以下函数,返回数组值及其对应的下标:

create or replace function f_unnest_ord(anyarray, out val anyelement, out ordinality integer)
returns setof record language sql immutable as
'select $1[i], i - array_lower($1,1) + 1
   from generate_series(array_lower($1,1), array_upper($1,1)) i';

        然后执行查询:

select name, (rec).val tag, (rec).ordinality rn
  from (select *, f_unnest_ord(arr) as rec
          from (select id, name, string_to_array(tag, ',') arr from book) t) t
 order by id, rn;

        执行结果如下所示:

test=# select name, (rec).val tag, (rec).ordinality rn
test-#   from (select *, f_unnest_ord(arr) as rec
test(#           from (select id, name, string_to_array(tag, ',') arr from book) t) t
test-#  order by id, rn;
 name | tag | rn 
------+-----+----
 Java | aa  |  1
 Java | bb  |  2
 Java | cc  |  3
 C++  | dd  |  1
 C++  | ee  |  2
(5 rows)

2. 多列转多行

       原始数据如下:

test=# select * from t1;
 c1 | c2 | c3 | c4 
----+----+----+----
  1 | 我 | 是 | 谁
  2 | 不 | 知 | 
  3 | 道 |    | 
(3 rows)

        要得到以下结果:

 c1 | c2 | c3 
----+----+----
  1 | 我 |  1
  1 | 是 |  2
  1 | 谁 |  3
  2 | 不 |  1
  2 | 知 |  2
  3 | 道 |  1

        也以看到,原数据只有三行,而结果是六行数据。要达到想要的结果,最重要的是如何从现有的行构造出新的数据行。下面用三种方法实现。 (1)最直接的方法——union         用SQL的并集操作符union是最容易想到的方法。

select * 
  from (select c1,c2,1 c3 from t1 
         union all 
        select c1,c3,2 from t1 
         union all 
        select c1,c4,3 from t1) t 
 where c2 <> '' 
 order by c1, c3;

        查询结果如下:

test=# select * 
test-#   from (select c1,c2,1 c3 from t1 
test(#          union all 
test(#         select c1,c3,2 from t1 
test(#          union all 
test(#         select c1,c4,3 from t1) t 
test-#  where c2 <> '' 
test-#  order by c1, c3;
 c1 | c2 | c3 
----+----+----
  1 | 我 |  1
  1 | 是 |  2
  1 | 谁 |  3
  2 | 不 |  1
  2 | 知 |  2
  3 | 道 |  1
(6 rows)

(2)最灵活的方法——笛卡尔积

        union虽然直接了当,但太过死板。如果列很多,需要叠加很多的union all,凸显乏味。更灵活的方法是通过笛卡尔积运算构造数据行,这种方法的关键在于需要一个所需行数的辅助表。许多关系数据库都提供相应的方法,例如Oracle用connect by level、MySQL用数字辅助表、PostgreSQL用generate_serie函数等。

select * 
  from (select c1,
               case when t2=1 then c2
                    when t2=2 then c3
                    else c4
                end c2,
               t2 c3
          from (select * from t1, generate_series(1,3) t2) t) t
 where c2 <> ''
 order by c1, c3;

        查询结果如下:

test=# select * 
test-#   from (select c1,
test(#                case when t2=1 then c2
test(#                     when t2=2 then c3
test(#                     else c4
test(#                 end c2,
test(#                t2 c3
test(#           from (select * from t1, generate_series(1,3) t2) t) t
test-#  where c2 <> ''
test-#  order by c1, c3;
 c1 | c2 | c3 
----+----+----
  1 | 我 |  1
  1 | 是 |  2
  1 | 谁 |  3
  2 | 不 |  1
  2 | 知 |  2
  3 | 道 |  1
(6 rows)

(3)最独特的方法——unnest

        前面两种是相对通用的方法,关系数据库的SQL都支持,而unnest是PostgreSQL独有的函数。有了前面的基础,这个实现就比较简单了,只要执行下面的查询即可:

select * 
  from (select c1,split_part(unnest(c2),':',1) c2, split_part(unnest(c2),':',2) c3 
          from (select c1,string_to_array(c2,',') c2 
                  from (select c1,coalesce(c2,'')||':1,'||coalesce(c3,'')||':2,'||coalesce(c4,'')||':3' c2 
                          from t1) t) t) t 
 where c2 <> '' 
 order by c1, c3;

        查询结果如下:

test=# select * 
test-#   from (select c1,split_part(unnest(c2),':',1) c2, split_part(unnest(c2),':',2) c3 
test(#           from (select c1,string_to_array(c2,',') c2 
test(#                   from (select c1,coalesce(c2,'')||':1,'||coalesce(c3,'')||':2,'||coalesce(c4,'')||':3' c2 
test(#                           from t1) t) t) t 
test-#  where c2 <> '' 
test-#  order by c1, c3;
 c1 | c2 | c3 
----+----+----
  1 | 我 | 1
  1 | 是 | 2
  1 | 谁 | 3
  2 | 不 | 1
  2 | 知 | 2
  3 | 道 | 1
(6 rows)

参考:

PostgreSQL unnest() with element number

POSTGRESQL交叉表的实现

PostgreSQL 一行变多行

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Golang语言社区

转--Go语言用堆排序的方法进行一千万个int随机数排序

上篇文章用的是quicksort方法排序,但是如果用快速排序法对重复率很高的slice排序的时候,时间复杂度会激增,速度相当慢 所以尝试了一下堆排序,实验结果,...

3097
来自专栏King_3的技术专栏

leetcode-914-卡牌分组

1、这道题给定一个vector,vector中存放着卡牌的数字,比如1、2、3、4这样子,你需要把这些卡牌分成多组。

1102
来自专栏我是东东强

数据结构之栈与队列(优先队列/堆)

栈与队列是两种重要的特殊线性表,从结构上讲,两者都是线性表,但从操作上讲,两者支持的基本操作却只是线性表操作的子集,是操作受限制的线性表。栈与队列两者最大的区别...

842
来自专栏醒者呆

Knowledge_SPA——精研查找算法

首先保证这一篇分析查找算法的文章,气质与大部分搜索引擎搜索到的文章不同,主要体现在代码上面,会更加高级,会结合到很多之前研究过的内容,例如设计模式,泛型等。这...

2565
来自专栏对角另一面

lodash源码分析之baseFindIndex中的运算符优先级

本文为读 lodash 源码的第十篇,后续文章会更新到这个仓库中,欢迎 star:pocket-lodash

1868
来自专栏玄魂工作室

如何学Python 第十八课 初见位运算

在上一篇文章里我们介绍了类和面向对象为编程带来的方便。今天我们来说点稍微简单的——位运算。 位运算会用到位运算符。但是我们今天不介绍具体的代码上的操作,而是主要...

2657
来自专栏决胜机器学习

PHP数据结构(十六) ——B树

PHP数据结构(十六)——B树 (原创内容,转载请注明来源,谢谢) 一、概述 B树在很多地方被称为“B-树”,因为B树的原英文名称为B-tre...

42411
来自专栏云霄雨霁

删数问题

1880
来自专栏后端技术探索

从头到尾解析Hash 表算法

问题描述 百度面试题: 搜索引擎会通过日志文件把用户每次检索使用的所有检索串都记录下来,每个查询串的长度为1-255字节。 假设目前有一千万个记...

924
来自专栏企鹅号快讯

详解大规模数据处理利器 BloomFilter 算法

转自:heaad http://www.cnblogs.com/heaad/archive/2011/01/02/1924195.html Bloom Filt...

1767

扫码关注云+社区