복붙노트

[SQL] 어떻게 MySQL을 순차적 번호 격차를 찾는 방법은?

SQL

어떻게 MySQL을 순차적 번호 격차를 찾는 방법은?

우리는 값을 다른 시스템에서 가져온 테이블이있는 데이터베이스가 있습니다. 자동 증가 열이있다, 그리고 중복 값이 ​​없지만, 누락 된 값이 있습니다. 예를 들어,이 쿼리를 실행 :

select count(id) from arrc_vouchers where id between 1 and 100

(100)를 반환하지만, 대신 87를 반환해야합니다. 내가 그 누락 된 번호의 값을 반환합니다 실행할 수있는 쿼리가 있습니까? 예를 들어, 기록은 ID 1-70 및 83-100 존재 수 있지만, ID의 71-82의 아무 기록도 없다. I는 창 71, 72, 73 등으로 할

이게 가능해?

해결법

  1. ==============================

    1.ConfexianMJS는 성능면에서 훨씬 더 나은 해답을 제공했다.

    ConfexianMJS는 성능면에서 훨씬 더 나은 해답을 제공했다.

    여기에 (단지 100 행에) 어떤 크기의 테이블에서 작동 버전입니다 :

    SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
  2. ==============================

    2.이것은 단지 이상의 80K 행 테이블의 격차를 찾기 위해 나를 위해 일한 :

    이것은 단지 이상의 80K 행 테이블의 격차를 찾기 위해 나를 위해 일한 :

    SELECT
 CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
 SELECT
  @rownum:=@rownum+1 AS expected,
  IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got
 FROM
  (SELECT @rownum:=0) AS a
  JOIN YourTable
  ORDER BY YourCol
 ) AS z
WHERE z.got!=0;

    결과:

    +------------------+
| missing          |
+------------------+
| 1 thru 99        |
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)

    열 순서 기대하고있어주의가 중요합니다.

    당신이 YourCol 1에서 시작되지 않는 것을 알고, 그 문제가되지 않는 경우, 당신은 대체 할 수있다

    (SELECT @rownum:=0) AS a

    와

    (SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a

    새로운 결과 :

    +------------------+
| missing          |
+------------------+
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)

    당신이 누락 된 ID를 쉘 스크립트 작업의 어떤 종류를 수행해야하는 경우, 당신은 직접 당신이 떠들썩한 파티에서 반복 할 수있는 표현을 생산하기 위해이 변형을 사용할 수 있습니다.

    SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM (  SELECT   @rownum:=@rownum+1 AS expected,   IF(@rownum=height, 0, @rownum:=height) AS got  FROM   (SELECT @rownum:=0) AS a   JOIN block   ORDER BY height  ) AS z WHERE z.got!=0;

    이 때문에 같은 출력을 생성

    $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

    그러면 각 ID에 대한 명령을 실행하는 떠들썩한 단말기의 for 루프에 복사하여 붙여 넣을 수

    for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
  echo $ID
  # fill the gaps
done

    그것은 읽기와 실행 모두의 유일한 것으로, 위와 같은 일입니다. 위의 "CONCAT"명령을 변경하여 구문은 다른 프로그래밍 언어에 대해 생성 할 수 있습니다. 아니면 심지어 SQL.

  3. ==============================

    3.트릭을 할해야 신속하고 더러운 쿼리 :

    트릭을 할해야 신속하고 더러운 쿼리 :

    SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
FROM 
 (
SELECT a1.id AS a , MIN(a2.id) AS b 
FROM arrc_vouchers  AS a1
LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id
WHERE a1.id <= 100
GROUP BY a1.id
) AS tab

WHERE 
b > a + 1

    이렇게하면 그 위에없는 ID를 가지고있는 ID를 나타내는 표를주고, 존재 next_id, 사이 ... 예컨대 얼마나 많은 누락됩니다.

     
id  next_id  missing_inbetween
 1        4                  2
