集算器实现SQL转置的通用方法

  转置是SQL中常见的算法，比如静态/动态行列转置，正转置/逆转置，单层/多层，对齐/补齐，无计算列/有计算列。针对上述不同的场景，SQL要用不同的方法来处理，有时要用pivot函数，有些只能用group+case when，有时要用union，有时只能用高级语言实现对应的动态SQL。方法太多，程序员缺乏清晰的思路，导致代码难以书写。

  免费的集算器支持动态脚本、有序计算、集合运算，可用一种方法实现不同场景下的行列转置，即：先取数、再构建空结果集、最后填入数据。集算器还支持独立使用、控制台执行、报表调用、JAVA代码调用，详情参考集算器辅助SQL编写的应用结构。

  下面举例说明SQL开发中常见的转置问题，以及集算器的通用解法。

  简单行列转置

  数据库表SALES存储着订单数据，部分数据如下：

OrderID	Client	SellerId	Amount	OrderDate
1	DSG	13	19480	2014-06-20 00:00
2	ERN	18	979	2014-06-13 00:00
3	JFE	19	28972	2014-12-11 00:00
4	OFS	21	4829	2014-02-24 00:00
5	ERN	22	21392	2014-02-01 00:00

  现在要计算出指定年份每个月订单的总金额、最大订单金额、最小订单金额，以及总订单数，并将数据转置成13列4行，即：四种算法是第一列，列名为subtotal，每个月占一列，列名分别是1、2、3、4…

  集算器代码：

  A1取数：用SQL进行简单的分组汇总。结果如下：

  A2创建空结果集：字段为“subtotal,1,2,3,4,5,6,7,8,9,10,11,12”。集合.string()可将集合成员合并为逗号分隔的字符串，${}可将字符串解析为表达式动态执行。

  A3：向空结果集逐条填入数据。函数fno可取得字段数，fname可按序号取得字段名，field可按序号取得某一列，run可循环集合\结果集，record可追加记录。追加后A2如下：

  可以看到，实现转置的通用方法分为三步：SQL取数、用create函数创建空结果集、用run函数循环源数据并用record函数逐条追加，其中追加数据的代码会根据场景的不同而略有变化，下面详述。

动态行列转置

  表liquors存储着各种酒的产地、类型、库存，部分源数据如下：

Lid	Name	Type	Production	Stock
1	42Below Vodka	Vodka	New Zealand	301
2	Absolut Vodka	Vodka	Sweden	95
3	Appleton Estate Reserve	Rum	Jamaica	202
4	Bacardi Superior	Rum	Puerto Rico	741
5	Baileys Irish Cream	Cordials	Ireland	434
6	Ballantines Special 12Years	Whisky	Scotland	237
7	Black Heart Rum	Rum	New Zealand	338

  现在要统计出每个产地每种类型的库存，其中每种类型都需要单独的字段。

  集算器代码：

  思路仍然是取数、建空结果集、填数。A2是结果集中2至N列的列名，函数id等价于SQL中的distinct函数。A4对A1按照Production字段分组，A5循环每组数据，每次向A3追加一条记录。其中函数align可将数据按照指定集合分组，允许组内成员为空。

  A3存储计算结果，如下：

多列转一行

  Students表格式如下：

name	age	sex	kg
A	10	f	30
B	11	f	35
C	12	m	33

  需要将所有的记录拼在一行，期望结果如下：

Aage	Asex	Akg	Bage	Bsex	Bkg	Cage	Csex	Ckg
10	f	30	11	f	35	12	m	33

  集算器代码：

  A2动态创建空序表，A3将A1组合为一条记录追加到A2。其中string(字段)表示将其他类型转为字符串，集合.string()表示将集合成员拼为字符串。也可用这句代码追加记录：>A2.record(A1.conj(~.array().to(2,4)))。

多层转置

  库表kpi中，f_site相同的4条记录是一组数据，现在要对每组数据进行行列转置，使dataset_date对应的值变成字段，使字段ioh_kpi、idh_kpi、iol_kpi变成KPI Name列的字段值。部分源数据如下:

dataset_date	f_site	ioh_kpi	idh_kpi	iol_kpi
2015/04/21 13:15	X6SF_SARF1	1	2	3
2015/04/21 13:30	X6SF_SARF1	9	1	2
2015/04/21 13:45	X6SF_SARF1	8	9	1
2015/04/21 14:00	X6SF_SARF1	7	8	9
2015/04/21 13:15	XC_01	2	3	4
2015/04/21 13:30	XC_01	11	12	13
2015/04/21 13:45	XC_01	21	22	23
2015/04/21 14:00	XC_01	31	32	33

  集算器代码：

  A2取得不重复的dataset_date，即["2015-04-21 13:15","2015-04-2113:30","2015-04-21 13:45","2015-04-21 14:00"]。B2:取得A1中字段名，从第3个开始，即["ioh_kpi","idh_kpi","iol_kpi"]。之后动态创建二维表A3，字段名依次为site,KPIName,"2015-04-21 13:15","2015-04-21 13:30","2015-04-2113:45","2015-04-21 14:00"。A4-B5使用循环语句来追加数据，等价于A1.group(f_site).run(…)，但在步骤较多时比函数run结构清晰。结果如下：

逆转置

  表tb1的查询结果只有1条记录，但字段数较多，如下：

project	operator1	actionTime1	operator2	actionTime2	operator3	actionTime3	poerator4	actionTime4
A	Ashley	20140404	Rachel	20150101	Emily	20140909	Ashley	20150225

  现在需要将该表转置为两个字段多条记录的二维表，如下：

