SQL 转置计算

基础转置

1. 行转列：将销量分组表的quater 字段里的值（行）Q1-Q4 ，转为新字段名（列）Q1-Q4 ，如下：

 year quarter amount ----> year Q1 Q2 Q3 Q4 year2018 Q1 89 year2018 89 93 88 99 year2018 Q2 93 year2019 92 97 90 88 year2018 Q3 88 year2018 Q4 99 year2019 Q1 92 year2019 Q2 97 ， year2019 Q3 90 year2019 Q4 88

2. 列转行：将销量交叉表的字段名Q1-Q4 ，转为新字段quarter 里的值Q1-Q4 ，如下：

 Year Q1 Q2 Q3 Q4 ----> year quarter amount year2018 89 93 88 99 year2018 Q1 89 year2019 92 97 90 88 year2018 Q2 93 year2018 Q3 88 year2018 Q4 99 year2019 Q1 92 year2019 Q2 97 year2019 Q3 90 year2019 Q4 88

早期SQL的解决方案

 /*mysql*/ Select year, max(Q1) 'Q1',  max(Q2)   'Q2',  max (Q3) 'Q3', max (Q4) 'Q4'  from   (                 select year,                 case when   quarter = 'Q1' then amount end Q1,                 case when   quarter = 'Q2' then amount end Q2,                 case when   quarter = 'Q3' then amount end Q3,                 case when   quarter = 'Q4' then amount end Q4                 from zz11 ) t  group by year;

 /*mysql*/ SELECT year,     MAX(IF(quarter = 'Q1',   amount, null)) AS 'Q1',     MAX (IF(quarter = 'Q2',   amount, null)) AS 'Q2',     MAX (IF(quarter = 'Q3',   amount, null)) AS 'Q3',     MAX (IF(quarter = 'Q4', amount, null)) AS   'Q4'  FROM zz11 GROUP BY year;

SQL之所以规定分组的同时必须汇总，是因为集合化不彻底的缘故。具体来说，SQL只能表达多条记录组成的小集合，而没有语法或符号表达多个小集合组成的大集合，一旦遇到后者，比如分组的情况，就必须立刻汇总，让每个小集合变成一条记录，从而转变成前者。

 select year, 'Q1' quarter , Q1 as amount from zz111 union select year, 'Q2' quarter , Q2 as amount from zz111 union select year, 'Q3' quarter , Q3 as amount from zz111 union select year, 'Q4' quarter , Q4 as amount from zz111

列转行虽然思路简单，但因为要枚举组内新行，比如季度、月份、省份，所以代码依然会很长。值得庆幸的是，组内新行来自原表列名（字段名），而原表列名通常固定，所以一般不存在动态计算的情况，算法也不会太复杂。

引入pivot/unpivot函数

