最近读了 O'Reilly 的技术译文书《SQL经典实例》,原书最后一章有一篇标题为《给经过两次转置的结果集添加列标题》的实例,该实例处理后的数据如下图所示,其结果有点类似电影结尾的职员表,或是以前校园里张贴的成绩排行榜。

SQL 经典实例

作者的想法很棒,同时也觉得作者的实现似乎有些过于复杂和抽象了,不太容易阅读和理解,在此以另一种方法实现,也算是对我自己学习结果的一次检验。(这里的标题仅仅是为匹配原书标题而取的,实际上我并不知道这个场景叫什么比较合适。)

创建数据表:

create table it_app (deptno int NULL,ename VARCHAR(30) NULL);
create table it_sch as select * from it_app where 1<>1;

写入示例数据:

INSERT INTO it_app VALUES (100, 'Clay');
INSERT INTO it_app VALUES (100, 'Mark');
INSERT INTO it_app VALUES (100, 'Jim');
INSERT INTO it_app VALUES (200, 'Lily');
INSERT INTO it_app VALUES (200, 'Lucy');
INSERT INTO it_app VALUES (200, 'Judah');
INSERT INTO it_app VALUES (300, 'Scott');
INSERT INTO it_app VALUES (300, 'Mary');
INSERT INTO it_app VALUES (100, 'Oracle');

INSERT INTO it_sch VALUES (500, 'Kate');
INSERT INTO it_sch VALUES (500, 'Steve');
INSERT INTO it_sch VALUES (500, 'Kettle');
INSERT INTO it_sch VALUES (400, 'Matt');
INSERT INTO it_sch VALUES (400, 'Lary');
INSERT INTO it_sch VALUES (400, 'Danny');

最终的 SQL 实现:

with temp_table as (
-- 整理数据,拼接上部门号, 序号取0(依据 row_number() 从1开始的事实)
select 'app' as mark, deptno, to_char(deptno) as ename, 0 as row_num from it_app
GROUP BY deptno
UNION ALL
select 'app' as mark, deptno,ename,row_number() over(PARTITION by deptno ORDER BY ename) row_num
from it_app
UNION ALL
-- 整理数据, 同上
select 'sch' as mark, deptno, to_char(deptno) as ename, 0 row_num from it_sch
GROUP BY deptno
UNION ALL
select 'sch' as mark, deptno, ename, row_number() over(PARTITION by deptno ORDER BY ename) row_num from it_sch
), tmp_data_src as (
-- 重新进行一次排序编号
select mark, deptno, ename, row_number() over (partition by mark order by deptno,row_num asc) as row_num from temp_table
)
-- 完成转置
select
	max(case mark when 'app' then ename end) as apps,
	max(case mark when 'sch' then ename end) as research
from tmp_data_src
GROUP BY row_num
ORDER BY row_num;

实际上我的这个方案也有它的缺点:较原书的实现方式本实现会各多读一次数据表。

对原书实现方案的改进

原书的实现似乎有 bug: 生成 row_number() 时只用了 id 一个字段,显然还需要加上 ename 字段,否则没法保证多截取的那行跟被替换为 deptno 的那行是同一个员工,下面是我在原作者的基础上做了调整后的实现,简化了层级结构,且实现了按姓名升序排列:

with temp_level as (
	select level as id from dual connect by level < 3 -- 每行需要计算的次数
), temp_table as (
	-- 将最后一行的雇员名称替换为部门号
	select c.mark,c.deptno,c.ename,c.ttl_row,c.row_order,decode(c.row_order, c.ttl_row, TO_CHAR(c.DEPTNO), c.ename) as ename2,
				 row_number() over (partition by c.mark ORDER BY c.mark, c.deptno asc) as last_rownum
	from (
		select a.mark, a.deptno, a.ename, a.ttl_row,b.id,row_number() over (partition by a.DEPTNO order by b.id, a.ename) as row_order 
		from (
			select 'app' as mark, deptno, ename, count(1) over (partition by DEPTNO) as ttl_row --这个字段用于计算行数使用需要再此基础上+1行
			from it_app
			union all
			select 'sch' as mark, deptno, ename, count(1) over (partition by DEPTNO) as ttl_row
			from it_sch
		) a,
		temp_level b
	) c
	where c.row_order > c.ttl_row - 1
)
select
	max(case d.mark when 'app' then ename2 end) as apps,
	max(case d.mark when 'sch' then ename2 end) as research
