SQL 转置计算

最新推荐文章于 2024-06-14 10:14:22 发布
最新推荐文章于 2024-06-14 10:14:22 发布
阅读量797 收藏 6
点赞数
分类专栏： 集算器 文章标签： SQL 转置计算 pivot 双向转置 集算器 动态转置
原文链接：http://c.raqsoft.com.cn/article/1596418412177
版权

转置是 SQL 常见算法，本文细分为行转列、列转行、动态转置、关联转置等多种情况，分别进行算法分析并给出案例代码。对于 SQL 难以实现的转置，还给出了方便的 esProc 解决方案, 通过以下文章了解详情。

转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。

基础转置

行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：

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

yearquarteramount ---->yearQ1Q2Q3Q4
year2018Q189  year201889938899
year2018Q293  year201992979088
year2018Q388       
year2018Q499       
year2019Q192       
year2019Q297  ，    
year2019Q390       
year2019Q488       

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

YearQ1Q2Q3Q4 ---->yearquarteramount
year201889938899  year2018Q189
year201992979088  year2018Q293
       year2018Q388
       year2018Q499
       year2019Q192
       year2019Q297
       year2019Q390
       year2019Q488

 

早期SQL的解决方案

对于行转列，早期的SQL没有pivot之类的专用函数（MySQL、HSQLDB等数据库现在也没有），这种情况下只能用多个基本函数的组合来实现行转列，同一个问题往往有多种实现方法。

方法1：case when子查询+分组汇总

/*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;

方法2：sum if+分组汇总：

/*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;

其他方法还有 WITH ROLLUP+分组汇总或UNION+分组汇总等，这里不一一列举。这些方法表面各异，但本质其实都差不多，都是用分组的方法算出year的值，用枚举的方法依次生成新列Q1-Q4，同时用汇总的方式生成新列的值。

可以看到，即使最基础最简单的转置，早期SQL的代码也很长。原因在于，每个新列都要枚举出来，新列越多，代码就越长。如果新列是12个月、各州各省，可以想象SQL会更长。

只要新列是已知的，用笨办法总能枚举出来，所以新列多只会影响代码长度，并不会影响难度。如果新列是未知的，想枚举就困难多了。比如：大客户名单经常变动，需要将动态的大客名单由行转为列。遇到这种情况，SQL就很难解决了，通常要求助存储过程\JAVA等语言工具，代码难度和维护难度都会陡然提升。

上面SQL其实还有个毛病：汇总算法难以理解。原表每年每季度只有一条数据，所以原本是不必汇总的，但因为计算year列需要分组，而SQL规定分组的同时必须汇总，所以必须对每年每季度的一条数据进行难以理解的汇总。因为这里的汇总毫无意义，所以汇总算法可以随便选，并不影响计算结果，比如将max换成sum。

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

列转行不涉及难以理解的汇总，早期SQL的思路相对简单，只需按列名依次取出Q1-Q4的记录，再用union拼起来就行，具体写法如下：

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实现转置确实不够方便，所以数据库厂商近几年推出专用函数，试图让用户更方便地实现转置。

用pivot实现行转列：

/*oracle*/

select * from zz111

              pivot(

                        max(amount)   for quarter in(

                                       'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4

                        )

              )

仔细观察就会发现，pivot的确让代码缩短了不少，但并没解决本质问题，早期SQL存在的那些问题，现在一个都不少。

首先，pivot不能解决动态语法问题，所以遇到动态新列，依然要依靠存储过程/JAVA，开发难度和维护难度依然很大。

其次，pivot不能解决SQL集合问题，依然要用汇总去解决和汇总毫无关系的问题。对新手来说，这是难以理解的知识点，恳请留意。

在某些特殊情况下，汇总也是有意义的，比如销量分组表另有一个字段customer，使每年每季度的数据有多条，在这种情况下需要行转列，并计算每年每季度amount最大的值。如下：

