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

来源：互联网发布：linux squid https 编辑：程序博客网时间：2024/06/03 23:39

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

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

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

简单行列转置

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

OrderID

Client

SellerId

Amount

OrderDate

DSG

19480

2014-06-20 00:00

ERN

979

2014-06-13 00:00

JFE

28972

2014-12-11 00:00

OFS

4829

2014-02-24 00:00

ERN

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

42Below Vodka

Vodka

New Zealand

301

Absolut Vodka

Vodka

Sweden

Appleton Estate Reserve

Rum

Jamaica

202

Bacardi Superior

Rum

Puerto Rico

741

Baileys Irish Cream

Cordials

Ireland

434

Ballantines Special 12Years

Whisky

Scotland

237

Black Heart Rum

Rum

New Zealand

338

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

集算器代码：

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

A3存储计算结果，如下：

多列转一行

Students表格式如下：

name

age

sex

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

Aage

Asex

Akg

Bage

Bsex

Bkg

Cage

Csex

Ckg

集算器代码：

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

2015/04/21 13:30

X6SF_SARF1

2015/04/21 13:45

X6SF_SARF1

2015/04/21 14:00

X6SF_SARF1

2015/04/21 13:15

XC_01

2015/04/21 13:30

XC_01

2015/04/21 13:45

XC_01

2015/04/21 14:00

XC_01

集算器代码：

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

Ashley

20140404

Rachel

20150101

Emily

20140909

Ashley

20150225

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

project

operator

actionTime

Ashley

20140404

Rachel

20150101

Emily

20140909

Ashley

20150225

集算器代码：

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

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

计算结果如下：

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

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

Table1部分数据如下：

Prob

Cost

Visible

C3001

100

C3002

C3003

200

Table2部分如下数据：

Item

Count

C3001

555

C3001

666

C3001

777

C3002

888

C3002

111

C3003

222

期望的结果：

Prob

Cost

Visible

Item1

Count1

Item2

Count2

Item3

Count3

C3001

100

555

777

666

C3002

888

111

C3003

200

222

集算器代码：

用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

+70000000

A02

mfc

John

+18761221

A03

java

Jack

+8014001231

A04

mfc

Tim

+008613133123

A05

John

+18761221

dColQuestion部分数据如下：

qID

tID

status

A01

yes

A01

yes

A02

yes

A03

A04

A05

yes

集算器代码：

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

A3存储计算结果，如下：

补齐月份再转置

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

time

quantity

2014-01-01 15:20:25

2014-02-21 16:11:23

2015-01-05 11:14:21

2015-02-11 15:21:11

需要将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

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

scooby

weight

scooby

hair

long

mickey

mouse

mickey

hair

mickey

weight

理想的结果：

userid

type

hair

weight

mickey

mouse

scooby

dog

long

集算器代码：

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

三表关联列转行

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

Students

stu_id

stu_name

class_id

Ashley

1-1

Rachel

1-1

Emily

1-3

Exam

stu_id

subject

score

java

c++

java

c++

java

c++

Retest

stu_id

subject

score

c++

java

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

stu_id

stu_name

java_score

c++_score

scoresSum

javaRetest

c++Retest

Ashley

156

Rachel

125

Emily

141

集算器代码：

0 0