不定区间的范围分组统计

jiqing1004

递归with

WITH T AS (
SELECT 1 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 10 N FROM DUAL UNION ALL
SELECT 11 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 20 N FROM DUAL
),v as (
select n, row_number() over(order by n) rn from t
),v1 (flag,n,rn) as (
select n,n,rn from v where rn = 1
union all
select case
         when v.n - v1.flag >= 5 then
          v.n
         else
          v1.flag
       end,
       v.n,
       v.rn
  from v, v1
where v.rn = v1.rn + 1
)
select * from v1

zjxpcyc

demonat 发表于 2013-11-19 16:13
没这么麻烦把。。。。

WITH T AS (

膜拜，大神！之前也想过找规律，但愣是没有看出来!

demonat

zjxpcyc 发表于 2013-11-19 17:15
膜拜，大神！之前也想过找规律，但愣是没有看出来!

这是错的，lz的需求不是这样的

zjxpcyc

demonat 发表于 2013-11-19 16:13
没这么麻烦把。。。。

WITH T AS (

借用你的修改了下

WITH T AS (
SELECT 1 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 10 N FROM DUAL UNION ALL
SELECT 11 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 69 N FROM DUAL UNION ALL
SELECT 20 N FROM DUAL
)
SELECT N,DENSE_RANK() over(order by FLOOR(n/6) + 1) f FROM T;

hh7yx

zjxpcyc 发表于 2013-11-19 17:21
借用你的修改了下

WITH T AS (

你这个应该也不对吧，加条记录：

SELECT 6 N FROM DUAL

yhqtxy1314

WITH T AS (
SELECT 1 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 10 N FROM DUAL UNION ALL
SELECT 11 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 20 N FROM DUAL

1. 
   
2. WITH T AS (
   
3. SELECT 1 N FROM DUAL UNION ALL
   
4. SELECT 3 N FROM DUAL UNION ALL
   
5. SELECT 4 N FROM DUAL UNION ALL
   
6.   SELECT 5 N FROM DUAL UNION ALL
   
7. SELECT 7 N FROM DUAL UNION ALL
   
8. SELECT 10 N FROM DUAL UNION ALL
   
9. SELECT 11 N FROM DUAL UNION ALL
   
10. SELECT 12 N FROM DUAL UNION ALL
    
11. SELECT 12 N FROM DUAL UNION ALL
    
12. SELECT 19 N FROM DUAL UNION ALL
    
13. SELECT 20 N FROM DUAL UNION ALL
    
14.   SELECT 50 N FROM DUAL UNION ALL
    
15.   SELECT 52 N FROM DUAL UNION ALL
    
16.   SELECT 53 N FROM DUAL UNION ALL
    
17.   SELECT 54 N FROM DUAL UNION ALL
    
18.   SELECT 55 N FROM DUAL UNION ALL
    
19.   SELECT 56 N FROM DUAL UNION ALL
    
20.    SELECT 51 N FROM DUAL
    
21. )
    
22. select n,ceil(n/5) from t

复制代码

zjxpcyc

hh7yx 发表于 2013-11-19 17:25
你这个应该也不对吧，加条记录：

SELECT 6 N FROM DUAL

是的，不对了。不只是6，只要是6的倍数且每组数字非5差之内的间断的，都不正确！

hh7yx

zjxpcyc 发表于 2013-11-19 17:39
是的，不对了。不只是6，只要是6的倍数且每组数字非5差之内的间断的，都不正确！

你的model很牛逼，
sql大赛后，看了1天的语法，还是迷迷糊糊的

peter1166

yhqtxy1314 发表于 2013-11-19 17:39

这个11 就不对了。

peter1166

jiqing1004 发表于 2013-11-19 17:06
递归with

WITH T AS (

学习了，原来用递归这么简明就出来了。 谢谢 ！