用SQL行列转换实现数据透视的一些思考

用SQL行列转换实现数据透视的一些思考

 

摘  要：根据对报表开发过程中碰到的需要用SQL行列转换进行解决的一类查询统计问题的分析，逐步探索求解得到一种较通用的解决思路，并用函数进行实现。该解决思路及函数实现可用于Oracle数据库开发中行列转换、数据透视等查询统计的应用开发，可直接供前端报表展示工具调用，也可用于后端存储过程中调用。

关键词：行列转换；数据透视；查询统计；数据库开发；SQL；函数。

1.开篇

在银行新资本协议风险相关的系统报表开发过程中，遇到以下需求：

根据损失数据收集表（见表1）生成某一时间段内损失数据条线类型分布表（表样见表2）。

表1：损失数据收集表

事件编号	发生日期	发生机构	业务条线	损失事件类型	损失金额
LE0001	2010-5-11	分行1	B4.商业银行	E1.内部欺诈	200
LE0002	2010-6-12	分行2	B5.支付和结算	E1.内部欺诈	400
LE0003	2010-7-14	分行3	B4.商业银行	E2.外部欺诈	300
LE0004	2010-8-15	分行4	B5.支付和结算	E2.外部欺诈	600
LE0005	2010-9-16	分行5	B4.商业银行	E1.内部欺诈	500
LE0006	2010-10-18	分行6	B5.支付和结算	E1.内部欺诈	200
…

表2 ：损失数据条线类型分布表

业务条线	E1.内部欺诈	E2.外部欺诈	E3.就业制度和工作场所安全事件	E4.客户、产品和业务活动事件	E5.实物资产的损坏	E6.信息科技系统事件	E7.执行、交割和流程管理事件
B1.公司金融	0	0	200	0	0	0	0
B2.交易和销售	0	0	0	400	0	0	0
B3.零售银行	0	0	0	0	300	0	0
B4.商业银行	200	800	0	0	0	0	0
B5.支付和结算	600	600	0	0	0	0	0
B6.代理服务	0	0	0	0	0	600	0
B7.资产管理	0	0	0	0	0	0	500
B8.零售经纪	0	0	200	0	0	0	0

表1是事件记录表，记录了每个损失事件的发生日期、机构、业务条线、损失事件类型、损失金额等信息。表2 是交叉表，纵向为业务条线，取值同表1中的业务条线（可称为分组列）；横向为损失事件类型，取值同表1中的损失事件类型（可称为转置列），所不同的是由表1中的纵向排列改为横向排列；表2中的数据项为将表1中的损失金额根据业务条线和损失事件类型汇总而得。从加工的规则和表样看，表2是对表1的一个数据透视表，行标签为业务条线，列标签为损失事件类型。

为得到从表1求出表2的通用解决方案，现对表1和表2进行以下抽象：

抽象一：将表1抽象为只有三列：分组列（group_col）、转置列(column_col)和值列(value_col)，表名为Ts。按一般的设计规则，业务条线和损失事件类型都应分别设计到数据字典，表1中仅保留对应的码值。为简化讨论，在此不做此处理。

抽象二：将表2抽象为行标签(row_tag)、列标签(col_tag)和统计值(stat_val)，表名为Tt。由于不想因为增加实体表而增加系统的复杂性，要求Tt是个临时表，或只是一个SQL。

从Ts到Tt的规则描述为：

规则一：行标签：SELECT DISTINCT group_col FROM Ts。

规则二：列标签：SELECT DISTINCT column_col FROM Ts。

规则三：统计值ij：SELECT SUM(value_col) FROM Ts WHERE group_col = 行标签i  and column_col = 列标签j。这里的SUM是一个聚合函数。

现在的需求抽象为：从表Ts按照规则一、规则二和规则三，生成Tt。

以下的讨论假定使用Oracle 10g数据库，并假定不使用存储过程实现。

2.使用SQL硬编码实现

首先想到的是使用SQL硬编码实现：用group_col进行分组，使用decode函数或case语句对column_col的值进行判别，对value_col进行求和。

使用decode函数求解Tt的代码见代码1。

代码1：使用SQL硬编码

