求反GROUP BY的写法

〇〇

把
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)

改成
create table data as select sys_guid(),dbms_random.random(10) from dual connect by level<3e5

〇〇

SQL> create table data as select sys_guid()name,round(dbms_random.value(1,10))cnt from dual connect by level<3e5;

Table created.

Elapsed: 00:00:02.79
SQL> select count(*) from data;

  COUNT(*)
----------
    299999

Elapsed: 00:00:00.10
SQL> select sum(length(name))s from(
  2  SELECT name
  3    FROM data
  4  CONNECT BY name=PRIOR name AND LEVEL<=cnt AND PRIOR SYS_GUID() IS NOT NULL
  5  );


         S
----------
  52755648

Elapsed: 00:00:05.48
SQL> SQL> /

         S
----------
  52755648

Elapsed: 00:00:06.40
SQL> select sum(length(name))s from(
  2  SELECT name
  3    FROM data
  4        ,TABLE( CAST( MULTISET( SELECT 1 FROM DUAL CONNECT BY LEVEL <= data.cnt ) AS SYS.ODCINUMBERLIST))
  5  ) ;


         S
----------
  52755648

Elapsed: 00:00:03.93
SQL> SQL> /

         S
----------
  52755648

Elapsed: 00:00:03.97
SQL> select sum(length(name))s from(
  2  SELECT NAME, x.num AS num
  3  FROM data,
  4          xmltable('1 to xs:integer($n)' passing cnt AS "n" columns num FOR
  5                    ordinality) x
  6  );

         S
----------
  52755648

Elapsed: 00:00:16.37
SQL> /

         S
----------
  52755648

Elapsed: 00:00:16.08
SQL> with rdata (name, cnt, lv) as
  2    (
select name, cnt, 1 as lv from data
union all
  3    4    5  select name, cnt, lv + 1 as lv from rdata where lv + 1 <= cnt
  6    )
  7  select sum(length(name))s from(
  8    select * from rdata
  9  );


         S
----------
  52755648

Elapsed: 00:01:11.17
SQL> SQL> select sum(length(name))s from(
  2  select a.name from data a,
  3  (
  4  select level rn from dual connect by level <=(select max(cnt) maxlevel from data)) b
  5  where a.cnt >= rn
  6  );


         S
----------
  52755648

Elapsed: 00:00:01.51
SQL> SQL> select sum(length(name))s from(
  2  SELECT name
  3    FROM data
  4  MODEL
  5  PARTITION BY (name)
  6  DIMENSION BY (1 n)
  7  MEASURES (cnt)
  8  RULES
  9  ITERATE (11) UNTIL (ITERATION_NUMBER>=cnt[1]-1)
10    (
11       cnt[ITERATION_NUMBER+1]=cnt[1]
12    )
13  );

         S
----------
  52755648

Elapsed: 00:00:17.83
SQL>

guostong

射了，这贴该射

romalon

學習了

lastwinner

〇〇 发表于 2011-10-27 21:51
SQL> create table data as select sys_guid()name,round(dbms_random.value(1,10))cnt from dual connect  ...

给个结论呀！

newkid

结论就是：没有结论，和数据的特点有关。
其实xxyy的办法是最经典最容易想到的，效果也不错，其他都是新奇好玩的写法而已。

guostong

newkid 发表于 2011-10-27 12:57
结论就是：没有结论，和数据的特点有关。
其实xxyy的办法是最经典最容易想到的，效果也不错，其他都是新奇 ...

同意，其他的方法有点折腾得意思，不过我们鼓励折腾。

zwqq01

学习了！

zwqq01

newkid 发表于 2011-10-26 23:10
WITH data AS (
SELECT  'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL

AND PRIOR SYS_GUID() IS NOT NULL;
是啥意思· 为什么后面要加这句！？

liwei9902

学习了，我也来写一个

1. WITH data AS (
   
2. SELECT  'A' name, 2 cnt FROM DUAL
   
3. UNION ALL SELECT 'B', 4 FROM DUAL
   
4. UNION ALL SELECT 'C', 1 FROM DUAL
   
5. )
   
6. SELECT name,cnt
   
7.   FROM data
   
8. CONNECT BY NAME=connect_by_root(NAME) AND LEVEL<=cnt

复制代码