68       70                  1
75       87                 11
  4. ==============================

    4.당신이 MariaDB를 사용하는 경우 시퀀스 스토리지 엔진을 사용하여 빠르게은 (800 %) 옵션이 있습니다 :

    당신이 MariaDB를 사용하는 경우 시퀀스 스토리지 엔진을 사용하여 빠르게은 (800 %) 옵션이 있습니다 :

    SELECT * FROM seq_1_to_50000 WHERE SEQ NOT IN (SELECT COL FROM TABLE);
  5. ==============================

    5.100 개 행이 임시 테이블과 값 1-100을 포함하는 하나의 열을 만듭니다.

    100 개 행이 임시 테이블과 값 1-100을 포함하는 하나의 열을 만듭니다.

    외부는 arrc_vouchers 테이블이 테이블에 가입하고 arrc_vouchers 아이디가 null의 단일 열 값을 선택합니다.

    이 블라인드를 코딩,하지만 작동합니다.

    select tempid from temptable 
left join arrc_vouchers on temptable.tempid = arrc_vouchers.id 
where arrc_vouchers.id is null
  6. ==============================

    6.것이 일부 처리를하고 몇 가지 코드 + 쿼리를 필요로하는 다른 솔루션 :

    것이 일부 처리를하고 몇 가지 코드 + 쿼리를 필요로하는 다른 솔루션 :

    select l.id lValue, c.id cValue, r.id rValue 
  from 
  arrc_vouchers l 
  right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null)
  left  join arrc_vouchers r on r.id=c.id+1
where 1=1
  and c.id > 0 
  and (l.id is null or r.id is null)
order by c.id asc;

    쿼리가 우리가하지의 MySQL의 계획에 의해 performantly 처리하는 것을 알고 있다고 모든 부속을 포함하지 않습니다.

    즉, 작은 값 (좌변) 또는 더 큰 값 (를 rvalue)을 가지고 있지 않습니다 중앙 값 (가치) 당 하나 개의 항목을 반환합니다, 즉 :

    lValue |cValue|rValue
-------+------+-------
{null} | 2    | 3      
8      | 9    | {null} 
{null} | 22   | 23     
23     | 24   | {null} 
{null} | 29   | {null} 
{null} | 33   | {null}

    자세한 내용에 가지 않고이 출력 수단을 것을 (우리는 다음 단락에서 볼 수 있습니다)

    기본적인 아이디어는 오른쪽을하는 것입니다 LEFT 우리가 값 (즉, 당 인접 신호 값을 가지고 같은 테이블을보고 조인 그래서 : 중앙 값이 우리가 왼쪽에서 3-1 = 2, 3 + 1에서 확인 '3'인 경우 오른쪽)와 ROW가 오른쪽 또는 왼쪽에서 NULL 값을 가질 때 우리는 더 인접한 값이없는 것을 알고있다.

    내 테이블의 전체 원시 출력은 다음과 같습니다

    select * from arrc_vouchers order by id asc;

0  
2  
3  
4  
5  
6  
7  
8  
9  
22 
23 
24 
29 
33

    일부 노트 :

  7. ==============================

    7.따라서 하나 & 테이블을 표현하기 위해 @var 사용할 수있는 방법을 보여 또한 원래의 질문에 답하고 - Lucek에 의해 위에 주어진 답변에 따라이 저장 프로 시저를 사용하면 비 연속 기록을 찾기 위해 테스트하고자하는 테이블 및 열 이름을 지정할 수 있습니다 저장 프로 시저에서 / 또는 열.

    따라서 하나 & 테이블을 표현하기 위해 @var 사용할 수있는 방법을 보여 또한 원래의 질문에 답하고 - Lucek에 의해 위에 주어진 답변에 따라이 저장 프로 시저를 사용하면 비 연속 기록을 찾기 위해 테스트하고자하는 테이블 및 열 이름을 지정할 수 있습니다 저장 프로 시저에서 / 또는 열.

    create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64))
language sql
not deterministic
contains sql
sql security definer
comment ''
begin
declare strsql varchar(1000);
declare tbl varchar(64);
declare col varchar(64);

set @tbl=cast(param_tbl as char character set utf8);
set @col=cast(param_col as char character set utf8);

