# 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 一行变多行

76 篇文章27 人订阅

0 条评论

## 相关文章

### 任意半径局部直方图类算法在PC中快速实现的框架。

在图像处理中，局部算法一般来说，在很大程度上会获得比全局算法更为好的效果，因为他考虑到了图像领域像素的信息，而很多局部算法可以借助于直方图获得加速。同...

1988

### Learning to Rank概述

Learning to Rank，即排序学习，简称为 L2R，它是构建排序模型的机器学习方法，在信息检索、自然语言处理、数据挖掘等场景中具有重要的作用。其达到的...

3425

### 一步步教你弹性框架-下篇

HTML5学堂：本文继续为大家讲解弹性框架，在前两篇文章当中，我们从最基本的来回运动，讲解到缓冲运动、有摩擦力的运动。基本实现了弹性动画效果。今天我们主要来进行...

3694

2423

902

1080

752

1173

26810

### tensorflow编程: Layers (contrib)

min(max(features, 0), 6)。即对 tf.nn.relu 的优化，防止 relu过后 某些 极端值 依然 大于6

1022