from temp_table d
GROUP BY d.last_rownum
ORDER BY d.last_rownum

实际工作中经常需要对金额类的数值做格式转换,比如有千分位四舍五入保留两位小数,下面介绍下几种常见数据库中这种转化的处理逻辑。

MySQL

SELECT FORMAT(123456789.1234567, 4);
-- 123,456,789.1235

FORMAT() 有三个参数,第一个是要转换的数值,第二个参数是(四舍五入)保留的小数位数,第三个参数用于指定区域设置(将决定转化结果千位分隔符和小数点的格式,缺省为'en US')。

PostgreSQL

select to_char(123456789.12345, 'FM999,999,999,999.9999');
-- 123,456,789.1235

select to_char(123456789.68, '999,999,999,999.9999');
-- 123,456,789.6800

to_char() 的第一个参数是要转换的数值。
to_char() 的第二个参数中的 FM 用于抑制前导的零或尾随的空白(注意对比上面的两个语句,第二个语句没有 FM,小数位部分以0作了填充补齐4位小数),否则结果可能是一个固定宽度的字符串;.9999表示的是小数部分的位数这里是(四舍五入)保留4位小数,改成.99就是保留两位小数;FM999,999,999,999定义的是千分位格式和最长支持的有效数值长度,将其中的,改成_,结果中的千分位将以_分隔。

Oracle

select to_char(123456789.12345, 'FM999,999,999,999.9999')  from dual;
-- 123,456,789.1235

Oracle 的语法同 PostgreSQL

SQL Server

select convert(VARCHAR, cast(123456789.12345 as money), 1);
-- 123,456,789.12

由于一些历史原因,SQL Server 的底层仍然是 Sybase 的那套逻辑,绝大部分的函数名和语法跟 Sybase 都是通用的,包括这里的千分位转换语法,也就是说把 Sybase 上正常运行的 SQL 语句放在 SQL Server 里也能运行。上述语句包含两个函数,内层的 cast(123456789.12345 as money) 用于将数值转换为 money 格式,外层的 conver() 函数是转化的关键:第一个参数指定转换的目标格式;第二个参数求值 cast(123456789.12345 as money) 的结果是要转换的来源值;第三个参数,用1来指定转换格式为逗号分隔的千分位,同时小数点保留两位小数。
如果你想保留4位小数或者1位小数,对不起不支持,只能自己想办法,下面是一个有千分位且保留4位小数的示例:

select substring(convert(varchar, cast(123456789.12345 as money), 1), 1,
                 charindex('.', convert(varchar, cast(123456789.12345 as money), 0)))
       +
       convert(varchar, round(convert(float, '0.' + substring(convert(varchar, 123456789.12345 * 1.0000), charindex('.', convert(varchar, 123456789.12345 * 1.0000)) + 1, 5)), 4));
-- * 1.0000 是为了兼容整数, 4 为要保留的小数位数

原理是截取利用 SQL Server 原生函数生成千分位,并截取千分位部分及小数点,再拼上小数部分。

DO $demo_do$ 
DECLARE
	demo_table VARCHAR ( 64 );
	val TEXT;
	sql TEXT;
	err TEXT;
	msg TEXT;