customeryearquarteramount  yearQ1Q2Q3Q4
companyAyear2018Q189 ---->year2018899388100
companyByear2018Q1100  year201992979088
companyAyear2018Q293       
companyByear2018Q388       
companyCyear2018Q499       
companyDyear2019Q192       
companyEyear2019Q297       
companyFyear2019Q390       
companyGyear2019Q488       

在这种特殊情况下，使用汇总才是真正合理的，合理到核心代码都不用改：

/*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

                           )

              )

 

可以看到，上述特殊情况实际上不是字面意义上的“行转列”，而是“分组汇总后再行转列”。也许有些初学者会有疑问：这明明是两种不同的算法，为何会使用相同的核心代码？但读过前文的人就会明白，这是SQL集合化不彻底的缘故。

相对而言，列转行函数unpivot就好理解多了：

select * from zz111

               unpivot(

                       amount for   quarter in(

                                Q1,Q2,Q3,Q4

                       )

                )

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

双向转置

双向转置可以理解为行列互换或镜像，通常来说，交叉表的双向转置才有意义。

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

如下所示：

YearQ1Q2Q3Q4     ---->quarteryear2018year2019
year201889938899 Q18992
year201992979088 Q29397
      Q38890
      Q49988

        双向转置的实现思路就写在名字里，即先对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

上述代码包含了行转列和列转行两种算法，所以也兼具了两者的缺点，比如代码冗长、不支持动态行、汇总算法难理解。这里需要注意的是，JAVA\C++等过程性语言擅长多步骤计算，代码的复杂度和代码长度可视为线性关系，SQL则不同，很难分步骤分模块或断点调试，这就导致SQL的复杂度随代码长度呈指数增长。总之，你会发现双向转置要比它表面看起来更难实现。

上面的算法是先列转行再行转列，理论上似乎也可以反过来，即先行转列再列转行，但实际上并非如此。如果先行转列，就会导致子查询的数量从1个增加到4个（由union导致），不仅代码更长，而且性能更差。当然，如果用支持with 语句的Oracle等数据库，反过来就没问题。

事实上，如果不是小型数据库，而是Oracle或MSSQL，那直接pivot、unpivot联用就可以了，用不到with语句。代码如下：

/*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      

上述代码思路更清晰，但由于子查询难以调试难以按独立步骤运行，所以理解起来并不会太轻松。另外列转行的顺序是不可控的，为了让quarter列按Q1-Q4的固定顺序排列，最后必须用order by排序。可以想象，如果需要自定义顺序（比如0、a、1），则需要造假表并关联该假表，难度会大幅提升。

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

 

动态转置    

前面简单提到过动态转置，这里再具体解释一下：由于待转置的值不是固定的，而是会动态增减的，所以转换后的行或列也不是固定的，而是要动态计算，这种算法就是动态转置。

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

如下图：

DeptAreaAvgSalary    ---->DeptBeijingShanghai...
SalesBeijing3100 Sales31002700 
MarketingBeijing3300 Marketing33002400 
HRBeijing3200 HR32002900 
SalesShanghai2700     
MarketingShanghai2400     
HRShanghai2900     
 …      

乍一看，这个问题应该可以用pivot解决，只须在in里用子查询动态取得地区的唯一值，比如：

/*Oracle 11*/

select * from temp pivot (

    max(AvgSalary) for Area in(

          select distinct Area   from temp

    )

)

上述语句看上去很合理，但实际上，pivot里的in函数和一般的in函数不同，一般的in函数里的确可以用子查询，但pivot的in函数不能直接支持子查询。

要想直接支持子查询，必须使用古怪的xml关键字，即：

/*Oracle 11*/

select * from temp pivot xml(

    max(AvgSalary) for Area in(

          select distinct Area   from temp

    )

)

这样就会产生一个古怪的中间结果集，该结果集含有2个字段，其中一个字段的类型是XML，如下所示。

