[整理学习]查询间断点系列问题

[整理学习]查询间断点系列问题

1.断点类型

1.1 查询间断点缺失数字

（如1 2 4 5 7 查询结果 3 6）

SELECT 
    ID
FROM
    (SELECT 
        id + 1 AS ID
    FROM
        table_02
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            table_02
        WHERE
            id = s.ID)
        AND ID < (SELECT 
            MAX(ID)
        FROM
            table_02)

1.2 查询间断点缺失范围

（如 1 2 3 6 7 10 查询结果4-5，8-9）
[摘自(http://www.cnblogs.com/frank-quan/p/5843149.html)

SELECT 
    id + 1 start_range,
    (SELECT 
            MIN(id) - 1
        FROM
            liubowen_01 C
        WHERE
            C.id > A.id) AS end_range  
FROM
    liubowen_01 AS A
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            liubowen_01 AS B
        WHERE
            A.id + 1 = B.id)
        AND id < (SELECT 
            MAX(id)
        FROM
            liubowen_01)

或者

SELECT
 cur + 1 AS start_range, 
 NEXT - 1 AS end_range
FROM
 (
  SELECT
   A.id AS cur,
   (
    SELECT MIN(id) FROM liubowen_01 AS B WHERE B.id > A.id
   ) AS NEXT
  FROM
   liubowen_01 AS A
 ) AS C
WHERE
 NEXT - cur > 1

1.3 查询每段连续范围

（如 1 2 3 6 7 10 查询结果1-3，6-7，10-10）
[摘自(http://www.cnblogs.com/frank-quan/p/5843149.html)

SELECT
 MIN(id) AS start_range,
 MAX(id) AS end_range
FROM
 (
  SELECT
   id,
   rn,
   id - rn AS diff
  FROM
   (
    SELECT
     id,
     @id := @id + 1 rn
    FROM
     liubowen_01,
     (
      select @id := 0
     ) as liubowen_01 
   ) AS b
 ) AS c
GROUP BY
 diff

或者(hive)
[摘自]蒋老师

select
 max(t.click_seq) click_seq
from
 (
  select
   cast(click_seq as int) as click_seq,
   cast(click_seq as int) - row_number() over(order by cast(click_seq as int)) as cnt
  from
   click
  where
   uuid = 'user_01'
 )
 t
group by
 cnt