用过程语言实现group by cube的代码挑战赛

nyfor

都研究这么深了，赶紧做成一个实用的可以从oracle 调用的外部C例程吧。

〇〇

现在这个代码的局限性还非常多
1.只适合4列数值型，其中第一列取值0<=c1<16,后3列取值0<=cx<128(或许可以扩大到<256)
2.申请内存或者太费，或者如果一次申请不够用，由于边读数边汇总，可能反复realloc开销很大
3.如果推广到n列，map的key类型不能用int，效率随key长度成反比
4.代码写死了，如果推广到n列，要有自动生成代码的技术
5.把结果写回数据库，目前是最慢的，主要是NULL的处理在oci结构indp上不成功，导致insert要加decode判断
6.求和只能处理整数，结果不能大于int64,（目前c速度快的原因可能就是在这里,oracle的number精度太高）
7.全部运算在内存，如果记录很多，总有会放不下的
8.从数据库读入，现在要从字符串转成整数
9.多层累加，新gid的数据从哪个旧gid生成最好还不知道
10.是独立可执行文件，要变成oracle能调用的dll还有不可预知的难点

〇〇

用indp=-1设置NULL标志成功,写出表节省了2秒
D:\app\instantclient10_1>..\mapc3 LT/LT@10.6.132.43/orcl query="select c1,c2,c3,c4,v from t" file=NUL

       0 rows exported at 2010-03-14 13:25:36
recn=0
  500000 rows exported at 2010-03-14 13:25:38
recn=500000
1000000 rows exported at 2010-03-14 13:25:39
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存3369ms
4,5,67,114,152760
申请map内存0ms
申请一层内存16ms
新建一层节点515ms,recn=1000000,每毫秒读1941行,每毫秒写1941节点
申请后面内存15ms,1000000
新建后面节点1451ms,recn=2299600,每毫秒读689行key,每毫秒写895节点
0,first=2,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1002132
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1049901
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284775
7,first=707F7F7F,renc=2284669
8,first=8F000001,renc=1147278
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284776
13,first=DF7F007F,renc=2299367
14,first=EF7F7F00,renc=2299486
15,first=FF7F7F7F,renc=2284670

Total number of rows to be inserted is 1000000
插入结果表time=3884

Total number of rows to be inserted is 1299600
插入结果表time=4805
释放所有动态内存0ms,2299600
释放所有map内存202ms,2299600
总时间11185ms,2299600
3步（读入+运算+写出）总时间14554ms

Exiting with SUCCESS status 0

SQL> truncate table yourcube;

表被截断。

SQL> select count(*) ,gid from yourcube where c4 is null group by gid order by gid;

  COUNT(*)        GID
---------- ----------
     76923          0
    103887          1
       873          2
       873          3
      1071          4
      1071          5
         9          6
         9          7
     11543          8
     11543          9
        97         10
        97         11
       119         12
       119         13
         1         14
         1         15

已选择16行。

SQL> select count(*) from (select * from yourcube minus select * from tcube);

  COUNT(*)
----------
         0

SQL> select count(*) from (select * from tcube minus select * from yourcube);

  COUNT(*)
----------
         0

〇〇

第一次发现读内存越界
也会出错
比mapc.cpp修改

/*
text *sqlstmt = (text *)"INSERT INTO yourcube (c1,c2,c3,c4,cv,sv,gid) \
values (decode(:c1,15,NULL,:c1),decode(:c2,127,NULL,:c2),decode(:c3,127,NULL,:c3),\
decode(:c4,127,NULL,:c4),:cv,mod(:sv+4294967296,4294967296)+:sv1*4294967296,:gid)";
*/

text *sqlstmt = (text *)"INSERT INTO yourcube (c1,c2,c3,c4,cv,sv,gid) \
values (:c1,:c2,:c3,:c4,:cv,mod(:sv+4294967296,4294967296)+:sv1*4294967296,:gid)";


flen[rowindex]=len;
//indp[rowindex]=0;
//printf("%d,",indp[rowindex]);
//if((colindex==0 && (*((int *)arr+rowindex)==15))||((colindex>=1 && colindex <=3)&&(*((int *)arr+rowindex)==127)))
//if(colindex==0)
//indp[rowindex]=(sb2)-1;



