SQL 和 SPL 的复杂静态转置对比

【摘要】
    转置功能常用报表等前端展现,将查询出来的数据转置成指定的显示格式。比如行转列,列转行,以及比较复杂的动态转置等等。SQL 和 SPL 是大家比较熟悉的程序语言,本文将探讨对于转置问题,这两种语言的解决方案和基本原理。如何简便快捷的处理转置运算,这里为你全程解析,并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的复杂静态转置对比

 

在前文中,我们为大家介绍了静态转置。接下来我们一起看一下,对于复杂的静态转置,SQL 和 SPL 是如何处理的。

 

1. 多行转多行

 

【例 1】 根据打卡记录,整理员工每日的外出数据。在日常考勤信息表中,每卡每天有 7 条数据:

PER_CODE

IN_OUT

DATE

TIME

TYPE

1110263

1

2013-10-11

09:17:14

In

1110263

6

2013-10-11

11:37:00

Break

1110263

5

2013-10-11

11:38:21

Return

1110263

0

2013-10-11

11:43:21

NULL

1110263

6

2013-10-11

13:21:30

Break

1110263

5

2013-10-11

14:25:58

Return

1110263

2

2013-10-11

18:28:55

Out

期望得到如下格式结果:

PER_CODE

DATE

IN

OUT

BREAK

RETURN

1110263

2013-10-11

9:17:14

18:28:55

11:37:00

11:38:21

1110263

2013-10-11

9:17:14

18:28:55

13:21:30

14:25:58

 

 

SQL的解决方案:

    本例中需要多条记录合并计算,根据目标结构返回多条记录。我们把数据按员工号和日期时间排序,每 7 行记录作为一组。顺序取出每组 [1,7,2,3] 行的时间,分别作为 IN,OUT,BREAK,RETURN 的值,再取出每组 [1,7,5,6] 行的时间作为第二组值。对于不支持 PIVOT\UNPIVOT 的数据库,SQL 语句如下:

 

   WITH CTE1 AS (

      SELECT

         PER_CODE,IN_OUT,"DATE","TIME",TYPE,

         MOD(ROWNUM-1,7)+1 GROUP_ORDER

      FROM DAILY_TIME

      ORDER BY PER_CODE,"DATE","TIME"

   )

   SELECT * FROM (

      SELECT

         T_IN.PER_CODE,T_IN."DATE",T_IN."IN",T_OUT."OUT",

         T_BREAK.BREAK,T_RETURN."RETURN"

      FROM (

         SELECT PER_CODE,"DATE","TIME" "IN"

         FROM CTE1

         WHERE GROUP_ORDER=1

      ) T_IN

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" "OUT"

         FROM CTE1

         WHERE GROUP_ORDER=7

      ) T_OUT

      ON T_IN.PER_CODE=T_OUT.PER_CODE

         AND T_IN."DATE"=T_OUT."DATE"

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" BREAK

         FROM CTE1

         WHERE GROUP_ORDER=2

      ) T_BREAK

      ON T_IN.PER_CODE=T_BREAK.PER_CODE

         AND T_IN."DATE"=T_BREAK."DATE"

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" "RETURN"

         FROM CTE1

         WHERE GROUP_ORDER=3

      ) T_RETURN

      ON T_IN.PER_CODE=T_RETURN.PER_CODE

         AND T_IN."DATE"=T_RETURN."DATE"

      UNION ALL (

         SELECT

            T_IN.PER_CODE,T_IN."DATE",T_IN."IN",T_OUT."OUT",

            T_BREAK.BREAK,T_RETURN."RETURN"

         FROM (

            SELECT PER_CODE,"DATE","TIME" "IN"

            FROM CTE1

            WHERE GROUP_ORDER=1

      ) T_IN

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" "OUT"

         FROM CTE1

         WHERE GROUP_ORDER=7

      ) T_OUT

      ON T_IN.PER_CODE=T_OUT.PER_CODE

         AND T_IN."DATE"=T_OUT."DATE"

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" BREAK

         FROM CTE1

         WHERE GROUP_ORDER=5

      ) T_BREAK

      ON T_IN.PER_CODE=T_BREAK.PER_CODE

         AND T_IN."DATE"=T_BREAK."DATE"

      LEFT JOIN (

         SELECT PER_CODE,"DATE","TIME" "RETURN"

         FROM CTE1

         WHERE GROUP_ORDER=6

      ) T_RETURN

      ON T_IN.PER_CODE=T_RETURN.PER_CODE

         AND T_IN."DATE"=T_RETURN."DATE"

      )

   )

   ORDER BY PER_CODE,"DATE",BREAK

 