set @strsql=concat("select 
    ( t1.",@col," + 1 ) as starts_at, 
  ( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at
    from ",@tbl," t1
        where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 )
        having ends_at is not null");

prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
end
  8. ==============================

    8.두 숫자 사이에 최대 하나의 갭을 갖는 시퀀스와 같은 (있으면 1,3,5,6)를 사용할 수있는 쿼리는 다음과 같습니다

    두 숫자 사이에 최대 하나의 갭을 갖는 시퀀스와 같은 (있으면 1,3,5,6)를 사용할 수있는 쿼리는 다음과 같습니다

    select s.id+1 from source1 s where s.id+1 not in(select id from source1) and s.id+1<(select max(id) from source1);
  9. ==============================

    9.나는 다른 매너와 나는이 간단한 쿼리이었다 발견 한 최상의 성능에 그것을 시도 :

    나는 다른 매너와 나는이 간단한 쿼리이었다 발견 한 최상의 성능에 그것을 시도 :

    select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;

    ... 하나는 왼쪽에 다음 ID가있는 경우 발견되지 않는 경우, 다음, 다음 하위 쿼리 간격의 끝을 찾을 수있는 다음 ID를 찾을 경우에만 확인하기 위해 가입 할 수 있습니다. 내가 해냈어 때문에 (>) 연산자보다 같 (=)는 더 나은 성능과 쿼리.

    sqlfiddle 사용은하지 다른 쿼리의 너무 다른 성능을 보여하지만, 실제 데이터베이스에 결과 위의이 쿼리는 3 배 이상 빠른 다른 사람보다.

    스키마 :

    CREATE TABLE arrc_vouchers (id int primary key)
;
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
;

    나는 성능을 비교하려고하는 모든 쿼리 노호에 따라 :

    select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;
select *, (gapEnd-gapIni) qt
    from (
        select id+1 gapIni
        ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
        from arrc_vouchers a
        order by id
    ) a where gapEnd <> gapIni
;
select id+1 gapIni
    ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
    #,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    where id+1 <> (select x.id from arrc_vouchers x where x.id>a.id limit 1)
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),concat('*** GAT *** ',(select x.id from arrc_vouchers x where x.id>a.id limit 1))) gapEnd
    from arrc_vouchers a
    order by id
;

    어쩌면 누군가 유용한 도움이됩니다.

    당신은 볼이 sqlfiddle를 사용하여 내 쿼리를 테스트 할 수 있습니다 :

    http://sqlfiddle.com/#!9/6bdca7/1

  10. ==============================

    10.이 모두 제대로 작동하지만, 매우 긴 시간에 결과 집합을 반환 50,000 기록이있는 경우.

    이 모두 제대로 작동하지만, 매우 긴 시간에 결과 집합을 반환 50,000 기록이있는 경우.

    나는 이것을 사용하고 쿼리에서 훨씬 빠른 복귀와의 격차 나 (마지막 1 + 사용) 사용 가능한 다음을 찾을 수 있습니다.

    SELECT a.id as beforegap, a.id+1 as avail
FROM table_name a
where (select b.id from table_name b where b.id=a.id+1) is null
limit 1;
  11. ==============================

    11.아마 관련,하지만 난 숫자의 순서로 격차를 목록에이 같은 뭔가를 찾고 정확하게 당신이 찾고있는 무엇에 따라 여러 다른 솔루션을 가지고이 게시물을 발견하지. 나는 순서에서 사용 가능한 첫 번째 차이 (즉, 다음 사용 가능한 번호)를 찾고 있었다, 이것은 잘 작동 보인다.

    아마 관련,하지만 난 숫자의 순서로 격차를 목록에이 같은 뭔가를 찾고 정확하게 당신이 찾고있는 무엇에 따라 여러 다른 솔루션을 가지고이 게시물을 발견하지. 나는 순서에서 사용 가능한 첫 번째 차이 (즉, 다음 사용 가능한 번호)를 찾고 있었다, 이것은 잘 작동 보인다.

    L LEFT OUTER 같은 환자 nextavabile 같은 SELECT MIN (l.number_sequence + 1) r.number_sequence은 NULL이다 l.number_sequence + 1 = r.number_sequence에 R로 환자 가입. 몇 가지 다른 시나리오 및 솔루션은 2005 년부터,이 논의!

    시퀀스와 SQL에서 값을 누락 찾는 방법

    12. from https://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 다른 주문 BY와 PostgreSQL을 DISTINCT ON  (0) 2020.03.20
[SQL] 쿼리 "NOT IN"MySQL은  (0) 2020.03.20
[SQL] SQL에서, 계수의 차이 (열) 무엇과 COUNT (*)?  (0) 2020.03.20
[SQL] .NET에서 Math.Max ​​같은 두 값을 사용합니다 SQL Server의 최대 기능이 있습니까?  (0) 2020.03.20
[SQL] 무엇 TRUNCATE의 차이점은 그리고 SQL에서 DELETE  (0) 2020.03.20
,

티스토리툴바