Преобразование последовательных чисел в один столбец с отсутствующими значениями в SQL

У меня есть список идентификаторов образцов для сайта в формате: имя сайта, номер образца, так что для данного сайта существует n номеров образцов. Например, данные могут быть:

site1 | 1
site1 | 2

и т. д. до произвольного n.

Используя следующий пример в качестве аналогичного примера, эти данные ниже получат ответ от последнего оператора выбора:

CREATE TABLE #SiteWithId(SiteId VARCHAR(50), SampleNumber INT)

INSERT INTO #SiteWithId
(
    SiteId,
    SampleNumber
)
values 
(   'test', -- SiteId - varchar(50)
    1  -- SampleNumber - int
    ),
    ('test',2),
    ('test',3),
    ('test',4),
    ('test',6),
    ('test',7)

    SELECT * FROM #SiteWithId
    DROP TABLE #SiteWithId
    --the answer
    SELECT 'test', '1-4,6-7'

Обратите внимание, что отсутствующий элемент создает разрыв в окончательном ответе.

Я знаю, что могу прокрутить набор данных на C# и создать такой элемент. Но кто-нибудь знает, как создать такое значение, используя только sql, чтобы я мог просто выдать нужные значения для отчета? Я думаю, что мог бы сделать цикл и в sql, но я боюсь, что это будет немасштабируемо, поскольку на самом деле это не то, для чего предназначен sql.

Есть ли лучший способ сделать это, кроме цикла в sql или c #?


sql sql-server tsql window-functions gaps-and-islands
person done_merson    schedule 27.03.2019    source источник
comment
Нет необходимости в петлях. Это кажется довольно небольшой задачей с использованием таблицы Numbers/Tally (или даже специальной таблицы Tally). ОДНАКО, я не понимаю, как структурированы ваши данные. Укусы? Ряды? Некоторое форматирование было бы полезно.   -  person John Cappelletti    schedule 28.03.2019

Ответы (1)


arrow_upward
5
arrow_downward

Вот решение, основанное на оконных функциях. Разница между SampleNumber записи и ее ROW_NUMBER() в группах записей, имеющих одинаковые SiteName, дает вам группу, к которой она принадлежит. Затем внешний запрос объединяет каждую группу:

SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
FROM (
    SELECT 
        SiteName, 
        SampleNumber, 
        ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
    FROM mytable
) x
GROUP BY SiteName, (SampleNumber - rn)

Демонстрация DB Fiddle:

Образец данных:

SiteName | SampleNumber
:------- | -----------:
site1    |            1
site1    |            2
site1    |            3
site1    |            5
site1    |            6
site1    |            8
site1    |            9
site1    |           10

Результаты:

SiteName | SampleRange
:------- | :----------
site1    | 1-3        
site1    | 5-6        
site1    | 8-10

Если вы хотите, чтобы все диапазоны каждого сайта были объединены в одну запись, вы можете добавить еще один уровень агрегации и использовать STRING_AGG() (доступно с SQL Server 2017):

SELECT SiteName, STRING_AGG(SampleRange,',') SampleRange
FROM (
    SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
    FROM (
        SELECT 
            SiteName, 
            SampleNumber, 
            ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
        FROM mytable
    ) x
    GROUP BY SiteName, (SampleNumber - rn)
) y
GROUP BY SiteName

Демо:

SiteName | SampleRange 
:------- | :-----------
site1    | 1-3,5-6,8-10
person GMB    schedule 27.03.2019