这个 SQL 语句已经很难看懂了。以 ORACLE 11g 为例,我们可以使用 PIVOT 简化 LEFT JOIN 部分,SQL 语句如下:

 

   WITH CTE1 AS (

      SELECT

         PER_CODE,IN_OUT,"DATE","TIME",TYPE,

         MOD(ROWNUM-1,7)+1 GROUP_ORDER

      FROM DAILY_TIME

      ORDER BY PER_CODE,"DATE","TIME"

   )

   SELECT *

   FROM (

      SELECT *

      FROM (

         SELECT

            PER_CODE,"DATE","TIME",GROUP_ORDER

         FROM CTE1

         WHERE GROUP_ORDER IN (1,7,2,3)

      )

      PIVOT(

         MIN("TIME") FOR GROUP_ORDER

         IN (1 AS "IN",7 AS "OUT",2 AS BREAK,3 AS "RETURN")

      )

      UNION ALL

      (

         SELECT *

         FROM (

            SELECT

               PER_CODE,"DATE","TIME",GROUP_ORDER

            FROM CTE1

            WHERE GROUP_ORDER IN (1,7,5,6)

         )

         PIVOT(

            MIN("TIME") FOR GROUP_ORDER

            IN (1 AS "IN",7 AS "OUT",5 AS BREAK,6 AS "RETURN")

         )

      )

   )

   ORDER BY PER_CODE,"DATE",BREAK

 

SPL的解决方案:

    虽然转置后的表结构是可以确定的,但是用函数 A.pivot() 实现起来会很复杂。这时可以先创建目标数据结构,再填充数据。


A

1

=create(PER_CODE,DATE,IN,OUT,BREAK,RETURN)

2

=T("DailyTime.txt").sort(PER_CODE,DATE,TIME)

3

