ReaderMX.
Halo Tech

halo数据库pivot与unpivot

HJACK约 6 分钟
halo数据库在Oracle兼容模式下，PIVOT 和 UNPIVOT 都是 FROM 子句中的参数，分别将查询输出从行轮换到列，以及从列轮换到行，以便于阅读的格式呈现表格查询结果。
PIVOT
在进行复杂的查询统计，处理大量数据的时候，PIVOT的作用就显得非常突出，它比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。
halo数据库PIVOT 语法如下：
SELECT ...FROM ...PIVOT(pivot_agg_clause  #定义进行聚集的列pivot_for_clause  #定义需要分组和转置的列pivot_in_clause   #定义限定结果集的值的范围)WHERE ...
PIVOT 语法图显示如下：

Image

pivot_agg_clause语法图如下：

Image

pivot_for_clause语法图如下：

Image

pivot_in_clause语法图如下

Image

PIVOT 示例

设置示例表和数据并使用它们运行后续示例查询。
CREATE TABLE part (    partname varchar2(32),    manufacturer varchar2(32),    quality int,    price decimal(12, 2));
INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);
INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);
INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);
partname 上的 PIVOT，在 price 上有一个 AVG 聚合，halo数据库查询将生成以下输出。
halo0root=# SELECT * FROM (SELECT partname, price FROM part) PIVOT (AVG(price) FOR partname IN ('prop' AS prop, 'rudder' AS rudder, 'wing' AS wing));+---------------------+--------------------+------+|        prop         |       rudder       | wing |+---------------------+--------------------+------+| 10.3333333333333333 | 2.7166666666666667 | 11.5 |+---------------------+--------------------+------+(1 row)
如果上例将 manufacturer 作为隐式列，halo数据库查询将生成以下输出：
halo0root=# SELECT *FROM (SELECT quality, manufacturer FROM part) PIVOT (    count(*) FOR quality IN (1, 2));+----------------+---+---+|  manufacturer  | 1 | 2 |+----------------+---+---+| local parts co | 1 | 1 || big parts co   | 1 | 1 || small parts co | 1 | 0 |+----------------+---+---+(3 rows)
PIVOT 定义中未引用的输入表列被隐式添加到结果表中。就是上一个示例中 manufacturer 列这种情况。上述示例中的 PIVOT 返回与以下查询类似的信息，其中包含 GROUP BY。区别在于 PIVOT 为列 “2” 和 manufacturer列 “small parts co” 返回值 0。GROUP BY 查询不包含相应的行。在大多数情况下，如果一行没有针对给定列的输入数据，则 PIVOT 会插入 NULL。但是，计数聚合不会返回 NULL，0 是默认值。
PIVOT 运算符接受聚合表达式和 IN 运算符的每个值上的可选别名。使用别名自定义列名。如果没有聚合别名，则仅使用 IN 列表别名。否则，将聚合别名附加到列名，并使用下划线将其与列名分开,halo数据库查询将生成以下输出:
halo0root=# SELECT *FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) AS count FOR quality IN (1, 2 AS low));+----------------+---------+-----------+|  manufacturer  | 1_count | low_count |+----------------+---------+-----------+| local parts co |       1 |         1 || big parts co   |       1 |         1 || small parts co |       1 |         0 |+----------------+---------+-----------+(3 rows)

halo数据库PIVOT 的使用说明：

UNPIVOT则比一系列复杂的 LATERAL 语句中所指定的语法更简单和更具可读性。

halo数据库UNPIVOT 语法如下：