BEGIN
	-- 创建临时表 demo_dept
	demo_table = 'demo_dept';
	sql = 'drop table if EXISTS ' || demo_table || ';';
	sql = sql || 'CREATE temp table ' || demo_table || '(name VARCHAR(20) NULL);';
	raise notice'sql___1(%)', sql;
	EXECUTE ( sql );
	-- 往临时表 demo_dept 写一行数据
	val = 'Tom';
	sql = 'INSERT into ' || demo_table || '(name) VALUES (''' || val || ''')';
	raise notice'sql___2(%)', sql;
	EXECUTE ( sql );
	-- 异常捕获和处理(可选)
	EXCEPTION 
	WHEN OTHERS THEN
		GET stacked DIAGNOSTICS err = RETURNED_SQLSTATE,
		msg = PG_EXCEPTION_DETAIL;
	raise notice'err(%),msg(%)', err, msg;
	-- do something
END;
$demo_do$;

connectby 是 postgresql 的 tablefunc 扩展模块所提供一个函数,它的作用是生成递归形式的记录行。

connectby 的参数

connectby 有7个参数,简单用示例说明如下
select * from connectby ('数据表字', '子字段', '父字段', '排序字段(可选)', '开始行的字段值', '查询深度(0表示不限)', '分支间隔符(可选)')
as t('子字段 数据类型', '父字段 数据类型', level int/*层级,固定数据类型*/, branch text/* 分支描述,固定数据类型 */, pos int /* 排序序号, 固定数据类型*/)

使用示例

create table prod_cat(cat_id INTEGER, cat_name VARCHAR(40), parent_cat_id INTEGER);
INSERT into prod_cat(cat_id, cat_name, parent_cat_id)
VALUES
(1, 'A', null),
(2, 'AA', 1),
(3, 'AB', 1),
(4, 'AAA', 2),
(5, 'AAB', 4),
(6, 'B', NULL),
(7, 'BA', 6),
(8, 'BAA', 7),
(9, 'BAAA', 8)
select t.*,t1.cat_name,t2.cat_name as parent_cat_name
from connectby('prod_cat', 'cat_id', 'parent_cat_id', 'cat_name', '1', 0, '~')
AS t(cat_id INTEGER, parent_cat_id INTEGER, LEVEL1 INT, branch1 text, se1q integer)
left join prod_cat t1 on t1.cat_id=t.cat_id
left join prod_cat t2 on t2.cat_id=t.parent_cat_id

crosstab 是 postgresql 的 tablefunc 扩展模块所提供的函数之一,tablefunc 提供了多个返回多行数据的函数,crosstab 便是其一,crosstab 提供了一种将多行数据转换成一行数据的能力,可以简单地理解为行转列。postgresql 帮助文档上都有对 crosstab 函数的详细介绍,不过使用的例子比较抽象,再加上粗陋的翻译质量可能让人不是很好理解,在此总结一下 crosstab 这个函数,并提供一个手册上没有的实用技巧(见本篇后半部的《双参数的 crosstab 示例2》)。

安装 tablefunc 模块

默认情况下 tablefunc 模块是未安装的,需要手动执行下方命令安装,重复执行会提示该扩展已创建:

CREATE EXTENSION tablefunc;

创建示例表

创建示例表:

CREATE table prod_attr
(
    prod_name      VARCHAR(20), -- 产品名称
    attr_name      VARCHAR(40), -- 属性名称
    attr_val       NUMERIC(15, 2), -- 属性值
    attr_val_desc  VARCHAR(20) -- 属性单位
);

写入示例数据:

insert into prod_attr(prod_name, attr_name, attr_val, attr_val_desc)
VALUES
('农夫山泉','库存数量', 100, '瓶'),
('农夫山泉','规格', 500, '毫升'),
('农夫山泉','单价', 2.00, '元'),
('雪碧','规格', 200, '毫升'),
('雪碧','单价', 1.80, '元'),
('雪碧','折后价', 1.60, '元'),
('手工面包','库存数量', 10, '盒'),
('手工面包','规格', 300, '克'),
('手工面包','单价', 12.00, '元'),
('手工面包','保质期', 7, '天');

执行查询:

select * from prod_attr;

下图是表里的原始数据:

在进一步介绍上述语句前先介绍下示例表和示例表中的数据:
示例表有四个字段,前三个字段分别是产品名称(prod_name),属性名称(attr_name)和属性值(attr_val),第四个字段(attr_val_desc)在这里并未实际使用,仅出于方便理解的目的作为对第三个字段的解释。

通过插入的实际值能够看出这是一个记录产品常见属性的表,如规格、价格、库存数量、保质期,每一个这样的产品属性占据一行,即同一个产品有几个属性就有几行数据在表里。

这里我们使用 crosstab 的最终目的就是要把分散在每一行的这些产品属性置于同一行来显示,以便用户使用,可以简单地把这种行为理解为行转列。

crosstab 第一个参数的约束限制

上述语句中的 crosstab 只有一个字符串参数,这个字符串参数必须是一个合法的 sql 查询,它的作用是从数据表中找出来需要转换处理的目标数据,这个查询需要满足以下几个条件:

  1. select 字段:必须具有顺序固定的三个字段列,第一列是名称(示例是产品名称 prod_name),第二列是属性名称(示例是属性名称 atrr_name),第三列是属性值(示例是属性值 attr_val);
  2. order by 字句:order by 子句的作用是给 select 子句的前两个字段(prod_name、atrr_name)排序,顺序必须是第一个字段在前、第二个字段在后,order by 不能省略且字段顺序一定要处理好,否则执行结果会有问题;

定义 crosstab 的返回字段和数据类型

同时,由于 crosstab 函数定义的返回类型为 record 即多行记录,需要在 crosstab 的 from 子句中为 crosstab 定义返回的字段及数据类型,这里的字段顺序是固定的,第一个字段名及其数据类型同 select 子句的第一个字段(prod_name),后续字段可以根据实际需要(一般是需要显示几个属性就定义几个)自行定义,但是它们的数据类型必备与 select 子句的第三个字段(attr_val)相同。

至此,上述 crosstab 的使用就清楚了,如果你不想看上述繁琐的文字叙述,看一下接下来的图片即明白这个函数的作用了。

一个参数的 crosstab 示例

注意参数中的 sql 里加了 where 条件限制,移除 where 后执行查询你会发现这里有一个问题

select
prod_name,attr_a as "规格",attr_b as "库存数量",attr_c as "单价", attr_d as "保质期"
from crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
      where t1.prod_name in (''农夫山泉'', ''手工面包'') -- 条件过滤
   order by prod_name, attr_name desc'
) as cte(prod_name VARCHAR, attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC)

这是通过 crosstab 转换后的数据:

是不是很简单,这里的前提是建立在每个商品属性一致的(即有都有同样的属性,即便其值有所不同值)假设前提下,使用者需要做的是通过第一个参数 sql 中的 order by 保证每个产品名称(prod_name)的属性字段(atrr_name)的顺序一致。当商品的属性不一致时上述语句就会有问题,这也是接下来要描述的双参数的 crosstab 要解决的问题。

两个参数的 crosstab

双参数的 crosstab 与一个参数的 crosstab 作用是相同的,区别是对属性字段的处理上更加灵活。

双参数 crosstab 的第一个参数

双参数 crosstab 的第一个参数与单参数 crosstab 的第一个参数是一样的,也是一个 sql 查询,特别之处是

  1. 在第一列(商品名称(prod_name))后方可以出现可选的一个或多个额外字段列(extra columns);与单参数一样,属性名称(示例是属性名称 atrr_name)和属性值(示例是属性值 attr_val)这两个字段始终要保证在倒数第二和倒数第一的位置上;
  2. order by 仍然要做排序,区别是只需要对第一列商品名称(prod_name)进行排序了。

双参数 crosstab 的第二个参数

它的第二个参数也是一个 sql 语句它的作用是指定需要显示的属性名称的列表,即在这里指定那些你希望在最后的结果出现的属性列,且要满足如下条件:
第二个参数的查询结果只能有一个字段,且不能存在重复行,否则会报错,即你要在第二个参数的 select 子句里预防性地加一个 distinct;
注意顺序,与返回字段配合好,关于顺利这里还有另一个技巧可以让你显示地指定这些字段和它们出现的顺序,下面会介绍。

定义 crosstab 的返回字段和数据类型

要求同单参数,不再赘述。

双参数的 crosstab 示例1

还是以上述的示例表中的数据为例,加入第二个参数,通过第二个参数里的 sql 查询指定属性名称(atrr_name)及其排序顺序。

select
prod_name,
attr_a as "规格",
attr_b as "单价",attr_c as "折后价", attr_d as "保质期", attr_e as "库存数量"
from 
crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
   order by prod_name', -- 要排序,仅需对第一个字段排序
	 'select DISTINCT attr_name from prod_attr' -- 保证只有一列且不能存在重复行
) as cte(prod_name VARCHAR,
				 attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC, attr_e NUMERIC)

执行结果如下图:

双参数的 crosstab 示例2(强烈推荐)

通过第二个参数显示指定属性名称字段及其顺序。

select
prod_name, attr_a as "保质期", attr_b as "规格",attr_c as "单价",attr_d as "折后价"
from 
crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
   order by prod_name', -- 排序仍然要, 仅需对第一个字段排序
	 'VALUES (''保质期''), (''规格''), (''单价''), (''折后价'')' -- 显示指定attr_name值和排序
) as cte(prod_name VARCHAR, attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC)

执行结果如下图:

参考:http://postgres.cn/docs/12/tablefunc.html