project	operator	actionTime
A	Ashley	20140404
A	Rachel	20150101
A	Emily	20140909
A	Ashley	20150225

  集算器代码：

  ((A1.fno()-1)/2)可算出结果集需要插入的记录数，之后用run函数循环追加记录。

  如果tb包含多条记录，则应当使用如下代码：

  计算结果如下：

子表动态插入主表（子表长度不定）

  Table1和Table2是主子表，通过ID关联，现在要将子表横向插入主表。已知子表记录经常变化，长度不定。

  Table1部分数据如下：

ID	Prob	Cost	Visible
C3001	100	50	1
C3002	90	33	1
C3003	200	75	0

  Table2部分如下数据：

ID	Item	Count
C3001	555	4
C3001	666	5
C3001	777	6
C3002	888	10
C3002	111	20
C3003	222	50

  期望的结果：

ID	Prob	Cost	Visible	Item1	Count1	Item2	Count2	Item3	Count3
C3001	100	50	1	555	4	777	6	666	5
C3002	90	33	1	888	10	111	20
C3003	200	75	0	222	50

  集算器代码：

  用SQL连接主子表，按ID分组，按照分组最大的记录数动态创建空序表A4，再将主表和子表字段拼成记录追加到A4中。集算器的group函数可以只进行分组运算而不聚合，这一点与SQL不同。

  A4存储计算结果，如下：

子表动态插入主表（子表长度有限）

  dColThread和dColQuestion是主子表，用tID字段关联。主表每条记录对应的status字段值有多个，但不超过5个，需要横向插入主表的Phone、Decline字段之间，依次命名为QuestionNo1、QuestionNo2…QuestionNo5。

  dColThread部分数据如下：

tID	ApplicationName	User	Phone	Decline
A01	mfc	Bill	[phone]	1
A02	mfc	John	[phone]	2
A03	java	Jack	[phone]	6
A04	mfc	Tim	+008613133123	4
A05	db	John	[phone]	8

  dColQuestion部分数据如下：

qID	tID	status
1	A01	yes
2	A01	no
3	A01	yes
4	A02	yes
5	A03	no
6	A04	no
7	A04	no
8	A05	yes

  集算器代码：

  A3创建固定字段的二维表，之后循环A2中的组，取当前组中status的字段值，并补足至少5条记录，再向A3追加一条完整记录。

  A3存储计算结果，如下：

补齐月份再转置

  表tb有time、quantity这2个字段，存储着每天的货物销售量，其中某些月份的销售量可能为空。部分源数据如下：

time	quantity
2014-01-01 15:20:25	3
2014-02-21 16:11:23	2
2015-01-05 11:14:21	1
2015-02-11 15:21:11	2

  需要将tb表整理成12条数据，字段包括固定的月份（值为1-12）、不固定的每年的销售量（跨两年则需要2个字段，跨三年则需要3个字段），示意如下：

月份

2013年的销售量

2014年的销售量

…年的销售量

  集算器代码：

  A1用SQL执行分组汇总，A2取得年份列表，A3将A1按照12个月对齐分组，A4动态创建空二维表，A5循环A3的每组数据，每次向A4追加一条记录。其中A3如下：

  A4存储计算结果，如下：

带计算列的转置

  表tb1部分数据如下：

Prjno	Subtask	Ddate	Num
P9996	P9996-sub002	2015-01-01	123
P9996	P9996-sub002	2015-01-02	134
P9996	P9996-sub002	2015-01-03	345
P9996	P9996-sub002	2015-01-04	55
T0071	T-007-01	2015-01-01	3333
T0071	T-007-01	2015-01-02	356
T0071	T-007-01	2015-01-03	178

  现在要输入日期，生成当月该日期前所有日期的项目总和，如输入2015-01-03希望得到：

Prjno	Subtask	2015-01-01	2015-01-02	2015-01-03
P9996	P9996-sub002	123	134	345
T0071	T-007-01	3333	356	178

  集算器代码：

  查询数据，对源数据分组，循环每组数据，每次循环向空二维表插入一条记录。

动态定位行列转置

  在库表tb中，userid相同的3条记录是一组数据，现在要将组记录转为行记录。库表tb部分数据如下：

userid	type	descr
scooby	dog	dog
scooby	weight	50
scooby	hair	long
mickey	mouse	mouse
mickey	hair
mickey	weight	2

  理想的结果：

userid	type	hair	weight
mickey	mouse	2
scooby	dog	long	50

  集算器代码：

  函数align可将数据按某集合成员（[‘hair’,’weight’]）对齐，@n表示将无法对齐的数据单列一行，本案例中该行数据为mouse\dog对应的记录。结果如下：

三表关联列转行

  有三张表，分别是学生表、成绩表、补考成绩，以stu_id为关联字段，如下：

  Students

stu_id	stu_name	class_id
1	Ashley	1-1
2	Rachel	1-1
3	Emily	1-3

  Exam

stu_id	subject	score
1	java	77
1	c++	80
2	java	67
2	c++	58
3	java	56
3	c++	85

  Retest

stu_id	subject	score
2	c++	78
3	java	82

  现在要查询三张表，得到每个学生的各科成绩、总成绩、补考成绩，如下：

stu_id	stu_name	java_score	c++_score	scoresSum	javaRetest	c++Retest
1	Ashley	77	80	156
2	Rachel	67	58	125	78
3	Emily	56	85	141	82

  集算器代码：