SELECT group_col 
  ,nvl(sum(decode(to_char(column_col),'E1.内部欺诈',value_col)),0.0)"E1.内部欺诈"
  ,nvl(sum(decode(to_char(column_col),'E2.外部欺诈',value_col)),0.0)"E2.外部欺诈"
  ,nvl(sum(decode(to_char(column_col),'E3.就业制度和工作场所安全事件',value_col)),0.0)"E3.就业制度和工作场所安全事件"
  ,nvl(sum(decode(to_char(column_col),'E4.客户、产品和业务活动事件',value_col)),0.0)"E4.客户、产品和业务活动事件"
  ,nvl(sum(decode(to_char(column_col),'E5.实物资产的损坏',value_col)),0.0)"E5.实物资产的损坏"
  ,nvl(sum(decode(to_char(column_col),'E6.信息科技系统事件',value_col)),0.0)"E6.信息科技系统事件"
  ,nvl(sum(decode(to_char(column_col),'E7.执行、交割和流程管理事件',value_col)),0.0)"E7.执行、交割和流程管理事件" 
FROM Ts 
WHERE 1=1 
GROUP BY group_col 

 

也可以使用case when …then…end语句来代替上式中的decode函数。再此不另附编码。

引种方案是最直接的解决方案。其优点是直观。缺点是使用了太多的硬编码，看起来非常的繁琐，特别是当column_col的值较多或需要使用多个column_col对应的value_col进行计算时，重复性的硬编码将会在代码中多次出现。不论从可读性、可维护性和扩展性上来讲，这都不能称为一个好的解决方案。

如果能有什么方法直接将column_col的值转置成列名，然后在SQL直接按列名取对应的统计值就好了。那样的话，生成报表的代码就可以写成类似这样的代码：

SELECT group_col，E1.内部欺诈，E2.外部欺诈

FROM  (…)

有没有这样的方法呢？

3.TABLE

能直接按列名访问的，可以是TABLE，可以是VIEW，也可以是CURSOR。

先看TABLE。

使用Table，有两种思路，一种是批量时使用Tt。由于不想因为增实体表而增大系统的复杂性，此种思路暂不考虑。

另一种思路是在SQL中生成临时表，生成报表后，在SQL中将临时表删除。这种思路带来的问题是如何解决临时表的命名冲突或数据一致性问题。

对于临时表的命名冲突，可以根据查询的条件组成临时表名，但这样会造成生成很多临时表的混乱局面，也无法避免两个查询条件一致时的并发问题。出于此种考虑，放弃掉使用临时表的想法。

4.VIEW

再看VIEW。

View是基于一个表或多个表或视图的逻辑表。建立View不会带来物理存储上的混乱。看起来是个不错的选择。但在解决此问题时，并不适合使用View，原因如下：

通用性问题：如果为这个需求建立了视图，那么再出现一个类似的需求是否也要建立一个视图？而这样的需求可以说是无穷尽的。只对表1，就可以提出很多种类似于表2的查询统计。如按机构、业务条线统计损失金额，再如按机构、损失事件类型统计损失金额等，可以按时间、机构、业务条线、损失事件类型、金额范围等多个维护的两两组合。

命名问题：如果使用临时生成的View，存在命名冲突的问题。

对于临时生成View，在技术上似乎可行，示例见代码2：

代码2：使用临时视图

WITH t0 AS(
  SELECT create_v('v_test') FROM dual  --生成临时视图v_test
) 
,t1 AS (
  SELECT * FROM v_test                 --使用视图v_test
)
, t2 AS(
  SELECT delete_v('v_test') FROM dual  --删除视图v_test
) SELECT * FROM t1, t2;

 

以上代码试图在t0段中调用create_v函数生成临时视图v_test，在t1段中使用该视图，在t2段中删除视图。

但代码2中至少存在两外错误：一是如果v_test是create_v()函数生成的临时视图，则t1段时无法直接使用；二是如果v_test视图早就存在，create_v()函数只是修改，则执行时报ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML，即违反了“在查询或数据管理语言中不能执行数据定义语言、提交或回滚”的约束。

由此看来，使用VIEW并非可行之选。

5.CURSOR

Cursor又叫游标，其作用是用于临时存储从数据库中提取的数据。

考虑到封装性，可以将用一个函数动态生成代码1，然后用代码1生成游标，并返回这个游标给外层的SQL，期望外层的SQL能能象使用table那样使用返回的游标。

Oracle中有三种Cursor，分别是隐式Cursor、显式Cursor和Ref Cursor（动态Cursor）。其中Ref Cursor可以作为函数参数或函数返回值进行传递，这将成为首先考虑的途径。Oracle还提供了DBMS_SQL包，该包中提供了更象是类似于C或Java等高级编程语言中的Cursor及操作，这也给我们提供了一种选择。

不管使用哪种Cursor，首要的任务是编制动态生成代码1的函数，在这里命名为GETNVSQL()，含义为GET Name Value SQL。