DeptArea_XML
HR<PivotSet><item><column name =   "AREA">Beijing</column><column name =   "MAX(AVGSALARY)">3200</column></item><item><column   name = "AREA">Shanghai</column><column name = "MAX(AVGSALARY)">3200</column></item></PivotSet>
Marketing<PivotSet><item><column name =   "AREA">Beijing</column><column name =   "MAX(AVGSALARY)">3300</column></item><item><column   name = "AREA">Shanghai</column><column name =   "MAX(AVGSALARY)">2400</column></item></PivotSet>
Sales<PivotSet><item><column name =   "AREA">Beijing</column><column name =   "MAX(AVGSALARY)">3100</column></item><item><column   name = "AREA">Shanghai</column><column name =   "MAX(AVGSALARY)">2700</column></item></PivotSet>

对于上述中间结果集，还需要动态解析XML，获得AREA的节点，并动态生成表结构，再动态填入数据，才能算出我们的目标。只用SQL已经无法实现此类动态算法，后续代码必须用JAVA或存储过程嵌入SQL才行，最终代码很长，放在文中影响阅读，这里就不贴了。

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

如下所示：

NameSourceIncome---->CategorySource1Income1Source2Income2
DavidSalary8000 DavidSalary8000Bonus15000
DavidBonus15000 DanielSalary9000  
DanielSalary9000 AndrewShares26000Sales23000
AndrewShares26000 RobertBonus13000  
AndrewSales23000      
RobertBonus13000      

本算法的整体思路很清晰：先生成结果表的表结构，再向结果表插入数据，最后输出结果表。

思路虽然清晰，但实际代码非常繁琐，这是因为代码中大量涉及动态语法，包括嵌套循环中的动态语法，而SQL本身不支持动态语法。为了弥补SQL的缺陷，只能用其他语言配合SQL，比如JAVA或存储过程。这些语言不擅长结构化计算，却非要实现结构化算法，代码必然冗长。有兴趣的可按如下步骤实现 ：

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.       查询结果表，返回数据。

可以想象，如果SQL支持动态语法，或者JAVA/存储过程内置结构化函数库（脱离SQL），那实际的代码就会精简很多 。

还应该注意到，上面第4步的算法是对Name去重，去重相当于分组后求分组字段的值，而第1步的算法是分组后求各组记录数。这两步同时有分组的动作，理论上是可以复用的，但由于SQL的集合化不彻底，分组的同时必须强制汇总，所以无法复用分组的结果。如果数据量小，能否复用并不重要，最多就是代码丑不丑的问题，而一旦遇到数据量较大或多处复用的算法，能否复用就决定性能高低了。

 

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

如下所示：

原表

Per_Codein_outDateTimeType
111026312013-10-1109:17:14In
111026362013-10-1111:37:00Break
111026352013-10-1111:38:21Return
111026302013-10-1111:43:21NULL
111026362013-10-1113:21:30Break
111026352013-10-1114:25:58Return
111026322013-10-1118:28:55Out

转置后目标表

Per_CodeDateInOutBreakReturn
11102632013-10-1109:17:1418:28:5511:37:0011:38:21
11102632013-10-1109:17:1418:28:5513:21:3014:25:58

 

由于转置后列数固定，无须动态算法，因此可用SQL实现本算法，具体如下：

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)

 转置后目标表的数据

WeekUser1User2User3
1YesNoYes
2YesYesNo
3YesNoYes
4NoYesYes

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，代码很长，这里照例不贴，下面只讲思路。

要实现上述算法，需要先进行准备工作：将用户表和记录表关联起来；新加计算列，算出Date字段值相对于2018-01-01的周数，最大不应超过53；对周数求最大值，可获得目标表的关键字列表rowKeys；对关联表去重，计算出目标表新增的列名colNames。

接下来是动态转置算法：用colNames动态生成建目标表的SQL，再动态执行SQL；循环rowKeys，每次循环时先从关联表取数据，再动态生成Insert SQL，再动态执行SQL。

