SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

code

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

codestyles 2020. 8. 28. 07:29

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

SQL 테이블의 카운터 열에서 첫 번째 "간격"을 찾고 싶습니다. 예를 들어 1, 2, 4, 5 값이 있다면 3을 찾고 싶습니다.

물론 값을 순서대로 가져 와서 수동으로 살펴볼 수 있지만 SQL에서 수행하는 방법이 있는지 알고 싶습니다.

또한 다양한 DBMS에서 작동하는 표준 SQL이어야합니다.

에서 MySQL와 PostgreSQL:

SELECT  id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id
LIMIT 1

에서 SQL Server:

SELECT  TOP 1
        id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id

에서 Oracle:

SELECT  *
FROM    (
        SELECT  id + 1 AS gap
        FROM    mytable mo
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    mytable mi 
                WHERE   mi.id = mo.id + 1
                )
        ORDER BY
                id
        )
WHERE   rownum = 1

ANSI (모든 곳에서 작동하며 효율성이 가장 낮음) :

SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )

슬라이딩 윈도우 기능을 지원하는 시스템 :

SELECT  -- TOP 1
        -- Uncomment above for SQL Server 2012+
        previd
FROM    (
        SELECT  id,
                LAG(id) OVER (ORDER BY id) previd
        FROM    mytable
        ) q
WHERE   previd <> id - 1
ORDER BY
        id
-- LIMIT 1
-- Uncomment above for PostgreSQL

첫 번째 값 id = 1이면 모든 답변이 제대로 작동합니다. 그렇지 않으면이 간격이 감지되지 않습니다. 예를 들어 테이블 ID 값이 3,4,5이면 쿼리는 6을 반환합니다.

나는 이렇게했다

SELECT MIN(ID+1) FROM (
    SELECT 0 AS ID UNION ALL 
    SELECT  
        MIN(ID + 1)
    FROM    
        TableX) AS T1
WHERE
    ID+1 NOT IN (SELECT ID FROM TableX)

내 머릿속에 처음으로 떠오른 것. 이런 식으로가는 것이 좋은 생각인지 확실하지 않지만 작동해야합니다. 테이블이 t있고 열이 c다음 과 같다고 가정합니다 .

SELECT t1.c+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL ORDER BY gap ASC LIMIT 1

편집 : 이것은 더 빠르게 (그리고 더 짧아 질 수 있습니다!) :

SELECT min(t1.c)+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL

이것은 SQL Server에서 작동합니다-다른 시스템에서는 테스트 할 수 없지만 표준으로 보입니다.

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))

where 절에 시작점을 추가 할 수도 있습니다.

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000

따라서 2003과 2004가 존재하지 않는 2000, 2001, 2002 및 2005가 있다면 2003을 반환합니다.

이를 수행 하는 매우 표준적인 SQL 방법은 없지만 어떤 형태의 제한 절을 사용하면 할 수 있습니다.

SELECT `table`.`num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
LIMIT 1

(MySQL, PostgreSQL)

또는

SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL

(SQL 서버)

또는

SELECT `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
AND ROWNUM = 1

(신탁)

가능한 모든 값이있는 뷰 또는 시퀀스에 대한 내부 조인.

테이블이 없나요? 테이블을 만드십시오. 나는 항상 이것을 위해 더미 테이블을 가지고 있습니다.

create table artificial_range( 
  id int not null primary key auto_increment, 
  name varchar( 20 ) null ) ;

-- or whatever your database requires for an auto increment column

insert into artificial_range( name ) values ( null )
-- create one row.

insert into artificial_range( name ) select name from artificial_range;
-- you now have two rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have four rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have eight rows

--etc.

insert into artificial_range( name ) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024

그때,

 select a.id from artificial_range a
 where not exists ( select * from your_table b
 where b.counter = a.id) ;

에 대한 PostgreSQL

재귀 쿼리를 사용하는 예입니다.

특정 범위에서 간격을 찾으려는 경우 유용 할 수 있습니다 (테이블이 비어 있어도 작동하지만 다른 예제는 그렇지 않습니다).

WITH    
    RECURSIVE a(id) AS (VALUES (1) UNION ALL SELECT id + 1 FROM a WHERE id < 100), -- range 1..100  
    b AS (SELECT id FROM my_table) -- your table ID list    
SELECT a.id -- find numbers from the range that do not exist in main table
FROM a
LEFT JOIN b ON b.id = a.id
WHERE b.id IS NULL
-- LIMIT 1 -- uncomment if only the first value is needed