5.1GETNVSQL函数

GETNVSQL函数的功能为动态生成代码1。执行分以下两个步骤进行：

步骤一：生成列标签cursor。

步骤二：对于列标签cursor每一行，组装成decode()语句。

步骤三：组装上SELECT 、WHERE子句、GROUP子句和ORDER子句。

考虑到通用性，将表名、分组列、转置列、值列、聚合函数名都设计为函数输入参数。为限制选择数据、排序方式、缺值处理，将选择条件、行列排序和空值处理也设计为函数输入参数。完整的GETNVSQL函数参见代码3。

代码3：GETNVSQL函数

CREATE OR REPLACE FUNCTION GETNVSQL(TABNAME         IN VARCHAR2, -- 需要进行行转列操作的表名；
       GROUP_COL      IN VARCHAR2, -- 查询结果要按某列或某些列分组的字段名；
       COLUMN_COL    IN VARCHAR2, -- 要从行转成列的字段；
       VALUE_COL       IN VARCHAR2, -- 需要聚合的值字段；
       AGGREGATE_FUNC  IN VARCHAR2 DEFAULT 'max', -- 选用的聚合函数，可选；
       CONDITIONS     IN VARCHAR2 DEFAULT ' 1 = 1', -- 条件
       COLORDER       IN VARCHAR2 DEFAULT NULL, -- 行转列后列的排序，可选；
       ROWORDER      IN VARCHAR2 DEFAULT NULL, -- 行转列后记录的排序，可选；
       WHEN_VALUE_NULL IN VARCHAR2 DEFAULT NULL) -- 空值处理，可选。
  RETURN VARCHAR2 AS
  SQLSTR   VARCHAR2(32767) := 'SELECT ' || GROUP_COL || ' ';
  C1       SYS_REFCURSOR;
  ValueStr   VARCHAR2(100);