flen[rowindex]=len;
indp[rowindex]=0;
//printf("%d,",indp[rowindex]);
//printf("%d/%d/%p/%d\n",colindex,rowindex,(char *)arr+rowindex,*((char *)arr+rowindex));
if((colindex==0 && (*((char *)arr+rowindex)==15))||((colindex>=1 && colindex <=3)&&(*((char *)arr+rowindex)==127)))
//if(colindex==0)
indp[rowindex]=(sb2)-1;

〇〇

读入原始表由字符串类型(char *)改为整型（char /int),节省1秒

D:\app\instantclient10_1>..\mapc4 LT/LT@10.6.132.43/orcl query="select c1,c2,c3,c4,v from t" file=NUL
0,1     1,1     2,1     3,1     4,4
       0 rows exported at 2010-03-14 14:49:28
recn=0
  500000 rows exported at 2010-03-14 14:49:29
recn=500000
1000000 rows exported at 2010-03-14 14:49:30
         output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存1965ms
4,5,67,114,152760
申请map内存0ms
申请一层内存16ms
新建一层节点483ms,recn=1000000,每毫秒读2070行,每毫秒写2070节点
申请后面内存16ms,1000000
新建后面节点1342ms,recn=2299600,每毫秒读745行key,每毫秒写968节点
0,first=5040302,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1002132
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1049901
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284775
7,first=707F7F7F,renc=2284669
8,first=8F000001,renc=1147278
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284776
13,first=DF7F007F,renc=2299367
14,first=EF7F7F00,renc=2299486
15,first=FF7F7F7F,renc=2284670

Total number of rows to be inserted is 1000000
插入结果表time=3478

Total number of rows to be inserted is 1299600
插入结果表time=5616
释放所有动态内存0ms,2299600
释放所有map内存202ms,2299600
总时间11497ms,2299600
3步（读入+运算+写出）总时间13462ms