내 추측:

SELECT MIN(p1.field) + 1 as gap
FROM table1 AS p1  
INNER JOIN table1 as p3 ON (p1.field = p3.field + 2)
LEFT OUTER JOIN table1 AS p2 ON (p1.field = p2.field + 1)
WHERE p2.field is null;

이것은 지금까지 언급 된 모든 것을 설명합니다. 시작점으로 0을 포함하며 값이없는 경우 기본값으로 설정됩니다. 또한 다중 값 키의 다른 부분에 대한 적절한 위치를 추가했습니다. 이것은 SQL Server에서만 테스트되었습니다.

select
    MIN(ID)
from (
    select
        0 ID
    union all
    select
        [YourIdColumn]+1
    from
        [YourTable]
    where
        --Filter the rest of your key--
    ) foo
left join
    [YourTable]
    on [YourIdColumn]=ID
    and --Filter the rest of your key--
where
    [YourIdColumn] is null

나는 그것을하는 빠른 방법을 썼다. 이것이 가장 효율적인지 확실하지 않지만 작업을 완료합니다. 간격을 알려주는 것이 아니라 간격 전후의 ID를 알려줍니다 (간격은 여러 값이 될 수 있으므로 예를 들어 1,2,4,7,11 등).

저는 sqlite를 예로 사용하고 있습니다.

이것이 당신의 테이블 구조라면

create table sequential(id int not null, name varchar(10) null);

그리고 이것들은 당신의 행입니다

id|name
1|one
2|two
4|four
5|five
9|nine

쿼리는

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null and a.id <> (select min(id) from sequential)
union
select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null and a.id <> (select max(id) from sequential);

https://gist.github.com/wkimeria/7787ffe84d1c54216f1b320996b17b7e

select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])

다음은 변경없이 모든 데이터베이스 서버에서 실행되는 표준 SQL 솔루션입니다.

select min(counter + 1) FIRST_GAP
    from my_table a
    where not exists (select 'x' from my_table b where b.counter = a.counter + 1)
        and a.counter <> (select max(c.counter) from my_table c);

행동에 대한 참조;

Oracle의 livesql을 통한 PL / SQL ,
sqlfiddle을 통한 MySQL ,
sqlfiddle을 통한 PostgreSQL
sqlfiddle을 통한 MS SQL

빈 테이블이나 음수 값에서도 작동합니다. SQL Server 2012에서 방금 테스트했습니다.

 select min(n) from (
select  case when lead(i,1,0) over(order by i)>i+1 then i+1 else null end n from MyTable) w

Firebird 3를 사용하는 경우 가장 우아하고 간단합니다.

select RowID
  from (
    select `ID_Column`, Row_Number() over(order by `ID_Column`) as RowID
      from `Your_Table`
        order by `ID_Column`)
    where `ID_Column` <> RowID
    rows 1

            -- PUT THE TABLE NAME AND COLUMN NAME BELOW
            -- IN MY EXAMPLE, THE TABLE NAME IS = SHOW_GAPS AND COLUMN NAME IS = ID

            -- PUT THESE TWO VALUES AND EXECUTE THE QUERY

            DECLARE @TABLE_NAME VARCHAR(100) = 'SHOW_GAPS'
            DECLARE @COLUMN_NAME VARCHAR(100) = 'ID'


            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 
            'SELECT  TOP 1
                    '+@COLUMN_NAME+' + 1
            FROM    '+@TABLE_NAME+' mo
            WHERE   NOT EXISTS
                    (
                    SELECT  NULL
                    FROM    '+@TABLE_NAME+' mi 
                    WHERE   mi.'+@COLUMN_NAME+' = mo.'+@COLUMN_NAME+' + 1
                    )
            ORDER BY
                    '+@COLUMN_NAME

            -- SELECT @SQL

            DECLARE @MISSING_ID TABLE (ID INT)

            INSERT INTO @MISSING_ID
            EXEC (@SQL)

            --select * from @MISSING_ID

            declare @var_for_cursor int
            DECLARE @LOW INT
            DECLARE @HIGH INT
            DECLARE @FINAL_RANGE TABLE (LOWER_MISSING_RANGE INT, HIGHER_MISSING_RANGE INT)
            DECLARE IdentityGapCursor CURSOR FOR   
            select * from @MISSING_ID
            ORDER BY 1;  

            open IdentityGapCursor

            fetch next from IdentityGapCursor
            into @var_for_cursor

            WHILE @@FETCH_STATUS = 0  
            BEGIN
            SET @SQL = '
            DECLARE @LOW INT
            SELECT @LOW = MAX('+@COLUMN_NAME+') + 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' < ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + '
            DECLARE @HIGH INT
            SELECT @HIGH = MIN('+@COLUMN_NAME+') - 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' > ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + 'SELECT @LOW,@HIGH'

            INSERT INTO @FINAL_RANGE
             EXEC( @SQL)
            fetch next from IdentityGapCursor
            into @var_for_cursor
            END

            CLOSE IdentityGapCursor;  
            DEALLOCATE IdentityGapCursor;  

            SELECT ROW_NUMBER() OVER(ORDER BY LOWER_MISSING_RANGE) AS 'Gap Number',* FROM @FINAL_RANGE

