【摘要】
    转置功能常用报表等前端展现，将查询出来的数据转置成指定的显示格式。比如行转列，列转行，以及比较复杂的动态转置等等。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.concat@c()})
2	=T("PaymentDetail.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 是如何解决动态转置问题的。

DailyTime.txt

PaymentDetail.txt

Employee.csv

SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比