=A2.group((#-1)\7).(~([1,7,2,3,1,7,5,6]))

4

>A1.record(A3.conj([~.PER_CODE,~.DATE]|~.(TIME).m([1,2,3,4])|[~.PER_CODE,~.DATE]|~.(TIME).m([5,6,7,8])))

A1:根据目标结构创建空表。

A2:导入日常打卡记录,并按员工号和日期时间排序。

A3:每 7 条记录为一组,每组按指定顺序([1,7,2,3,1,7,5,6])返回记录。

A4:将所有的记录数据按目标顺序合并,再添加到 A3 创建的表中。

 

    我们先分析一下 SPL 的解决方案:首先我们按照期望的结构创建一个空表。然后将数据排序,每 7 条记录分为一组,每组的成员取 [1,7,2,3,1,7,5,6],即要生成的 2 条记录的时间值。最后再按目标结构顺序填入数据即可。而在 SQL 语句中,尽管使用了 PIVOT 函数,SQL 语句仍然非常复杂。这个题目并不适合用 PIVOT 来实现,如果 SQL 中也按照 SPL 的思路来做就会遇到一些问题。首先 SQL 中的分组并不会保留分组子集,这导致我们不能像 SPL 中一样,在分组后再进行复杂计算。另外 SQL 中的集合是无序的,我们可以利用行号来计算每组中的序号,但是不能像 SPL 中一样很方便的使用多个序号有序的访问成员。

 

 

2. 行转列同时行间计算

 

【例 2】 根据用户支付明细表,统计各个用户 2014 年每月应付金额的汇总表。部分数据如下:

ID

CUSTOMERID

NAME

UNPAID

ORDER_DATE

112101

C013

CA

12800

2014/02/21

112102

C013

CA

3500

2014/06/15

112103

C013

CA

2600

2015/03/21

期望得到如下格式结果:

NAME

1

2

3

4

5

6

7

8

9

10

11

12

CA


12800

12800

12800

12800

3500

3500

3500

3500

3500

3500

3500













 

 

SQL的解决方案:

    先构造目标结构,再填充数据的解决方案,在 SQL 中无法实现。所以每个问题都需要思考不同的解决方案,我们知道最后一步可以用 PIVOT 函数将月份行转列,之前需要把数据准备成如下格式:

NAME

ORDER_MONTH

UNPAID

CA

1


CA

2

12800

CA

3

12800

CA

4

12800

CA

5

12800

CA

6

3500

CA

7

3500

    在数据表中,并不是每个月份都有记录,而我们最终需要的是 2014 年每个客户每个月的应付金额表格。所以我们使用 2014 年有应付记录的客户唯一值列表,与 1 到 12 月份叉乘,得到的就是 2014 年每个客户每个月的表格。接下来我们使用这个表格与用户支付明细表进行左连接,得到每个客户每个月的应付金额。此时的表格大概是这样的:

NAME

ORDER_MONTH

UNPAID

CA

1


CA

2

12800

CA

3


CA

4


CA

5


CA

6

3500

CA

7


 

但是到这一步还没有完,我们期望将表中 3 到 5 月的应付金额填充为 2 月的 12800,将 CA 客户 7 月以后的应付金额填充为 6 月的 3500。这时我们可以使用子查询,当月没有应付金额时,选出该客户前面的月份中有应付金额的记录,并从这些记录中选择月份最靠后的一条。最后我们看一下 SQL 语句:

 

   WITH CTE1 AS(

      SELECT T1.NAME,T1.ORDER_MONTH,T2.UNPAID

      FROM (

         SELECT *

         FROM (

            SELECT DISTINCT CUSTOMERID, NAME

            FROM PAYMENT

            WHERE EXTRACT (YEAR FROM ORDER_DATE)=2014

         )

         CROSS JOIN (

            SELECT 1 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 2 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 3 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 4 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 5 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 6 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 7 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 8 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 9 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 10 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 11 ORDER_MONTH FROM DUAL

            UNION ALL SELECT 12 ORDER_MONTH FROM DUAL

         )

      ) T1

      LEFT JOIN (

         SELECT

            CUSTOMERID, NAME,

            EXTRACT (MONTH FROM ORDER_DATE) ORDER_MONTH, UNPAID

         FROM PAYMENT

         WHERE EXTRACT (YEAR FROM ORDER_DATE)=2014

      ) T2

      ON T1.NAME=T2.NAME AND

         T1.ORDER_MONTH=T2.ORDER_MONTH

      ORDER BY NAME,ORDER_MONTH

   ),

   CTE2 AS (

      SELECT

         T1.NAME,T1.ORDER_MONTH,

         NVL(T1.UNPAID,

            (

               SELECT

                  MIN(UNPAID) KEEP (DENSE_RANK FIRST ORDER BY ORDER_MONTH DESC)

               FROM CTE1 T2

               WHERE T1.NAME=T2.NAME AND

                  T2.UNPAID>0 AND

                  T2.ORDER_MONTH<T1.ORDER_MONTH

            )

         ) UNPAID

      FROM CTE1 T1

      ORDER BY T1.NAME,T1.ORDER_MONTH

   )

   SELECT *

   FROM CTE2

   PIVOT(

      MIN(UNPAID) FOR ORDER_MONTH

      IN (

         1 AS "1",2 AS "2",2 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 语句还是太复杂了,我们再来看一下 SPL 的解决方案。

 

SPL的解决方案:

    这个问题用函数 A.pivot() 实现起来会很复杂。可以按照上一题的思路,先创建目标数据结构,再填充数据。


A

1

=create(NAME,${12.string()})

2

=T("Payment1.txt").select(year(ORDER_DATE)==2014).group(CUSTOMERID)

3

>A2.((m12=12.(null),~.(m12(month(ORDER_DATE))=UNPAID),   m12.(~=ifn(~,~[-1])),A1.record(NAME|m12)))

A1:根据目标结构创建空表。

A2:导入 2014 年用户支付明细表,并按客户 ID 分组。

A3:循环每组数据,对成员再进行循环,计算每个月的应付金额,最后和客户名称一起添加到 A1 创建的表中。

 

    与 SQL 语句相比,SPL 不仅脚本很简洁,更重要的是思路清晰。对于复杂的静态转置,我们可以先创建目标结构,再填充数据。而且 SPL 中的集合都是有序集合,我们可以很方便的进行行间计算。

 

 

3. 数据分栏

数据分栏常用于数据展现,将有相似属性的数据分栏列出来,可以互相比较查看。我们通过下面例子具体看一下,SQL 和 SPL 是如何实现数据分栏功能的。

 

【例 3】 分栏列出销售部和研发部工资超 1 万的员工名称及工资(每栏按从多到少排序)。员工表部分数据如下:

ID

NAME

SURNAME

STATE

DEPT

SALARY

1

Rebecca

Moore

California

R&D

7000

2

Ashley

Wilson

New   York

Finance

11000

3

Rachel

Johnson

New   Mexico

Sales

9000

4

Emily

Smith

Texas

HR

7000

5

Ashley

Smith

Texas

R&D

16000

期望结果如下:

SALESNAME

SALARY

RDNAME

SALARY

Madeline

15000

Ashley

16000

Jacob

12000

Jacob

16000

Andrew

12000

Ryan

13000

 

SQL的解决方案:

首先根据部门名称等于销售或者研发,并且收入大于 1 万进行条件过滤。因为需要分栏显示,需要查询出每组的行号,再通过行号相等进行全连接。SQL 语句如下:

 

   SELECT

      T1.NAME SALESNAME, T1.SALARY, T2.NAME RDNAME, T2.SALARY

   FROM (

      SELECT

         NAME,SALARY, ROW_NUMBER()OVER (ORDER BY SALARY DESC) NO

      FROM EMPLOYEE

      WHERE DEPT='Sales' AND SALARY >10000

   ) T1

   FULL JOIN (

      SELECT

         NAME,SALARY, ROW_NUMBER()OVER (ORDER BY SALARY DESC) NO

      FROM EMPLOYEE

      WHERE DEPT='R&D' AND SALARY >10000

   ) T2

   ON T1.NO=T2.NO

 

SPL的解决方案:

    SPL处理数据分栏,与之前的动态转置方法类似。还是先构造目标数据结构,再填入数据。SPL 脚本如下:


A

1

=T("Employee.csv").select(SALARY   >10000).sort@z(SALARY)

2

=A1.select(DEPT:"Sales")

3

=A1.select(DEPT:"R&D")

4

=create('SALESNAME',SALARY,'RDNAME',   SALARY)

5

=A4.paste(A2.(NAME),A2.(SALARY),A3.(NAME),A3.(SALARY))

A1:导入员工表,选出工资超过 1 万的记录,并按工资降序排列。

A2:取出销售部数据。

A3:取出研发部数据。

A4:按目标结构创建一个空表。

A5:使用函数 A.paste() 将值粘贴到对应列。

 

经过上面的讨论,我们可以看到,SQL 提供的静态转置功能 PIVOT 和 UNPIVOT 适用范围很受限,而且还只有部分数据库支持。要用 SQL 实现一些比较复杂的静态转置功能,常常会遇到语句过于复杂的问题,而且也缺少一个标准的解决思路。

而 SPL 所提供的转置功能要更加灵活,适应性也更加广泛,可以满足各种复杂的转置需求。更重要的是,SPL 处理复杂转置问题的思路非常清晰,首先按照需求创建目标数据结构,再将计算出的数据依次填入表中即可。

在后续的文章中,我们将为大家介绍 SQL 和 SPL 是如何解决动态转置问题的。

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