SQL 转置计算
转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。
基础转置
行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：
1.行转列：将销量分组表的quater字段里的值（行）Q1-Q4，转为新字段名（列）Q1-Q4，如下：
yearquarteramount---->yearQ1Q2Q3Q4
year2018Q189year201889938899
year2018Q293year201992979088
year2018Q388
year2018Q499
year2019Q192
year2019Q297，
year2019Q390
year2019Q488
2.列转行：将销量交叉表的字段名Q1-Q4，转为新字段quarter里的值Q1-Q4，如下：
YearQ1Q2Q3Q4---->yearquarteramount
year201889938899year2018Q189
year201992979088year2018Q293
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最大的值。如下：
customeryearquarteramountyearQ1Q2Q3Q4
companyAyear2018Q189---->year2018899388100
companyByear2018Q1100year201992979088
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
year201889938899Q18992
year201992979088Q29397
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...
SalesBeijing3100Sales31002700
MarketingBeijing3300Marketing33002400
HRBeijing3200HR32002900
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 pivotxml(
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
DavidSalary8000DavidSalary8000Bonus15000
DavidBonus15000DanielSalary9000
DanielSalary9000AndrewShares26000Sales23000
AndrewShares26000RobertBonus13000
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年的每周，各用户是否存在活动记录，用户名需转置为列。
如下所示：
源表结构
UserRecord
ID(pk) 1:N---->ID(pk)
NameDate(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
CA128001280012800128003500350035003500350035003500
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)
CustomerNumber(pk)
DateProduct
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_idsubjectscorestu_idstu_nameclass_idstu_idsubjectscore
1Chinese801Ashley3012Chinese78
1Math772Rachel3013Math82
2Chinese583Emily301
2Math67
3Chinese85
3Math56
转置后目标表
stu_idstu_nameChinese_scoreMath_scoretotal_scoreChinese_retestMath_retest
1Ashley8077156
2Rachel586712578
3Emily855614182
如果科目固定，就可以用SQL解决，先将Students和Exam左关联并piovt，然后Retest和Exam左关联并pivot，最后再左关联一次。
但每次考试的科目不固定，因此目标表的列是动态的，只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先将2表左关联至Students；对关联表按stu_id分组，求各组记录数，再求最大记录数，从而计算出目标表的动态列列表colNames；对关联表按stu_id去重，计算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。
分栏
11源表记录各大洲的部分城市人口，现在要分别找出欧洲和非洲的城市和人口，分两栏横向转置，注意目标列是固定的，但源表行数是动态的。如下所示：
ContinentCityPopulation---->EuropeCityEuropePopulationAfricaCityEuropePopulation
AfricaCairo6789479Moscow8389200Cairo6789479
AfricaKinshasa5064000London7285000Kinshasa5064000
AfricaAlexandria3328196Alexandria3328196
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))
