队列转换的一个解决思路，欢迎提意见

行列转换的一个解决思路，欢迎提意见
    工作中常需要把数据库中某数据按照某列转成行。而展现的工具可能又只支持1句SQL完成，所以就考虑写视图解决。但是写此类SQL既繁琐，又容易出错，所以想写一个自动生成的脚本处理。
解决的问题，例：
表结构为：tab1
  orgid(机构) gender(性别) ,itemid（项目） ，val（值）
    0322 ,0,A,1
   0322 ,0,B,4
   0321 ,1,C,5
   0324 ,1,C,3
展现效果：
0322,0,1,4,0
0321,0,0,0,5
0324,0,0,0,3
解决方法：
配置文件: SQL.txt
 

       V_tab1.tab1.orgid,gender.VAL.itemid.A,B,C  

 
说明：
以"."分割列，分别是：视图名.表名,查询项.分组项.分组值(多值以","分割)

脚本: sql.sh

awk -F'.' '{     test = $6    alen=split(test,cols,",");    for( idx=1;idx<=alen;idx++)    {sqlalias[idx]="v" idx "_" $4    }         print  " DROP VIEW " $1 "  ; "    print  " CREATE VIEW " $1 "  \n AS "    print  "SELECT " $3 ","    for( idx=1;idx<=alen;idx++)    {    if( idx == alen )    print " \t sum(v" idx "_" $4 ") AS v" idx "_" $4     else    print " \t sum(v" idx "_" $4 ") AS v" idx "_" $4 " ,"    }        print "FROM ("         for( idx=1;idx<=alen;idx++)    {    csql = "\tSELECT " $3 "," "\n \t \t "    if( idx != 1 ) csql = "\tUNION \n" csql     for( i=1;i<=alen;i++)    {    if( i == idx ) csql = csql $4     else csql = csql " 0 "    csql = csql " AS " sqlalias[i]    if( i != (alen) ) csql = csql " , "    }      csql = csql " \tFROM " $2 " WHERE " $5 "='\''" cols[idx] "'\''"    print  csql             }    print " ) aaa "    print " GROUP BY "  $3 ";"       }' SQL.txt  > test.sql

  
生成的代码:test.sql

 DROP VIEW v_tab1  ;  CREATE VIEW v_tab1   AS SELECT orgid,gender,  sum(v1_VAL) AS v1_VAL ,  sum(v2_VAL) AS v2_VAL ,  sum(v3_VAL) AS v3_VALFROM (SELECT orgid,gender,   VAL AS v1_VAL ,  0  AS v2_VAL ,  0  AS v3_VAL FROM tab1 WHERE itemid='A'UNION SELECT orgid,gender,    0  AS v1_VAL , VAL AS v2_VAL ,  0  AS v3_VAL FROM tab1 WHERE itemid='B'UNION SELECT orgid,gender,    0  AS v1_VAL ,  0  AS v2_VAL , VAL AS v3_VAL FROM tab1 WHERE itemid='C' ) aaa  GROUP BY orgid,gender;

 
使用方法：

     select * from v_tab1  

  
适用范围：
  报表，查询。
  通过该方法，在没有报表工具的情况下解决了行列转换问题。
已知缺点：
   1.由于不熟悉perl，简单解决方案，所以觉得用AWK处理，这样处理导致配置文件配置不灵活
   2.在数据量过大的时候，这种查询效率也不会高，不过既然要行列转换，性能总是要有牺牲。
     在db2中，没有oracle的decode类似的函数（也许有也可能，我没仔细去找），所以用union，不过我猜想即使用decode,效率也是差不多，当然仅仅是猜想而已，没在oralce下测试过。union实现要比左外连接效率要高很多，这方面测试过。
    如果确实有更高性能要求，把创建试图语句改成创建表，每天晚上执行生成的insert语句即可。（BI项目的数据，通常至少延迟1天，所以可在晚上跑数据）


补充说明：
  我是采用union实现的，但其实也可生成case when,decode(oralce)方式实现，但其实这与本主题并不矛盾。本方法不过是提出一个通过脚本生成相关SQL的思路，重点不在SQL技巧。
  至于效率问题，如果很效率问题是瓶颈，可以改动AWK生成creat table ,insert语句，然后定时抽取。
我前面做的那个项目，就是通过类似手段解决问题的。简单描述：通过某种定制手段，直接生成SQL，实行数据仓库的清洗工作。这也是我们几个数据仓库的项目经验总结。经证实，这种方法确实切实有效，减少了大家相当多的工作量。前面一个数据仓库投入11个人左右，而这个项目我们只投入了7人，除项目经理外还有2人很少编码（做业务分析去了）。但是项目中，我们大量采用类似方法实现，带来了相当大效果。
  后面这个项目数据量虽然比前面的小，但是业务其实更复杂。通过大量使用脚本，开发效率得到了大大提升。难怪以前的一个同事说：在他们现公司他们部门，所有的开发都是脚本，几乎不用脚本外的与语言。该兄弟其实可是C语言高手，现在完全投入到脚本怀抱中去了。

1 楼 lixigua 2006-10-26   竟然被转道新手贴。
刚搜乐下行列转换的思路，基本上跟我做法类此，不过我提供的是自动生成创建试图的SQL。
分组达到20项的时候，你手共写代码写这试图看看，不出错才怪。
在oracle还可以用decode，db2无法用，手工写的SQL更长的了

估计我的那段AWK程序是没人看过，大约也就有相当多的人痛快的点了新手贴。
郁闷之际。


2 楼 ddd 2006-10-26   如果感觉被冤枉，可以选择投诉。

实话说，我不认为这个是新手贴（删除前就看过）。 3 楼 renyangok 2006-10-27   在牛人面前我们永远是新手 4 楼 ouspec 2006-10-27   可以申诉的，建议把帖子内容恢复出来，马上请仲裁委员会给一个判定 5 楼 lixigua 2006-10-28   自己定一下。刚补充了些说明

查看更多 下一篇