Exiting with SUCCESS status 0
        switch(nextcol->dbtype)
        {
            case NUMBER_TYPE:
               if (nextcol->precision)
                   nextcol->dsize=nextcol->precision;
               else
                   nextcol->dsize=40;
               nextcol->dbtype=(fixlen?SQLT_AVC:STRING_TYPE);//INT_TYPE);//
               if(fpctl != NULL)
               {
                  if (fixlen)
                      fprintf(fpctl,"  %s POSITION(%d:%d) CHAR(%d)",
                           nextcol->buf,totallen, totallen + nextcol->dsize-1, nextcol->dsize);
                  else
                      fprintf(fpctl,"  %s CHAR(%d)", nextcol->buf,nextcol->dsize);
               }
               nextcol->dbtype=INT_TYPE;
               if(col<=3){nextcol->dsize=1;}//c1 c2 c3 c4
               else{nextcol->dsize=sizeof(eword);}//v
               break;

                   if(1==2) //read string
                   switch(c)
                   {
                           case 0:c1[rn]=(char)atoi((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 1:c2[rn]=(char)atoi((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 2:c3[rn]=(char)atoi((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 3:c4[rn]=(char)atoi((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));
                           /*if(*(cols[c]->col_retlen+r)==0)c4[rn]=(char)127;*/break;
                           case 4:v[rn]=atoi(((const char *)(cols[c]->colbuf+(r* cols[c]->dsize))));break;
                          
                    }
                    else
                   switch(c)//read int
                   {
                           case 0:c1[rn]=(char)*((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 1:c2[rn]=(char)*((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 2:c3[rn]=(char)*((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));break;
                           case 3:c4[rn]=(char)*((const char *)(cols[c]->colbuf+(r* cols[c]->dsize)));
                           /*if(*(cols[c]->col_retlen+r)==0)c4[rn]=(char)127;*/break;
                           case 4:v[rn]=*((int *)((const char *)(cols[c]->colbuf+(r* cols[c]->dsize))));break;
                          
                    }

〇〇

mapc4在aix上编译不成功，把oci调用全删除后变成mapc6.cpp
与上一个能编译的版本map8a比较
ibm01:/tmp$time ./mapc6
9,97,119,114,11843118
申请map内存66335ms
申请一层内存19976ms
新建一层节点459249ms,recn=1000000,每毫秒读2行,每毫秒写2节点
申请后面内存19971ms,1000000
新建后面节点1381282ms,recn=2299600,每毫秒读0行key,每毫秒写0节点
0,first=0,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1002132
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1049901
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284775
7,first=707F7F7F,renc=2284669
8,first=8F000001,renc=1147278
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284776
13,first=DF7F007F,renc=2299367
14,first=EF7F7F00,renc=2299486
15,first=FF7F7F7F,renc=2284670
释放所有动态内存0ms,2299600
释放所有map内存1688312ms,2299600
总时间3635125ms,2299600
3步（读入+运算+写出）总时间3635125ms

real    0m3.72s
user    0m3.63s
sys     0m0.00s

9,97,119,114,11843118
申请源表内存0ms
填充源表数据79818ms,recn=1000000,每毫秒12行
申请一层内存9987ms
新建一层节点408969ms,recn=1000000,每毫秒读2行,每毫秒写2节点
申请后面内存0ms,1000000
新建后面节点1601817ms,recn=2299600,每毫秒读0行key,每毫秒写0节点
0,first=0,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1000002
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1000001
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284774
7,first=707F7F7F,renc=2284669
8,first=8F000000,renc=1000000
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284775
13,first=DF7F007F,renc=2299366
14,first=EF7F7F00,renc=2299485
15,first=FF7F7F7F,renc=2299599
汇总后面节点109657ms,recn=2299600,每毫秒读9行，每毫秒写11行
释放所有动态内存0ms,2299600
0,0
1,1000000
2,1000000
3,103887
4,1000000
5,103887
6,122094
7,873
8,1000000
9,103887
10,99522
11,873
12,122094
13,1071
14,1026
15,873
释放所有map内存2474610ms,2299600
总时间4684858ms,2299600

real    0m8.30s
user    0m8.16s
sys     0m0.00s
计算时间从2209->1946

在笔记本运行时间
D:\lt\dl>cl /O2 /EHsc mapc5.cpp /link oci.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 13.10.3077 for 80x86
Copyright (C) Microsoft Corporation 1984-2002. All rights reserved.

mapc5.cpp
Microsoft (R) Incremental Linker Version 7.10.3077
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:mapc5.exe
oci.lib
mapc5.obj
LINK : warning LNK4089: all references to 'OCI.dll' discarded by /OPT:REF

D:\lt\dl>timer
'timer' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

D:\lt\dl>mapc5
9,97,119,114,11843118
申请map内存62ms
申请一层内存16ms
新建一层节点422ms,recn=1000000,每毫秒读2369行,每毫秒写2369节点
申请后面内存0ms,1000000
新建后面节点1171ms,recn=2299600,每毫秒读853行key,每毫秒写1109节点
0,first=3D454D41,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1002132
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1049901
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284775
7,first=707F7F7F,renc=2284669
8,first=8F000001,renc=1147278
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284776
13,first=DF7F007F,renc=2299367
14,first=EF7F7F00,renc=2299486
15,first=FF7F7F7F,renc=2284670
释放所有动态内存0ms,2299600
释放所有map内存468ms,2299600
总时间2171ms,2299600
3步（读入+运算+写出）总时间2171ms

〇〇

newkid的代码修改版
对9,97,119,114
42秒

level 1:1134
level 2:2087
level 3:273
output:658

PL/SQL 过程已成功完成。

已用时间:  00: 00: 42.07

[ 本帖最后由 〇〇 于 2010-3-16 14:41 编辑 ]

〇〇

看到plsql的nest table用hash表很快，也想在c++里用，结果很糟糕（需要vc2003编译）

D:\app>mapc6h
9,97,119,114,11843118
申请map内存46ms
申请一层内存32ms
新建一层节点624ms,recn=1000000,每毫秒读1602行,每毫秒写1602节点
新建一层hashmap节点1529ms,recn=1000000,每毫秒读654行,每毫秒写654节点
申请后面内存31ms,1000000
新建后面节点1576ms,recn=2299600,每毫秒读634行key,每毫秒写824节点
0,first=6D6F435C,renc=0
1,first=1000007F,renc=1000003
2,first=20007F00,renc=1002132
3,first=30007F7F,renc=2260123
4,first=407F0000,renc=1049901
5,first=507F007F,renc=2260125
6,first=607F7F00,renc=2284775
7,first=707F7F7F,renc=2284669
8,first=8F000001,renc=1147278
9,first=9F00007F,renc=2260124
10,first=AF007F00,renc=2273610
11,first=BF007F7F,renc=2284668
12,first=CF7F0000,renc=2284776
13,first=DF7F007F,renc=2299367
14,first=EF7F7F00,renc=2299486
15,first=FF7F7F7F,renc=2284670
释放所有动态内存15ms,2299600
释放所有map内存7488ms,2299600
总时间11372ms,2299600
3步（读入+运算+写出）总时间25849ms

newkid

应OO要求贴出一个INDEX BY二维数组的版本。一个COLLECTION的元素可以是另一个COLLECTION, 所以实现多维数组完全没有问题。

DECLARE
   TYPE t_num IS TABLE OF NUMBER INDEX BY VARCHAR2(42);
   TYPE t_nums IS TABLE OF t_num INDEX BY PLS_INTEGER;
   lv_rownum t_nums;
   
   TYPE t_row IS TABLE OF yourcube%ROWTYPE;
   TYPE t_rows IS TABLE OF t_row INDEX BY PLS_INTEGER;
   lv_result t_rows;
      
   cursor c is SELECT * FROM T;
   
   TYPE t_row2 IS TABLE OF c%ROWTYPE;
   lv_row t_row2;
   
   lv_cnt NUMBER;
   lv_cnt2 NUMBER;
   
   PROCEDURE agg (p_c1 IN NUMBER,p_c2 IN NUMBER,p_c3 IN NUMBER, p_c4 IN NUMBER, p_v IN NUMBER, p_c IN NUMBER, p_gid IN NUMBER)
   IS
      lv_idx NUMBER;
      lv_key VARCHAR2(42) := LPAD(NVL(TO_CHAR(p_c1),' '),10)||LPAD(NVL(TO_CHAR(p_c2),' '),10)||LPAD(NVL(TO_CHAR(p_c3),' '),10)||LPAD(NVL(TO_CHAR(p_c4),' '),10)||LPAD(p_gid,2);
   BEGIN
       BEGIN
          lv_idx := lv_rownum(p_gid)(lv_key);
          IF p_v IS NOT NULL THEN
             lv_result(p_gid)(lv_idx).sv := NVL(lv_result(p_gid)(lv_idx).sv,0) + p_v;
          END IF;

       EXCEPTION
          WHEN NO_DATA_FOUND THEN
               BEGIN
                  lv_idx := lv_rownum(p_gid).COUNT+1;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       lv_idx := 1;
               END;
               IF lv_idx>lv_result(p_gid).COUNT THEN
                  lv_result(p_gid).EXTEND(10000);
               END IF;
               lv_rownum(p_gid)(lv_key) := lv_idx;
               lv_result(p_gid)(lv_idx).c1 := p_c1;
               lv_result(p_gid)(lv_idx).c2 := p_c2;
               lv_result(p_gid)(lv_idx).c3 := p_c3;
               lv_result(p_gid)(lv_idx).c4 := p_c4;
               lv_result(p_gid)(lv_idx).sv := p_v;
               lv_result(p_gid)(lv_idx).gid := p_gid;
               lv_result(p_gid)(lv_idx).cv := 0;
       END;
       lv_result(p_gid)(lv_idx).cv := lv_result(p_gid)(lv_idx).cv + p_c;
      
   END agg;
BEGIN

    FOR i IN 0..15 LOOP
        lv_result(i) := t_row();
    END LOOP;
   
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO lv_row LIMIT 1000;
        FOR I IN 1..lv_row.COUNT LOOP
            agg(lv_row(I).c1,lv_row(I).c2,lv_row(I).c3,lv_row(I).c4,lv_row(I).v,CASE WHEN lv_row(I).v IS NULL THEN 0 ELSE 1 END,0);
        END LOOP;
        EXIT WHEN C%NOTFOUND;
    END LOOP;
    CLOSE C;
   
    FOR I IN 1..lv_rownum(0).COUNT LOOP
            agg(lv_result(0)(I).c1,lv_result(0)(I).c2,lv_result(0)(I).c3,NULL              ,lv_result(0)(I).sv,lv_result(0)(I).cv,1);
            agg(lv_result(0)(I).c1,lv_result(0)(I).c2,NULL              ,lv_result(0)(I).c4,lv_result(0)(I).sv,lv_result(0)(I).cv,2);
            agg(lv_result(0)(I).c1,NULL              ,lv_result(0)(I).c3,lv_result(0)(I).c4,lv_result(0)(I).sv,lv_result(0)(I).cv,4);
            agg(NULL              ,lv_result(0)(I).c2,lv_result(0)(I).c3,lv_result(0)(I).c4,lv_result(0)(I).sv,lv_result(0)(I).cv,8);
    END LOOP;
   
    FOR I IN 1..lv_rownum(1).COUNT LOOP
        agg(lv_result(1)(I).c1,lv_result(1)(I).c2,NULL              ,NULL           ,lv_result(1)(I).sv,lv_result(1)(I).cv,3);
        agg(lv_result(1)(I).c1,NULL              ,lv_result(1)(I).c3,NULL           ,lv_result(1)(I).sv,lv_result(1)(I).cv,5);
        agg(NULL              ,lv_result(1)(I).c2,lv_result(1)(I).c3,NULL           ,lv_result(1)(I).sv,lv_result(1)(I).cv,9);
    END LOOP;
   
    FOR I IN 1..lv_rownum(2).COUNT LOOP
        agg(NULL              ,lv_result(2)(I).c2,NULL           ,lv_result(2)(I).c4,lv_result(2)(I).sv,lv_result(2)(I).cv,10);
    END LOOP;
   
    FOR I IN 1..lv_rownum(4).COUNT LOOP
        agg(lv_result(4)(I).c1,NULL           ,NULL              ,lv_result(4)(I).c4,lv_result(4)(I).sv,lv_result(4)(I).cv,6);
        agg(NULL              ,NULL           ,lv_result(4)(I).c3,lv_result(4)(I).c4,lv_result(4)(I).sv,lv_result(4)(I).cv,12);
    END LOOP;

    FOR I IN 1..lv_rownum(3).COUNT LOOP
        agg(lv_result(3)(I).c1,NULL              ,NULL           ,NULL           ,lv_result(3)(I).sv,lv_result(3)(I).cv,7);
        agg(NULL              ,lv_result(3)(I).c2,NULL           ,NULL           ,lv_result(3)(I).sv,lv_result(3)(I).cv,11);
        agg(NULL              ,NULL              ,NULL           ,NULL           ,lv_result(3)(I).sv,lv_result(3)(I).cv,15);
    END LOOP;

    FOR I IN 1..lv_rownum(5).COUNT LOOP
        agg(NULL           ,NULL           ,lv_result(5)(I).c3,NULL           ,lv_result(5)(I).sv,lv_result(5)(I).cv,13);
    END LOOP;

    FOR I IN 1..lv_rownum(6).COUNT LOOP
        agg(NULL           ,NULL           ,NULL           ,lv_result(6)(I).c4,lv_result(6)(I).sv,lv_result(6)(I).cv,14);
    END LOOP;
   
    FOR I IN 0..15 LOOP
        FORALL J IN 1..lv_rownum(I).COUNT
            INSERT INTO yourcube VALUES lv_result(i)(j);
    END LOOP;

END;
/

wabjtam123

NEWKID兄你的开发水平达到如此精深的地步！
真的可以考虑自己写点啥的，用来做共通软件来进行商业运营啊