BEGIN
  OPEN C1 FOR 'SELECT distinct ' || COLUMN_COL || ' FROM ' || TABNAME || ' WHERE ' || CONDITIONS || CASE WHEN COLORDER IS NOT NULL THEN ' ORDER BY ' || COLORDER END;
  LOOP
    FETCH C1
      INTO ValueStr;
    EXIT WHEN C1%NOTFOUND;
    SQLSTR := SQLSTR || CHR(10) || ',' || CASE WHEN WHEN_VALUE_NULL IS NOT NULL THEN 'nvl(' END || AGGREGATE_FUNC || '(decode(to_char(' || COLUMN_COL || '),''' || ValueStr || ''',' || VALUE_COL || '))' || CASE WHEN WHEN_VALUE_NULL IS NOT NULL THEN CHR(44) || WHEN_VALUE_NULL || CHR(41) END || '"' || ValueStr || '"';
  END LOOP;
  CLOSE C1;
  SQLSTR := SQLSTR || ' FROM ' || TABNAME || ' WHERE ' || CONDITIONS || ' GROUP BY ' || GROUP_COL || CASE WHEN ROWORDER IS NOT NULL THEN ' ORDER BY ' || ROWORDER END;
  RETURN(SQLSTR);
END GETNVSQL;

 

用以下代码对GETNVSQL函数进行测试：

SELECT getnvsql('Ts', 'group_col', 'column_col','value_col', 'sum', '1=1', 'column_col', 'group_col', '0.0') FROM dual

得到GETNVSQL函数生成的代码同代码1一致，完成了生成标签和统计值的功能。

5.2 Ref Cursor

Ref Cursor提供了在函数间传递游标的可能性。对Ref Cursor的使用一般按Open、Fetch和Close三个步骤进行。

在函数中可以利用代码3可以动态生成代码1，可以为代码1利用Open生成Ref Cursor。但是这个RefCursor如何能在SQL中直接使用呢？又在什么时候执行Close以释放Ref Cursor占用的资源呢？经过多种尝试，也没有找到在SQL中直接使用Ref Cursor的办法。

5.3 DBMS_SQL

再看DBMS_SQL。DBMS_SQL包提供了动态定义Cursor、动态生成Cursor、动态进行变量绑定等功能，使用方法更像是用C或Java等高级语言操作数据库游标的方式。DBMS_SQL包中所用的Cursor更像是一个文件指针，其类型是int型的。其使用一般按Open、Parse、Describe_Columns、Define_Columns,Fetch_Rows和Close等步骤进行。DBMS_SQL给了开发者更多的自由来操纵Cursor。

然而，这里的Cursor也无法直接提供给SQL使得。

要想把Cursor提供给SQL象TABLE那样使用，只能再经过包装。

6.TABLE函数

Oracle提供了TABLE函数，可以接收自定义数组参数，返回值可以被视为表或视图一样进行访问。

使用TABLE函数一般按以下步骤进行：

步骤一：定义行数据类型；

步骤二：定义行数据类型的表类型；

步骤三：生成表类型的数据；

步骤四：在SQL中FROM子句中使用TABLE(表类型数据)进行调用。

其中步骤一、二决定了步骤三、四的设计，是解决问题的关键。设计什么样的行数据类型合适呢？

6.1类型定义

我们可以按Tt的结构来设计行数据类型。但带来的问题，每一个种应用有不同的列标签，我们不可能穷举所有可能的结构（见4.VIEW中的讨论）。如何能定义一种可以包含这些可能的结构的结构呢？这样的结构是否存在呢？

我们先来分析一下行（表中的一条记录）的本质。表中的一行是表结构类型的一个具体实例，也可以解释为表结构类型的一个赋了值结构体变量，对行中列值的引用是通过列名来进行的。在这种意义上，我们可以认为表中的一行是一个名值对的列表。由此可进行如下定义：

名值对:=<名|值>                                                 (1)

行:=名值对1…,名值对i…,名值对N                                  (2)

根据此定义，我们可以定义行数据类型是一个字符串，字符串中按名值对的形式存放数据。

由此可进行行数据和表数据类型定义，见代码4。

代码4：类型定义

CREATE OR REPLACE TYPE nvs_row AS OBJECT(
  nvs VARCHAR2(32767)
);
CREATE OR REPLACE TYPE nvs_tab AS TABLE OF nvs_row;

 

其中32767是varchar2类型变量的长度最大值。

以下是一行数据的例子：

,FGROUP_COL1|Commercial,F121|200,F125|500

至于从这样的字符串中取出名对应的值，就是件纯技术上的事情了，实现参见代码5。

代码5：GETNV函数

CREATE OR REPLACE FUNCTION GETNV(PROPERTIES    IN VARCHAR2,
                                 PROPERTY_NAME IN VARCHAR2)
  RETURN VARCHAR2 AS
  
  RESULT         VARCHAR2(100) := '0.0';
  NAMELOCAL      VARCHAR2(100) := 'F' || PROPERTY_NAME || '|';
  POSBEGIN       INT := INSTR(PROPERTIES, NAMELOCAL);
  POSEND         INT := 0;
  LENGTHPROPERTY INT := 0;
  VALUEBEGIN     INT := 0;
BEGIN
  IF POSBEGIN > 1 THEN
    BEGIN
      VALUEBEGIN := POSBEGIN + LENGTH(NAMELOCAL);
      POSEND     := INSTR(PROPERTIES, ',F', VALUEBEGIN);
      IF POSEND = 0 THEN
        POSEND := LENGTH(PROPERTIES);
      END IF;
      LENGTHPROPERTY := POSEND - VALUEBEGIN;
    
      RESULT := SUBSTR(PROPERTIES, VALUEBEGIN, LENGTHPROPERTY);
    END;
  END IF;
  RETURN(RESULT);
END GETNV;

 

6.2生成自定义表

选择使用DBMS_SQL包编写生成自定义表的函数，原因如下：

原因一：代码3所生成SQL语句执行的结果集字段名不确定；

原因二：结果集字段个数不确定；

原因三：DBMS_SQL包为探索和访问动态游标提供了可能性。

根据SQL语句生成自定义表的函数实现，见代码6。

为了使用上的方便，编写GETNVST函数作为开给SQL的最终接口。在GETNVST函数中依次调用GETNVSQL函数和GETNVSTA函数，并返回自定义表，函数实现见代码7。

7.完整解决方案

综合以上讨论，完整的解决方案包含以下五个构件：

构件一：NVS_ROW和NVS_TAB类型定义。见代码4。

构件二：主控函数GETNVST。见代码7。

构件三：动态生成SQL函数GETNVSQL。见代码3。

代码6：GETNVSTA函数

CREATE OR REPLACE FUNCTION GETNVSTA(SQLSTR IN VARCHAR2) RETURN NVS_TAB AS
  NVS       NVS_TAB := NVS_TAB();
  L_CURSOR    INTEGER := DBMS_SQL.OPEN_CURSOR;
  L_VALUE     VARCHAR2(4000) := 'null';  L_STATUS    INTEGER;
  L_COLCOUNTS INTEGER;               L_DESCTBL   DBMS_SQL.DESC_TAB;
  ROWSTR      VARCHAR2(32766);      I           INTEGER := 0;
BEGIN
  --分析sql
  DBMS_SQL.PARSE(L_CURSOR, SQLSTR, DBMS_SQL.NATIVE);
  --获取列数
  DBMS_SQL.DESCRIBE_COLUMNS(L_CURSOR, L_COLCOUNTS, L_DESCTBL);
  --对每一列设置
  FOR I IN 1 .. L_COLCOUNTS LOOP
    DBMS_SQL.DEFINE_COLUMN(L_CURSOR, I, L_VALUE, 4000);
  END LOOP;
  L_STATUS := DBMS_SQL.EXECUTE(L_CURSOR);
  WHILE (DBMS_SQL.FETCH_ROWS(L_CURSOR) > 0) LOOP
    NVS.EXTEND;
    ROWSTR := '';
    FOR I IN 1 .. L_COLCOUNTS LOOP
      --依次取值
      DBMS_SQL.COLUMN_VALUE(L_CURSOR, I, L_VALUE);
      ROWSTR := ROWSTR || ',F' || L_DESCTBL(I).COL_NAME || '|' || L_VALUE;
    END LOOP;
    NVS(NVS.COUNT) := nvs_row(ROWSTR);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(L_CURSOR);
  RETURN(NVS);
END GETNVSTA;

 

代码7：GETNVST函数

CREATE OR REPLACE FUNCTION GETNVST(TABNAME IN VARCHAR2, GROUP_COL IN VARCHAR2, 
       COLUMN_COL IN VARCHAR2, VALUE_COL IN VARCHAR2,
	AGGREGATE_FUNC  IN VARCHAR2 DEFAULT 'max',
	CONDITIONS IN VARCHAR2 DEFAULT ' 1 = 1', COLORDER IN VARCHAR2 DEFAULT NULL, 
       ROWORDER IN VARCHAR2 DEFAULT NULL, WHEN_VALUE_NULL IN VARCHAR2 DEFAULT NULL)  RETURN NVS_TAB AS
  QLSTR VARCHAR2(32767) := ' ';
BEGIN
  SQLSTR := GETNVSQL(TABNAME, GROUP_COL, COLUMN_COL, VALUE_COL, AGGREGATE_FUNC, CONDITIONS, COLORDER, ROWORDER, WHEN_VALUE_NULL);
  RETURN(GETNVSTA(SQLSTR));
END GETNVST;

 

构件四：根据SQL生成自定义表函数GETNVSTA。见代码6。

构件五：取名值函数GETNV。见代码5。

构件关系图见图1。

图1：构件关系图

 

至此开篇所列行列转换数据透视的需求问题得以解决，应用示例代码参见代码 8 。

代码8：应用示例

SELECT getnv(nvs, 'GROUP_COL') "业务条线"
       , getnv(nvs,'E1.内部欺诈') + getnv(nvs,'E2.外部欺诈') "欺诈" 
       , getnv(nvs,'E6.信息科技系统事件') "系统"
FROM table(getnvst('Ts', 'group_col', 'column_col','value_col', 'sum', '1=1', 'column_col', 'group_col', '0.0'))

 

8.讨论

该解决方案存在以下限制：

限制一：无法处理column_col或value_col值中含有字符’|’的情况。

限制二：GETNVST参数中tabname不接受会话级临时表名。

限制三：生成的动态SQL存储在VARCHAR2字符串变量中，而其最大长度为32767；NVS_ROW中的NVS也定义为VARCHAR2，同样受32767的长度限制。

限制四：无法支持从外VALUE_COL，或对一个VALUE_COL同时使用多个聚合函数。变通的方法是多次调用生成多个临时表，但这有性能上的损耗。

该解决方案可以有以下扩展应用：

扩展应用一：五个构件中除GETNVST外都可单独使用。互相之间不存在较依赖关系。

扩展应用二：需求和例子中只给出了一个GROUP_COL的例子。实际上，该解决方案支持使用多个GROUP_COL，只需要在传递参数时将多个GROUP_COL之间用逗号分隔即可。

扩展应用三：需求和例子中只给出一个TABNAME的例子。实际上，支持使用多个TABNAME。注意两点：一是在传递TABNAME参数时用类似于’tabname1,tabname2’的形式；二是在Conditions中使用正确的连接方式。

为了不使代码占太多的篇幅，在代码中省略了很多注释和规范性的空行，可能造成阅读上的很多不便。

代码3的函数接口定义和实现部分参照了网上的内容，由于转载次数过多，最初出处已不能明确标明。