上述动态转置算法前面也见到过，事实上，凡此类涉及动态列的转置，都有个动态生成目标表结构，再动态插入数据的过程。这个算法难度较大，这既是SQL缺乏动态语言能力的表现，也是我们不得不求助于JAVA/存储过程的根本原因。后面遇到类似的情况，我会用“动态转置”直接带过。

转置同时列间计算

前面都是单纯的转置，作为习题比较合适，在实际工作中，转置的同时通常会附带其他计算，比如列之间的计算。

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

如下所示：

源表

IDNameamount_payabledue_date
112101CA128002014-02-21
112102CA35002014-06-15
112104LA250002014-01-12
112105LA200002014-11-15
112106LA80002014-12-06

 

 

 

转置后目标表

name123456789101112
CA 128001280012800128003500350035003500350035003500
LA25000250002500025000250002500025000250002500025000200008000

因为转置后的列是固定的，所以可以用SQL解决，大致思路是：造一个包含单字段month，值为1-12的临时表t1；通过源表的日期算出月份，字段名也是month；用这两个表进行左关联，造出连续的应付款记录，注意这里有很多数据无效；使用pivot实现行转列，用min汇总去除无效数据。具体SQL如下：

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"))

上述SQL不长，但是很难理解，尤其是造无效数据这一古怪算法。之所以出现这种情况，是因为SQL集合本身没有序号，也不擅长有序计算，尤其是行间计算，只能采取一些古怪的手段去间接实现。

 

表间关联列转行

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

源表关系

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

转置后目标表

IDCustomerDateProduct1Amount1Product2Amount2Product3Amount3
132019-01-01Apple5Milk3Salt1
252019-01-02Beef2Pork4  
322019-01-02Pizza3    

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先把两表关联起来；对关联表（或子表）按ID分组，求各组记录数，求最大值，从而算出目标表的动态列列表colNames；对关联表（或主表）的ID去重，算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

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

源表数据及关系

Exam table  <----1:NStudents table  1:N ---->Retest table
stu_idsubjectscore stu_idstu_nameclass_id stu_idsubjectscore
1Chinese80 1Ashley301 2Chinese78
1Math77 2Rachel301 3Math82
2Chinese58 3Emily301    
2Math67        
3Chinese85        
3Math56        

转置后目标表

stu_idstu_nameChinese_scoreMath_scoretotal_scoreChinese_retestMath_retest
1Ashley8077156  
2Rachel586712578 
3Emily8556141 82

如果科目固定，就可以用SQL解决，先将Students和Exam左关联并piovt，然后Retest和Exam左关联并pivot，最后再左关联一次。

但每次考试的科目不固定，因此目标表的列是动态的，只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先将2表左关联至Students；对关联表按stu_id分组，求各组记录数，再求最大记录数，从而计算出目标表的动态列列表colNames；对关联表按stu_id去重，计算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

分栏

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

ContinentCityPopulation---->EuropeCityEuropePopulationAfricaCityEuropePopulation
AfricaCairo6789479 Moscow8389200Cairo6789479
AfricaKinshasa5064000 London7285000Kinshasa5064000
AfricaAlexandria3328196   Alexandria3328196
EuropeMoscow8389200     
EuropeLondon7285000     

目标表的结构是固定的，可以用SQL解决，思路是：过滤出包含欧洲城市的记录，用rownum算出行号，作为计算列；类似地，过滤出包含非洲城市的记录；将两者进行full join，并取出所需字段。

具体SQL如下：

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

 

总结

通过上面的讨论可以发现，只有最简单的三种转置可以直接用piovt/unpivot实现，且仅限大型数据库，还需注意xml解析、结果集乱序，以及难以移植的问题。

对于有一定难度的转置算法来说，如果列是固定的，通常就能用SQL解决，但代码通常很难写，需要熟知SQL的缺陷，并掌握各类古怪的技巧来弥补这些缺陷。前面遇到的缺陷包括：集合化不彻底、集合无序号、不擅长有序计算、难以分步计算、难以调试代码等。