SELECT ...FROM ...UNPIVOT(unpivot_val_clause       #定义反转置值的列名unpivot_for_clause       #定义反转置所得到列的列名称unpivot_in_clause        #定义进行反转置已转置列的列表)WHERE ...
UNPIVOT 语法图显示如下：

Image

unpivot_val_clause语法图显示如下：

Image

unpivot_for_clause语法图显示如下：

Image

unpivot_in_clause语法图显示如下：

Image

UNPIVOT 示例

设置示例数据并使用它来运行后续示例。
CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);
INSERT INTO count_by_color VALUES ('high', 15, 20, 7);INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);
红、绿和蓝输入列上的 UNPIVOT,halo数据库查询将生成以下输出:
halo0root=# SELECT *halo0root-# FROM (SELECT red, green, blue FROM count_by_color) halo0root-# UNPIVOT ( cnt FOR color IN (red, green, blue));+-------+-----+| color | cnt |+-------+-----+| red   |  15 || green |  20 || blue  |   7 || red   |  35 || blue  |  40 || red   |  10 || green |  23 || red   |  50 |+-------+-----+(8 rows)
上例显示在默认情况下，将跳过输入列中的 NULL 值，且不会产生结果行。
以下示例显示了带有 INCLUDE NULLS 的 UNPIVOT。以下是halo数据库结果输出：
halo0root=# SELECT *halo0root-# FROM (halo0root(#     SELECT red, green, bluehalo0root(#     FROM count_by_colorhalo0root(# ) UNPIVOT INCLUDE NULLS (cnt FOR color IN (red, green, blue));+-------+-----+| color | cnt |+-------+-----+| red   |  15 || green |  20 || blue  |   7 || red   |  35 || green |     || blue  |  40 || red   |  10 || green |  23 || blue  |     || red   |  50 || green |     || blue  |     |+-------+-----+(12 rows)
上例显示如果已设置 INCLUDING NULLS 参数，NULL 输入值将生成结果行。
以下查询将带有 quality列 作为 UNPIVOT 隐式列,halo数据库查询将生成以下输出
halo0root=# SELECT *halo0root-# FROM count_by_color UNPIVOT (halo0root(#     cnt FOR color IN (red, green, blue)halo0root(# );+---------+-------+-----+| quality | color | cnt |+---------+-------+-----+| high    | red   |  15 || high    | green |  20 || high    | blue  |   7 || normal  | red   |  35 || normal  | blue  |  40 || low     | red   |  10 || low     | green |  23 || low     | red   |  50 |+---------+-------+-----+(8 rows)
以上例子UNPIVOT 定义中未引用的输入表列被隐式添加到结果表中。在该示例中，quality 列就是这种情况。
以下示例显示了带有 UNPIVOT IN 列表中值别名的 ，halo数据库查询将生成以下输出
halo0root=# SELECT * FROM count_by_color UNPIVOT (cnt FOR color IN (red AS 'r', green AS 'g', blue AS 'b'));+---------+-------+-----+| quality | color | cnt |+---------+-------+-----+| high    | r     |  15 || high    | g     |  20 || high    | b     |   7 || normal  | r     |  35 || normal  | b     |  40 || low     | r     |  10 || low     | g     |  23 || low     | r     |  50 |+---------+-------+-----+(8 rows)
支持unpivot列转行主要有以下四个场景功能：
  1. 将表中多个列缩减为一个聚合列，例: 值聚合名 FOR 聚合列名 IN (表列名1 AS 常量别名1, 表列名2 AS 常量别名2...)。

  2. 将表中多个列集合缩减为一个聚合列,例：（值聚合名1,值聚合名2) FOR 聚合列名 IN ((表列名1, 表列名2)AS (常量别名1), (表列名3,表列名4)...)。

  3. 将表中多个列缩减为多个聚合列,例：值聚合名 FOR （聚合列名1,聚合列名2） IN (表列名1 AS (常量别名1,常量别名2）, 表列名2 ...)。

  4. 将表中多个列集合缩减为多个聚合列相对应,例：（值聚合名1，值聚合名2) FOR （聚合列名1, 聚合列名2) IN ((表列名1, 表列名2) AS (常量别名1,常量别名2）, (表列名3,表列名4)...)。

以下是 UNPIVOT 的使用说明:

KEEP THE THREAD GOING

更多来源文章