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

时间:2022-01-30 11:12:57

  转置是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…

  集算器代码:

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

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

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

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

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

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

  可以看到,实现转置的通用方法分为三步: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


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

  集算器代码:

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

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

  A3存储计算结果,如下:

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

多列转一行

  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

  集算器代码:

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

  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


  集算器代码:

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

  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结构清晰。结果如下:

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

逆转置

  表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

  集算器代码:

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

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

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

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

  计算结果如下:

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

子表动态插入主表(子表长度不定)

  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转置的通用方法

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

  A4存储计算结果,如下:

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

子表动态插入主表(子表长度有限)

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

  dColThread部分数据如下:

tID

ApplicationName

User

Phone

Decline

A01

mfc

Bill

+70000000

1

A02

mfc

John

+18761221

2

A03

java

Jack

+8014001231

6

A04

mfc

Tim

+008613133123

4

A05

db

John

+18761221

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

  集算器代码:

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

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

  A3存储计算结果,如下:

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

补齐月份再转置

  表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年的销售量

…年的销售量

  集算器代码:

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

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

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

  A4存储计算结果,如下:

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

带计算列的转置

  表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

  集算器代码:

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

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

动态定位行列转置

  在库表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

  集算器代码:

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

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

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

三表关联列转行

  有三张表,分别是学生表、成绩表、补考成绩,以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

  集算器代码:

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