[mysql] mysql에서 순차 번호 매기기의 차이를 찾는 방법은 무엇입니까?

Program / 글쓴이

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

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

100을 반환해야하지만 대신 87을 반환합니다. 누락 된 숫자의 값을 반환하는 쿼리를 실행할 수 있습니까? 예를 들어, ID 1-70 및 83-100에 대한 레코드가있을 수 있지만 ID가 71-82 인 레코드가 없습니다. 71, 72, 73 등을 반환하고 싶습니다.

이게 가능해?

답변

최신 정보

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
  • gap_starts_at -현재 간격의 첫 번째 ID
  • gap_ends_at -현재 간격의 마지막 ID

답변

이것은 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)

열의 순서 expected와got 중요합니다.

이것이 YourCol1에서 시작하지 않고 중요하지 않다는 것을 알고 있다면

(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에 대해 일종의 셸 스크립트 작업을 수행해야하는 경우 bash에서 반복 할 수있는 표현식을 직접 생성하기 위해이 변형을 사용할 수도 있습니다.

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)

그런 다음 bash 터미널의 for 루프에 복사하여 붙여 넣어 모든 ID에 대해 명령을 실행할 수 있습니다.

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

위와 동일하지만 읽기와 실행이 모두 가능합니다. 위의 “CONCAT”명령을 변경하면 다른 프로그래밍 언어에 대한 구문을 생성 할 수 있습니다. 아니면 SQL 일 수도 있습니다.

답변

트릭을 수행해야하는 빠르고 더러운 쿼리 :

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

답변

를 사용하는 MariaDB경우 시퀀스 저장소 엔진을 사용하는 더 빠른 (800 %) 옵션이 있습니다 .

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

답변

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

외부이 테이블을 arrc_vouchers 테이블에 조인하고 arrc_vouchers ID가 널인 단일 열 값을 선택하십시오.

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

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

답변

쿼리 + 일부 처리를 수행하는 일부 코드가 필요한 대체 솔루션은 다음과 같습니다.

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의 플래너가 성능을 발휘하지 못하는 것으로 알고있는 하위 선택이 포함되어 있지 않습니다.

그러면 더 작은 값 (lValue)이나 더 큰 값 (rValue)이없는 centralValue (cValue) 당 하나의 항목이 반환됩니다. 즉,

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

더 자세한 내용을 다루지 않고 (다음 단락에서 볼 것입니다)이 출력은 다음을 의미합니다.

  • 0과 2 사이의 값이 없습니다.
  • 9에서 22 사이의 값 없음
  • 24에서 29 사이의 값 없음
  • 29에서 33 사이의 값 없음
  • 33에서 MAX VALUE 사이의 값이 없습니다.

따라서 기본 아이디어는 동일한 테이블로 RIGHT 및 LEFT 조인을 수행하여 값당 인접 값이 있는지 확인하는 것입니다 (즉, 중앙 값이 ‘3’이면 왼쪽에서 3-1 = 2, 오른쪽), 그리고 ROW가 RIGHT 또는 LEFT에 NULL 값을 가질 때 우리는 인접한 값이 없다는 것을 압니다.

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

select * from arrc_vouchers order by id asc;

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

몇 가지 참고 사항 :

  1. ‘id’필드를 UNSIGNED로 정의하면 조인 조건의 SQL IF 문이 필요하므로 0 미만으로 줄일 수 없습니다. 다음 노트에 언급 된대로 c.value> 0을 유지하는 경우 반드시 필요하지는 않지만 문서로 포함합니다.
  2. 이전 값에 관심이없고 다음 행에서 post 값을 파생 할 수 있으므로 0 중심 값을 필터링합니다.

답변

두 숫자 사이에 최대 1의 간격이있는 시퀀스 (예 : 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);
  • table_name- source1
  • column_name- id