求反GROUP BY的写法

〇〇

newkid #10
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
SELECT name
  FROM data
MODEL
PARTITION BY (name)
DIMENSION BY (1 n)
MEASURES (cnt)
RULES
ITERATE (3e5) UNTIL (ITERATION_NUMBER>=cnt[1]-1)
  (
     cnt[ITERATION_NUMBER+1]=cnt[1]
  )
);
Elapsed: 00:00:37.75

风铃中の鬼

这个可以顶

〇〇

guostong
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
),
rdata (name, cnt, lv) as
  (
select name, cnt, 1 as lv from data
union all
select name, cnt, lv + 1 as lv from rdata where lv + 1 <= cnt
  )
select sum(length(name))s from(
  select * from rdata
);

Elapsed: 00:00:05.58

〇〇

其实hxy的可以更简明

WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
select a.name from data a,
(
select level rn from dual connect by level <=(select max(cnt) maxlevel from data)) b
where a.cnt >= rn
);

sohay

顶，这么简单的sql能玩出这么多花样！
OO性能测试是否可以说明：对于单个sql效率，connet by  > 递归with > model ?

dingjun123

不足以说明，OO下次别测试了，测试发一贴就OK啦

〇〇

dingjun123 发表于 2011-10-27 15:19
不足以说明，OO下次别测试了，测试发一贴就OK啦

其实newkid的一般就可以，没想到你的xml也这么犀利

liwei9902

这个有点意思

newkid

OO应该测试一下NAME很多，CNT不大的这种情况，比较符合实际。

〇〇

newkid 发表于 2011-10-27 21:33
OO应该测试一下NAME很多，CNT不大的这种情况，比较符合实际。

但我猜测几种方法的基本比例关系不会错