早期SQL实现转置确实不够方便，所以数据库厂商近几年推出专用函数，试图让用户更方便地实现转置。

 /*oracle*/ select * from zz111               pivot(                         max(amount)   for quarter in(                                        'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4                         )               )

 customer year quarter amount year Q1 Q2 Q3 Q4 companyA year2018 Q1 89 ----> year2018 89 93 88 100 companyB year2018 Q1 100 year2019 92 97 90 88 companyA year2018 Q2 93 companyB year2018 Q3 88 companyC year2018 Q4 99 companyD year2019 Q1 92 companyE year2019 Q2 97 companyF year2019 Q3 90 companyG year2019 Q4 88

 /*oracle*/ select * from (select year,quarter,amount from zz111)               pivot(                          max(amount)   for quarter in(                                     'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4                            )               )

 select * from zz111                unpivot(                        amount for   quarter in(                                 Q1,Q2,Q3,Q4                        )                 )

可以看到，unpivot不仅可以解决代码冗长的问题，而且由于不涉及汇总，所以理解起来也很容易。另外列转行很少遇到动态取列名的需求，因此基础算法不会发生太复杂的变化。可以这样说，unpivot是个相对成功的函数。

双向转置

3 ．将年度- 季度销售表转置为季度- 年度销售表，即将year 的值转为新列名year2018 、year2019 ，同时将列名Q1-Q4 转为新列quarter 的值。

 Year Q1 Q2 Q3 Q4 ----> quarter year2018 year2019 year2018 89 93 88 99 Q1 89 92 year2019 92 97 90 88 Q2 93 97 Q3 88 90 Q4 99 88

双向转置的实现思路就写在名字里，即先对Q1-Q4执行列转行，再对year2018、year2019执行行转列。如果用小型数据库实现，代码会是下面这样：

 /*mysql*/ select quarter,     max(IF(year = 'year2018',   amount, null)) AS 'year2018',     max(IF(year = 'year2019',   amount, null)) AS 'year2019' from (         select year, 'Q1'   quarter , Q1 as amount from crosstb         union         select year, 'Q2'   quarter , Q2 as amount from crosstb         union         select year, 'Q3'   quarter , Q3 as amount from crosstb         union         select year, 'Q4'   quarter , Q4 as amount from crosstb ) t group by quarter

 /*Oracle*/ select * from(         select * from crosstb unpivot(                 amount for   quarter in(                           Q1,Q2,Q3,Q4                 )         ) ) pivot(         max(amount) for year   in(                  'year2018' as   year2018,'year2019' as year2019         ) ) order by quarter

Pivot/unpivot其实还有个共同的问题，也请初学者注意：这类函数并非ANSI规范，所以各厂商语法区别较大，迁移时比较困难。

动态转置

4. 动态行转列：部门- 地区平均工资表中的地区会随着业务拓展而增加，请将地区字段的值（行）转为新字段名（列）。

 Dept Area AvgSalary ----> Dept Beijing Shanghai ... Sales Beijing 3100 Sales 3100 2700 Marketing Beijing 3300 Marketing 3300 2400 HR Beijing 3200 HR 3200 2900 Sales Shanghai 2700 Marketing Shanghai 2400 HR Shanghai 2900 …

 /*Oracle 11*/ select * from temp pivot (     max(AvgSalary) for Area in(           select distinct Area   from temp     ) )

 /*Oracle 11*/ select * from temp pivot xml(     max(AvgSalary) for Area in(           select distinct Area   from temp     ) )

 Dept Area_XML HR Beijing3200Shanghai3200 Marketing Beijing3300Shanghai2400 Sales Beijing3100Shanghai2700

5. 组内记录行转列：收入来源表中，逻辑上Name 是分组字段， Source 和Income 是组内字段，每个Name 对应多条组内记录，数量不固定，现在要将组内记录由行转列。

 Name Source Income ----> Category Source1 Income1 Source2 Income2 David Salary 8000 David Salary 8000 Bonus 15000 David Bonus 15000 Daniel Salary 9000 Daniel Salary 9000 Andrew Shares 26000 Sales 23000 Andrew Shares 26000 Robert Bonus 13000 Andrew Sales 23000 Robert Bonus 13000

1.       计算出结果表应该有几组组内字段（colN），即对源表按Name分组，求各组记录数，进而求最大的记录数。上表中David和Andrew的记录数最多，有2条，所以colN=2。通过colN，很容易计算出动态列的列名colNames。

2.       动态生成建结果表的SQL字符串（cStr）。难点在于循环colN次，每次都要生成一组组内字段，所有字段包括1个固定列+2*colN个动态列（如图）。

3.       动态执行上述字符串，生成临时表。代码形如：execute immediate cStr;

4.       计算结果表应该插入的关键字列表（rowKeys），即对源表按Name去重。上表中rowKeys=["David","Daniel","Andrew","Robert"]

5.       循环rowKeys，每次动态生成向结果表插入记录的SQL字符串iStr，并动态执行。生成iStr时，先根据当前Name查询源表，以获得对应的记录列表，这里要动态生成SQL并动态执行。接下来循环该记录列表，拼凑出iStr并执行，从而完成一次循环。

6.       查询结果表，返回数据。

6. 复杂静态行列转置: 每人每天在考勤表有7 条固定记录，需要将其转置为2 条，其中第1 条的In 、Out 、Break 、Return 字段值分别对应原表的第1 、7 、2 、3 条的Time 字段值，第2 条对应原表的1 、7 、5 、6 的Time 字段值。

 Per_Code in_out Date Time Type 1110263 1 2013-10-11 09:17:14 In 1110263 6 2013-10-11 11:37:00 Break 1110263 5 2013-10-11 11:38:21 Return 1110263 0 2013-10-11 11:43:21 NULL 1110263 6 2013-10-11 13:21:30 Break 1110263 5 2013-10-11 14:25:58 Return 1110263 2 2013-10-11 18:28:55 Out

 Per_Code Date In Out Break Return 1110263 2013-10-11 09:17:14 18:28:55 11:37:00 11:38:21 1110263 2013-10-11 09:17:14 18:28:55 13:21:30 14:25:58

 With r as(   select Per_code,Date,Time,row_number()  over(partition by Per_Code,Date order by Time) rn from temp) select Per_code,Date, max(case when rn=1   then Time end) In, max(case when rn=7   then Time end) Out, max(case when rn=2   then Time end) Break, max(case when rn=3   then Time end) Return from r group by   Per_code,Date union select Per_code,Date, max(case when rn=1   then Time end) In, max(case when rn=7   then Time end) Out, max(case when rn=5   then Time end) Break, max(case when rn=6   then Time end) Return from r group by   Per_code,Date

SQL集合无序，所以不能用序号引用记录，而本算法又需要序号，所以我们不得不人为制造一个序号，即上述代码中的with子句。有了序号之后，取数据就方便多了。至于明明没有汇总算法，却硬要max，这是SQL集合化不彻底的缘故，前面已经解释过这种现象。

7 复杂动态行列转置：用户表和记录表通过用户ID 关联，表示用户在2018 年某日存在一条活动记录。现在需要计算出2018 年的每周，各用户是否存在活动记录，用户名需转置为列。

 User Record ID(pk) 1:N----> ID(pk) Name Date(pk)

转置后目标表的数据

 Week User1 User2 User3 1 Yes No Yes 2 Yes Yes No 3 Yes No Yes 4 No Yes Yes

转置同时列间计算

8 表Temp 存储2014 年每个客户每个月的应付款情况，现在要将其转置，客户名为主键（关键字）列，1-12 月为转置列，对应的值为当月应付款金额，如果当月无数据，则用上月的应付款金额。

 ID Name amount_payable due_date 112101 CA 12800 2014-02-21 112102 CA 3500 2014-06-15 112104 LA 25000 2014-01-12 112105 LA 20000 2014-11-15 112106 LA 8000 2014-12-06

 name 1 2 3 4 5 6 7 8 9 10 11 12 CA 12800 12800 12800 12800 3500 3500 3500 3500 3500 3500 3500 LA 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 20000 8000

 With t2 as(select   name,amount_payable,EXTRACT(MONTH from dule_date) month from temp ) ,t1 as(SELECT rownum   month FROM dual CONNECT BY LEVEL <= 12 ) ,t3 as(select   t2.name,t2.amount_payable,t1.month from t1 left join t2 on   t1.month>=t2.month )  select * from t3  pivot(min(amount_payable) for month in(1 as   "1",2 as "2",3 as "3",4 as "4",5 as   "5",6 as "6",7 as "7",8 as "8",9 as   "9",10 as "10",11 as "11",12 as   "12"))

表间关联列转行

9 子表动态插入主表：订单表和订单明细是主子关系，一条订单对应至少一条明细，现在要将明细动态插入订单，如下所示：

 Order ----> OrderDetail ID(pk) OrderID(PK) Customer Number(pk) Date Product Amount

 ID Customer Date Product1 Amount1 Product2 Amount2 Product3 Amount3 1 3 2019-01-01 Apple 5 Milk 3 Salt 1 2 5 2019-01-02 Beef 2 Pork 4 3 2 2019-01-02 Pizza 3

10 多表关联列转行： 考试成绩表Exam 和补考成绩Retest 表都是Students 的子表，现在需要将两个子表转置到主表的列，且增加一个总分，注意考试的科目不定，且并非每个人都会补考，但考试的科目一定包含了补考科目。

 Exam table <----1:N Students table 1:N ----> Retest table stu_id subject score stu_id stu_name class_id stu_id subject score 1 Chinese 80 1 Ashley 301 2 Chinese 78 1 Math 77 2 Rachel 301 3 Math 82 2 Chinese 58 3 Emily 301 2 Math 67 3 Chinese 85 3 Math 56

 stu_id stu_name Chinese_score Math_score total_score Chinese_retest Math_retest 1 Ashley 80 77 156 2 Rachel 58 67 125 78 3 Emily 85 56 141 82

分栏

11 源表记录各大洲的部分城市人口，现在要分别找出欧洲和非洲的城市和人口，分两栏横向转置，注意目标列是固定的，但源表行数是动态的。如下所示：

 Continent City Population ----> EuropeCity EuropePopulation AfricaCity EuropePopulation Africa Cairo 6789479 Moscow 8389200 Cairo 6789479 Africa Kinshasa 5064000 London 7285000 Kinshasa 5064000 Africa Alexandria 3328196 Alexandria 3328196 Europe Moscow 8389200 Europe London 7285000

 With t1 as(select city   Europecity,population Europepopulation,rownum rn from temp where   continent='Europe') ,t2 as(select city   Africacity,population Africapopulation,rownum rn from temp where   continent='Africa') select   t1.Europecity,t1.Europepopulation,t2.Africacity,t2.Africapopulation from t1   full join t2 on t1.rn=t2.rn

总结

SQL的上述缺陷是个独特的历史现象，在其它计算机语言中并不存在，比如VB\C++\JAVA，甚至包括存储过程。当然，这些语言的集合计算能力比较弱，缺乏结构化计算类库，需要编写大量代码才能实现上述算法（指不嵌入SQL的情况）。

1行转列，有类似的pivot函数

 A 1 =connect("orcl").query@x("select * from T") 2 =A1.pivot(year; quarter, amount)

2列转行，有相当于unpivot的函数

 A 1 =connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T") 2 =A1.pivot@r(year; quarter, amount)

3双向转置，结合使用pivot及其逆

 A 1 =connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T") 2 =A1.pivot@r(year;quarter,amount).pivot(quarter;year,amount)

4动态行转列，SPL的pivot可以支持动态数据结构

 A 1 =connect("orcl").query@x("select Dept,Area,AvgSalary   from T") 2 =A1.pivot@r(year;quarter,amount).pivot(Dept; Area, AvgSalary)

5组内记录行转列，分步计算并支持动态数据结构

 A B 1 =orcl.query("select   Name,Source,Income from T") 2 =gData=A1.group(Name) 3 =colN=gData.max(~.len()) 4 =create(Name, \${colN.("Source"+string(~)+",   Income"+string(~)).concat@c()}) 5 for gData =A5. Name | A5.conj([Source,   Income]) 6 >A4.record(B5)

6复杂静态行列转置，天然支持序号

 A B 1 =connect("orcl").query@x("select * from DailyTime   order by Per_Code,Date,Time") =A1.group((#-1)\7) 2 =create(Per_Code,Date,In,Out,Break,Return) =B1.(~([1,7,2,3,1,7,5,6])) 3 =B2.conj([~.Per_Code,~.Date]|~.(Time).m([1,2,3,4])|[~.Per_Code,~.Date]|~.(Time).m([5,6,7,8])) >A2.record(A3)

7复杂动态行列转置

 A B 1 =connect("db").query("select   t1.ID as ID, t1.Name as Name, t2.Date as Date from User t1, Record t2 where   t1.ID=t2.ID") 2 =A1.derive(interval@w("2018-01-01",Date)+1:Week) =A2.max(Week) 3 =A2.group(ID) =B2.new(~:Week,\${A3.("\"No\":"+Name).concat@c()}) 4 =A3.run(~.run(B3(Week).field(A3.#+1,"Yes")))

8转置同时列间计算

 A B 1 =orcl.query@x("select name,amount_payable from T") 2 =create(name,\${12.string@d()}) =A1.group(customID) 3 for B2 =12.(null) 4 >A3.run(B3(month(due_date))=   amount_payable) 5 >B3.run(~=ifn(~,~[-1])) 6 =A2.record(B2.name|B3)

9子表动态插入主表

 A B 1 =orcl.query@x("select * from OrderDetail left join Order on   Order.ID=OrderDetail.OrderID") 2 =A1.group(ID) =A2.max(~.count()).("Product"+string(~)+","+"Amount"+string(~)).concat@c() 3 =create(ID,Customer,Date,\${B2}) >A2.run(A3.record([ID,Customer,Date]|~.([Product,Amount]).conj()))

10多表关联列转行

 A B 1 =orcl.query@x("select t1.stu_id stu_id,t1.stu_name stu_name,t2.subject   subject,t2.score score1,t3.score score2 from Students   t1 left join Exam t2 on  t1.stu_id=t2.stu_id   left join  Retest t3 on t1.stu_id=t3.stu_id and   t2.subject=t3.subject order by t1.stu_id,t2.subject 2 =A1.group(stu_id) =A1.group(subject) 3 =create(stu_id,stu_name,\${(B2.(~.subject+"_score")|"total_score"|B2.(~.subject+"_retest  ")).string()}) 4 >A2.run(A3.record([stu_id,stu_name]|B2.(~(A2.#).score1)|A2.sum(score1)|B2.(~(A2.#).score2)))

11分栏

 A B 1 =orcl.query@x("select * from World where Continent   in('Europe','Africa')") 2 =A1.select(Continent:"Europe") =A1.select(Continent:"Africa") 3 =create('Europe City',Population,'Africa City', Population) =A3.paste(A2.(City),A2.(Population),B2.(City),B2.(Population))

• 博文量
82
• 访问量
35432