核心内容摘要
TIA Portal V17与西门子PLC编程实战：1200/1500系列快速入门教程
背景SQL用于统计数据分析过程中行列转换是一项常见的操作。
按转化方向划分行列转换可分为行转列、列转行按转化内容划分可分为行列转字符串、字符串转行列等。
受SQL语句语法限制传统的数据库数据类型在处理行列转换上不很方便要么语句冗长要么要借助存储过程来实现有的数据库管理系统则用特定的函数来实现。
并与传统的方法比较。
为了解决这一问题下面利用一些数据库管理系统引入的数组数据类型来比较方便高效地完成部分转换工作。
数据准备我们使用一张学生表T作为我们的基础表。
用它来演示各种类型的行列转换操作。
假定表中主键是学号学期科目。
数据库管理系统采用duckdb它的语法与PostgreSQL兼容 。
多种国产数据库产品也与之兼容如金仓数据库。
表
成绩登记表┌───────┬───────┬───────┬───────┐ │ a │ b │ c │ d │ │ 学号 │ 学期 │ 科目 │ 分数 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴───────┘简单起见用a,b,c,d分别表示学号、学期、科目、分数建表语句如下create table t(a smallint,b smallint,c smallint,d smallint, primary key(a,b,c));插入模拟数据语句a,b,c中的每个都取
2两个值insert into t with a(n) as(select 1 union select
select a.n,b.n,c.n,a.nb.nc.n from a,a b,a c;为了表示某些数据缺失情况将表格中特定的行删除。
语句如下delete from t where (a,b,c)in((2,2,
);
行转列就是把多个行某一列的值用多个列来展示。
假定我们有3种表需要显示。
表
按学号和学期显示不同科目的分数学号 学期 科目1分数 科目2分数表
按学号和科目显示不同学期的分数学号 科目 学期1分数 学期2分数表
按学号显示不同科目和学期的分数学号 科目1学期1分数 科目1学期2分数 科目2学期1分数 科目2学期2分数一 使用case when行转列表1的查询语句select a,b,max(case when c1 then d end)c1, max(case when c2 then d end)c2 from t group by a,b; ┌───────┬───────┬───────┬───────┐ │ a │ b │ c1 │ c2 │ │ 学号 │ 学期 │ 科目1 │ 科目2 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 3 │ 4 │ │ 1 │ 2 │ 4 │ 5 │ │ 2 │ 1 │ 4 │ 5 │ │ 2 │ 2 │ NULL │ 6 │ └───────┴───────┴───────┴───────┘上述语句的含义是将每种a,b组合中符合条件c1和c2的d值分别用max求出每列都需要一个case when语句如果显示某个列的条件包含多个列则多个列的条件都需要在when中写出。
表2的查询语句select a,c,max(case when b1 then d end)b1, max(case when b2 then d end)b2 from t group by a,c; ┌───────┬───────┬───────┬───────┐ │ a │ c │ b1 │ b2 │ │ 学号 │ 科目 │ 学期1 │ 学期2 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 3 │ 4 │ │ 1 │ 2 │ 4 │ 5 │ │ 2 │ 1 │ 4 │ NULL │ │ 2 │ 2 │ 5 │ 6 │ └───────┴───────┴───────┴───────┘表3的查询语句select a,max(case when b1 and c1 then d end)b1c1, max(case when b1 and c2 then d end)b1c2, max(case when b2 and c1 then d end)b2c1, max(case when b2 and c2 then d end)b2c2 from t group by a; ┌───────┬───────┬───────┬───────┬───────┐ │ a │ b1c1 │ b1c2 │ b2c1 │ b2c2 │ │ 学号 │ int16 │ int16 │ int16 │ int16 │ ├───────┼───────┼───────┼───────┼───────┤ │ 1 │ 3 │ 4 │ 4 │ 5 │ │ 2 │ 4 │ 5 │ NULL │ 6 │ └───────┴───────┴───────┴───────┴───────┘二 PostgreSQL使用crosstab行转列表1的查询语句select a/10 a,a%10 b,c1,c2 from crosstab(select a*10b,c,d from t order by a,b,c,$$VALUES(
,(
$$) as d(a int ,c1 int,c2 int);其中crosstab函数是一个扩展函数需要用命令create extension tablefunc;启用它包含2个参数参数1是一个查询必须是3列分别是行分组列、列分组列、取值列。
参数2是一个查询或值的列表表示列分组列的取值和次序。
这里的$$VALUES(
,(
$$可以等价替换成select distinct c from t order by c。
as子句表示将crosstab的返回结果设置一个表别名列别名和类型列表最前面的select 子句的列名均引用自此表别名。
由于crosstab只支持一列作为行分组列所以此处使用了一个合并后拆分的手段如果取值不是简单的1位整数则需要改为其他写法。
表2的查询语句select a/10 a,a%10 c,b1,b2 from crosstab(select a*10c,b,d from t order by a,c,b,$$VALUES(
,(
$$) as d(a int ,b1 int,b2 int);表3的查询语句select a,b1c1,b1c2,b2c1,b2c2 from crosstab(select a,b*10c,d from t order by a,c,b,$$VALUES(
,(
,(
,(
$$) as d(a int , b1c1 int,b1c2 int,b2c1 int,b2c2 int);三 使用数组函数array_agg行转列表1的查询语句with a(n) as(select 1 union select
,t1 as(select a.n a,b.n b,c.n c from a,a b,a c) select a,b,array_agg(coalesce(d,
order by c)d from t1 left join t using(a,b,c) group by a,b order by 1, 2; ┌───────┬───────┬─────────┐ │ a │ b │ d │ │ int32 │ int32 │ int16[] │ ├───────┼───────┼─────────┤ │ 1 │ 1 │ [3, 4] │ │ 1 │ 2 │ [4, 5] │ │ 2 │ 1 │ [4, 5] │ │ 2 │ 2 │ [0, 6] │ └───────┴───────┴─────────┘这个语句的with部分的t1表示a,b,c三列的全组合也就是笛卡尔积。
coalesce函数表示在d的值为NULL时将其替换成0若无需替换则可省略coalesce函数输出变为┌───────┬───────┬───────────┐ │ a │ b │ d │ │ int32 │ int32 │ int16[] │ ├───────┼───────┼───────────┤ │ 1 │ 1 │ [3, 4] │ │ 1 │ 2 │ [4, 5] │ │ 2 │ 1 │ [4, 5] │ │ 2 │ 2 │ [NULL, 6] │ └───────┴───────┴───────────┘left join表示将上述t1与基础表左连接之所以这样做是为了当某个学生缺少某学期某科目成绩时能够显示0或NULL而不至于错位。
如果不存在这种情况每个学生都有全部的成绩简单地select a,b,array_agg(d order by c)d from t group by a,b order by 1, 2即可。
表示行方向按a,b分组列方向按c顺序列举。
如果要得表2的结果with a(n) as(select 1 union select
,t1 as(select a.n a,b.n b,c.n c from a,a b,a c) select a,c,array_agg(d order by a,c)d from t1 left join t using(a,b,c)group by a,c order by 1,2; ┌───────┬───────┬───────────┐ │ a │ c │ d │ │ int32 │ int32 │ int16[] │ ├───────┼───────┼───────────┤ │ 1 │ 1 │ [3, 4] │ │ 1 │ 2 │ [4, 5] │ │ 2 │ 1 │ [4, NULL] │ │ 2 │ 2 │ [5, 6] │ └───────┴───────┴───────────┘若学生都有全部的成绩只需要简单地改变分组和排序列select a,c,array_agg(d order by b)d from t group by a,c order by 1, 2即可。
如果要得表3的结果with a(n) as(select 1 union select
,t1 as(select a.n a,b.n b,c.n c from a,a b,a c) select a,array_agg(d order by b,c)d from t1 left join t using(a,b,c)group by a order by 1; ┌───────┬─────────────────┐ │ a │ d │ │ int32 │ int16[] │ ├───────┼─────────────────┤ │ 1 │ [3, 4, 4, 5] │ │ 2 │ [4, 5, NULL, 6] │ └───────┴─────────────────┘若学生都有全部的成绩只需要将b从分组列改为排序列select a,array_agg(d order by b,c)d from t group by a order by 1即可。
要将数组数据类型中的元素取出采用数组[序号]的语法序号从1开始编号。
比如要把上述表3的数组类型结果转化为多列结果这样编写with a as(select a,array_agg(d order by b,c)d from t group by a)select a,d[1]b1c1,d[2]b1c2,d[3]b2c1,d[4]b2c2 from a; ┌───────┬───────┬───────┬───────┬───────┐ │ a │ b1c1 │ b1c2 │ b2c1 │ b2c2 │ │ int16 │ int16 │ int16 │ int16 │ int16 │ ├───────┼───────┼───────┼───────┼───────┤ │ 1 │ 3 │ 4 │ 4 │ 5 │ │ 2 │ 4 │ 5 │ 6 │ NULL │ └───────┴───────┴───────┴───────┴───────┘因为未与全组合的表关联上述结果的NULL就发生了错位。
错把a2b2c2的结果填在了a2b2c1的位置。
四 DuckDB使用pivot行转列duckdb行列转换有更简洁的pivot和unpivot语法如下所示D pivot t on c using sum(d); ┌───────┬───────┬────────┬────────┐ │ a │ b │ 1 │ 2 │ │ int16 │ int16 │ int128 │ int128 │ ├───────┼───────┼────────┼────────┤ │ 1 │ 1 │ 3 │ 4 │ │ 1 │ 2 │ 4 │ 5 │ │ 2 │ 1 │ 4 │ 5 │ │ 2 │ 2 │ NULL │ 6 │ └───────┴───────┴────────┴────────┘ D pivot t on b using sum(d); ┌───────┬───────┬────────┬────────┐ │ a │ c │ 1 │ 2 │ │ int16 │ int16 │ int128 │ int128 │ ├───────┼───────┼────────┼────────┤ │ 1 │ 1 │ 3 │ 4 │ │ 1 │ 2 │ 4 │ 5 │ │ 2 │ 1 │ 4 │ NULL │ │ 2 │ 2 │ 5 │ 6 │ └───────┴───────┴────────┴────────┘ D pivot t on b,c using sum(d); ┌───────┬────────┬────────┬────────┬────────┐ │ a │ 1_1 │ 1_2 │ 2_1 │ 2_2 │ │ int16 │ int128 │ int128 │ int128 │ int128 │ ├───────┼────────┼────────┼────────┼────────┤ │ 1 │ 3 │ 4 │ 4 │ 5 │ │ 2 │ 4 │ 5 │ NULL │ 6 │ └───────┴────────┴────────┴────────┴────────┘详细用法参见文档
列转行列转行是行转列的逆操作就是把多个列的值用多行一列来展示对我们的例子从表
1、
3得到表0的结果都属于列转行。
为减少篇幅仅用较复杂的多列表3来得到表1举例其他可以仿照表3的处理来完成。
首先将表3查询结果用create table as保存为数据库表T3。
create table t3 as select a,max(case when b1 and c1 then d end)b1c1, max(case when b1 and c2 then d end)b1c2, max(case when b2 and c1 then d end)b2c1, max(case when b2 and c2 then d end)b2c2 from t group by a;最容易想到的方法是将不同列的查询结果逐个取出再用union all合并但此法有多少列就需要读多少次原表因此对大表而言不可行。
一 使用case when列转行使用case when列转行的原理是通过与一个行数与需转换列数相同每行都有一个行号的中间表做笛卡尔积将每个列映射到某个行号再将行号与原始表关联将对应的分组列输出with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c) ┌───────┬───────┬───────┐ │ rn │ b │ c │ │ int64 │ int32 │ int32 │ ├───────┼───────┼───────┤ │ 1 │ 1 │ 1 │ │ 2 │ 1 │ 2 │ │ 3 │ 2 │ 1 │ │ 4 │ 2 │ 2 │ └───────┴───────┴───────┘ with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c) select t
a,t
b,t
c,case rn when 1 then b1c1 when 2 then b1c2 when 3 then b2c1 when 4 then b2c2 end d from t1, t3; ┌───────┬───────┬───────┬───────┐ │ a │ b │ c │ d │ │ int16 │ int32 │ int32 │ int16 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 1 │ NULL │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴───────┘二 使用JOIN LATERAL列转行crosstab只能做行转列操作列转行可利用PostgreSQL的另一功能JOIN LATERAL完成它本质上与case when列转行的思路一致不过形式更为简明。
SELECT t.a, b,c, d FROM t3 t CROSS JOIN LATERAL (VALUES(1,1, t.b1c
,(1,2, t.b1c
,(2,1,t.b2c
,(2,2,t.b2c
) s(b,c, d); ┌───────┬───────┬───────┬───────┐ │ a │ b │ c │ d │ │ int16 │ int32 │ int32 │ int16 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 1 │ NULL │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴───────┘三 使用数组列转行对于常规数据类型的结果表列转行数组没有什么用。
create table t3a as with a(n) as(select 1 union select
,t1 as(select a.n a,b.n b,c.n c from a,a b,a c)select a,array_agg(d order by b,c)d from t1 left join t using(a,b,c)group by a order by 1; select * from t3a; ┌───────┬─────────────────┐ │ a │ d │ │ int32 │ int16[] │ ├───────┼─────────────────┤ │ 1 │ [3, 4, 4, 5] │ │ 2 │ [4, 5, NULL, 6] │ └───────┴─────────────────┘要将数组数据类型的数据列转行比如要将上述按a行转数组列的结果表t3a恢复为基础表可以将它与b,c两列的全组合连接并以b,c两列组合的序号为下标取出数组元素得到。
此处关键是要按照同样的顺序去关联结果表和全组合表。
with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c)select a,b,c,d[rn]d from t1,t3a; ┌───────┬───────┬───────┬───────┐ │ a │ b │ c │ d │ │ int32 │ int32 │ int32 │ int16 │ ├───────┼───────┼───────┼───────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 1 │ NULL │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴───────┘数组转行还可以使用unnest函数实现上述语句的等价写法如下with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c) select t3a.a,t
b,t
c,u.d from t3a,unnest(d) with ordinality as u(d,i),t1 where t
rnu.i order by 1,2,3;这种写法稍微费解一点在from语句中unnest(d)引用的是t3a表的d列with ordinality表示拆分数组的同时产生序号拆出的元素存在表别名u的d列序号存在i列利用这个序号与t1表作关联。
在这个场合不如用数组下标的方法简明。
行转字符串仍以表3为例如果要把分数列用一个字符串表示这通过专门函数string_agg实现用法与array_agg基本一致只是增加了一个分隔字符的参数select a,string_agg(d::varchar,, order by b,c)d from t group by a; ┌───────┬─────────┐ │ a │ d │ │ int16 │ varchar │ ├───────┼─────────┤ │ 1 │ 3,4,4,5 │ │ 2 │ 4,5,6 │ └───────┴─────────┘注意 string_agg函数忽略NULL, 如果要处理NULL需要像前面一样用全组合表左连接再用coalesce函数转换。
with a(n) as(select 1 union select
,t1 as(select a.n a,b.n b,c.n c from a,a b,a c) select a,string_agg(coalesce(d::varchar,NULL),, order by b,c)d from t1 left join t using (a,b,c) group by a order by 1; ┌───────┬────────────┐ │ a │ d │ │ int32 │ varchar │ ├───────┼────────────┤ │ 1 │ 3,4,4,5 │ │ 2 │ 4,5,NULL,6 │ └───────┴────────────┘
字符串转行split_part函数用于将指定分隔符的字符串的某个部分取出它的第一个参数是字符串第二个是分隔符第三个是序号用int类型。
将上述string_agg转换的结果存为临时表ts转换为表0的操作如下with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c) select a,b,c,split_part(d,,,rn::int)d::int from t1 ,ts order by 1,2,3; ┌───────┬───────┬───────┬─────────┐ │ a │ b │ c │ d │ │ int32 │ int32 │ int32 │ varchar │ ├───────┼───────┼───────┼─────────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 1 │ NULL │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴─────────┘这个结果比原始表多了1行d为NULL的行可以用where d NULL’过滤掉。
数据类型也不对可以过滤之后再转换为整数。
regexp_split_to_table 和 regexp_split_to_array 都是字符串分割函数可通过指定的表达式进行分割。
regexp_split_to_table 将分割出的数据转成行这个函数不能输出序号因此也难以与带序号的其它表关联。
regexp_split_to_array 是将分隔的数据转成数组,它可以用下标方式取出所需的元素值。
上述split_part查询的regexp_split_to_array等效语句如下with a(n) as(select 1 union select
,t1 as(select row_number()over(order by b,c)rn,b.n b,c.n c from a b,a c),t2 as(select a,regexp_split_to_array(d,,)d from ts) select a,b,c,d[rn]d from t1 ,t2 order by 1,2,3; ┌───────┬───────┬───────┬─────────┐ │ a │ b │ c │ d │ │ int32 │ int32 │ int32 │ varchar │ ├───────┼───────┼───────┼─────────┤ │ 1 │ 1 │ 1 │ 3 │ │ 1 │ 1 │ 2 │ 4 │ │ 1 │ 2 │ 1 │ 4 │ │ 1 │ 2 │ 2 │ 5 │ │ 2 │ 1 │ 1 │ 4 │ │ 2 │ 1 │ 2 │ 5 │ │ 2 │ 2 │ 1 │ NULL │ │ 2 │ 2 │ 2 │ 6 │ └───────┴───────┴───────┴─────────┘string_to_array函数的功能与regexp_split_to_array相同。
结论case when通用性好适合于几乎所有关系型数据库。
缺点是当输出列较多时写法过于冗长不易编写编写工作效率较低。
数组函数array_agg和crosstab扩展函数相比具有如下优点可读性好不需要标出数据类型。
可扩展性好可以推广到多个分组不需要特殊处理。
可维护性好改变分组只需要改变分组列和排序列。
通用性较好其他数据库管理系统如duckdb可兼容这种写法。
使用数组的缺点是它不是标准数据类型有些数据库不支持。
用下标访问数组元素时不同数据库管理系统的起始值可能不同如较早版本的duckdb的数组下标从0开始计数PostgreSQL从1开始计数需要注意。