如果列是动态的，复杂程度将大幅上升，只能用JAVA/存储过程，代码将非常繁琐。事实上，不支持动态结构，也是SQL的重大缺陷。

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

采用esProc 的 SPL能更好地适应这些问题。esProc 是专业的数据计算引擎，基于有序集合设计，像SQL一样提供了完善的结构化函数，又和Java等语言类似天然支持分步计算，相当于 Java 和 SQL 优势的结合。使用SPL （替代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组内记录行转列，分步计算并支持动态数据结构

 AB
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()})
5for gData=A5. Name | A5.conj([Source,   Income])
6 >A4.record(B5)

 

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

 AB
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复杂动态行列转置

 AB
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转置同时列间计算

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

 

9子表动态插入主表

 AB
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多表关联列转行

 AB
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分栏

 AB
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))
chaochao52001
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
从Oracle 表格行列转置说起第1/2页
09-11
虽然行列转置在表单内可能会带来较高的计算成本，因为它涉及多次扫描和聚合操作，但其优势在于表结构的稳定性。当新的星期值出现时，只需要添加新的记录，而不需要创建新的列，这有利于保持数据库的灵活性和扩展性。...
SQL中的行列转置
qq_42640067的博客
08-19 1万+
前言 在实际的开发过程中，我们可能会遇到这样的需求：需要将一行数据按照列拆分为多行，或者是需要将多行数据通过扩展列的形式合并为一行。这在SQL上一般称为行列转置，也就是将行转为列，列转为多行。下面我们一起来看看行列转置是如何实现的。 行列转置 1、行转列 行转列实际上是将多行数据按照某个相同的值通过扩展列的形式合并为一行数据。以下通过一个实例来说明。事先准备好3张表：student5表、score5表、subject5表。 student5表数据如下： score5表数据如下： 由于表中数据过多，所以这
SQL之行列转置以及非常规的行列转置
最新发布
zxcvbnm0207的博客
06-14 1223
创建数据表# 插入数据values ('张三', '语文', 80),('张三', '数学', 90),('张三', '物理', 85),('李四', '语文', 85),('李四', '数学', 92),('李四', '物理', 82);
SQL 转置查询
李世铭的博客
11-27 2088
SELECT 'zhangsan' AS 项目,IIF(xm='语文',zs,0) AS 语文,IIF(xm='数学',zs,0) AS 数学。SELECT 'wanger' AS 项目,IIF(xm='语文',we,0) AS 语文,IIF(xm='数学',we,0) AS 数学。SELECT 'zhangsan' AS 项目,IF(xm='语文',zs,0) AS 语文,IF(xm='数学',zs,0) AS 数学。group by 项目。
sql 转置_详谈转置 pivot
weixin_39589253的博客
12-09 584
问题概要 在日常工作中时常会遇到将数据表的行列进行转换的问题。SQL 提供了静态转置的功能 pivot 和 unpivot，但适用范围很受限，要用 SQL 实现一些比较复杂的转置功能常常会遇到语句过于复杂的问题，而且缺少一个标准的解决思路。而集算器的 SPL 语言，则因其语法的灵活和函数库的丰富，可以完美地弥补 SQL 在这方面的不足。 下面则通过实例详细阐述一下转置功能的实现。基础篇一、行转...
sqlserver 行列互转实现小结
09-11
这个过程通常称为“透视”或“转置”。这里主要介绍两种方法：静态方法和动态方法，以学生成绩为例进行说明。 1. **静态方法**： 静态方法适用于已知所有列名的情况。例如，我们有学生姓名（Student）、课程...
[数据库] SQL查询语句表行列转换及一行数据转换成两列1
08-03
子查询是在一个SQL查询语句中嵌套另一个查询，用于获取或计算某个特定值。在统计不同性质的学生总数时，可以使用子查询来分别统计不同学院、不同性别以及来自特定地区的学生人数。例如： ```sql -- 统计不同学院总...
数据库行列转置参考.pdf
11-23
以下是如何实现动态SQL转置的一个示例： ```sql DECLARE @sql VARCHAR(8000); SET @sql = 'SELECT Name AS 姓名 '; SELECT @sql = @sql + ', MAX(CASE Subject WHEN ''' + Subject + ''' THEN Result ELSE 0 END) ...
sql转置两种实现方式记录
jwl965278523的专栏
12-14 3899
sql转置简单与复制两种实现方式记录，工作中常用工具记录
在 SQL 中转置列和行的简单方法?
maomao1983zy的专栏
09-08 393
本文介绍了在 SQL 中转置列和行的方法?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
sql转置 -- 行转列，使用case when、if
热门推荐
慢慢来
07-31 2万+
SQL转置 转置使用 case when 或 if都可以。但是，若需要group by，则需要对case when 或 if 使用聚合函数。即： group by和聚合函数要么都出现，要么都不出现。 实现代码中用到了MAX函数，这个很重要，它可以返回多个字符串中ASCII最大的字符串， 不过使用中要注意，它可能改变返回的数据类型（String-&amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;BLOB）。 另外，MA...
SQL server中转置函数（列转行PIVOT()函数、行转列UNPIVOT()函数）
蓝星部队的博客
03-18 1万+
首先，通过两个表格说明本次要讲解的为什么要使用这两个函数？
Sql经典之数据表转置
abc_57699的博客
09-22 726
数据库表格TableA，如何得到TableB呢？TableA: userid subj score 張三 chinese 78 李四 chinese 89 張三 english 90 張三 maths 75 李四 maths 97T...
sql将多条数据数据行列转置到同一条数据
mr_kangaroo的博客
04-25 376
今天公司有这样一个业务统计各个公司每个月的计划销量和实际销量，需要将其展示到一个表格中，昨天晚上还想了半天因为要根据不同的月份和不同的公司做汇总,之前用group by语句查询之后再行列转置会变成一种倒梯形(存在重复数据)，当时不知道咋想的就要放到业务代码里去实现。今天早上睡醒之后突然想到我可以再套一层group by来根据公司ID进行数据求和然后再去重就ok了。再通过group by 公司ID的形式去求202101计划等这些列的和就出来了。最后不知道有没有更简单的方式形成这样的数据，求大佬解答。
sql 转置_谈谈转置功能的实现
weixin_39579726的博客
12-09 751
【摘要】 SQL 提供的静态转置功能 pivot 和 unpivot 适用范围很受限，要用 SQL 实现一些比较复杂的转置功能常常会遇到语句过于复杂的问题，而且也缺少一个标准的解决思路。而集算器的 SPL 语言，则因其语法的灵活和函数库的丰富，恰好可以弥补 SQL 这方面的不足。若想了解更多，请前往乾学院一探究竟：谈谈转置功能的实现!一、行转列1、数据库的 pivot pivot 并不是从一开始就...
SQL行列转置，汇总再揭
sinat_38531349的博客
10-13 617
SQL行列转置，汇总再揭 1.使用case when进行性别翻译 ，并将性别字段的 case when 表达式放在group by中，根据性别将数据分两行。2. 根据表头条件设置列，case when第一层 放性别（匹配性别分的两行），第二层 统计匹配条件的人口。初始数据，有区域、性别（1为男，2为女）、和人口；将数据根据、区、市、省对不同性别的人口进行分别汇总。行列转置，汇总再揭后的报表结果。
用SQL对经过排名的结果集进行转置
wzy0623的专栏
10-28 369
用SQL对经过排名的结果集进行转置
计算转置矩阵
05-31
转置矩阵的计算方法是将原矩阵的行列互换，即原矩阵的第i行第j列元素变成转置矩阵的第j行第i列元素。 在Python中，可以使用numpy库中的transpose函数或T属性来计算矩阵的转置。 示例代码如下： ```python import ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值