찾을 방법의 대부분은 매우 실행 매우 느린에서 mysql. 여기에 대한 내 솔루션이 mysql < 8.0있습니다. 1M 레코드에 대해 테스트를 마쳤습니다. 다른 SQL 유형에 맞는지 확실하지 않습니다.

SELECT cardNumber - 1
FROM
    (SELECT @row_number := 0) as t,
    (
        SELECT (@row_number:=@row_number+1), cardNumber, cardNumber-@row_number AS diff
        FROM cards
        ORDER BY cardNumber
    ) as x
WHERE diff >= 1
LIMIT 0,1

시퀀스가 '1'에서 시작한다고 가정합니다.

다음 솔루션 :

테스트 데이터를 제공합니다.
an inner query that produces other gaps; and
it works in SQL Server 2012.

Numbers the ordered rows sequentially in the "with" clause and then reuses the result twice with an inner join on the row number, but offset by 1 so as to compare the row before with the row after, looking for IDs with a gap greater than 1. More than asked for but more widely applicable.

create table #ID ( id integer );

insert into #ID values (1),(2),    (4),(5),(6),(7),(8),    (12),(13),(14),(15);

with Source as (
    select
         row_number()over ( order by A.id ) as seq
        ,A.id                               as id
    from #ID as A WITH(NOLOCK)
)
Select top 1 gap_start from (
    Select 
         (J.id+1) as gap_start
        ,(K.id-1) as gap_end
    from       Source as J
    inner join Source as K
    on (J.seq+1) = K.seq
    where (J.id - (K.id-1)) <> 0
) as G

The inner query produces:

gap_start   gap_end

3           3

9           11

The outer query produces:

gap_start

3

If your counter is starting from 1 and you want to generate first number of sequence (1) when empty, here is the corrected piece of code from first answer valid for Oracle:

SELECT
  NVL(MIN(id + 1),1) AS gap
FROM
  mytable mo  
WHERE 1=1
  AND NOT EXISTS
      (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = mo.id + 1
      )
  AND EXISTS
     (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = 1
     )

DECLARE @Table AS TABLE(
[Value] int
)

INSERT INTO @Table ([Value])
VALUES
 (1),(2),(4),(5),(6),(10),(20),(21),(22),(50),(51),(52),(53),(54),(55)
 --Gaps
 --Start    End     Size
 --3        3       1
 --7        9       3
 --11       19      9
 --23       49      27


SELECT [startTable].[Value]+1 [Start]
     ,[EndTable].[Value]-1 [End]
     ,([EndTable].[Value]-1) - ([startTable].[Value]) Size 
 FROM 
    (
SELECT [Value]
    ,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS startTable
JOIN 
(
SELECT [Value]
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS EndTable
ON [EndTable].Record = [startTable].Record+1
WHERE [startTable].[Value]+1 <>[EndTable].[Value]

참고URL : https://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql

'code' 카테고리의 다른 글

Python의 생성기 함수에서 한 번에 하나의 값을 얻는 방법은 무엇입니까? (0)	2020.08.28
git update-index --assume-unchanged on directory (0)	2020.08.28
자바 스크립트 : 최고의 싱글 톤 패턴 (0)	2020.08.28
Stylecop 대 FXcop (0)	2020.08.28
PC에서 APK를 설치하는 방법은 무엇입니까? (0)	2020.08.28

현재글SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

codestyle

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

'code' 카테고리의 다른 글

'code'의 다른글

티스토리툴바

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

SQL로 카운터를 실행할 때 "갭"을 어떻게 찾습니까?

'code' 카테고리의 다른 글

'code'의 다른글

관련글

티스토리툴바