SQL-запрос для группировки элементов по времени, но только если они находятся рядом друг с другом?

Я пытаюсь создать оператор SQL для извлечения значений образца из БД. Таблица содержит значения, относящиеся к смене инструмента на станке с ЧПУ. Текущий оператор, который у меня есть, правильно извлекает значения, но только в том случае, если для данной программы есть одно вхождение инструмента. Если инструмент появляется несколько раз, значения времени соответствуют от первой загрузки до последней загрузки. Существует только один столбец TIME, и, найдя его первое и последнее вхождение, я могу определить время входа/выхода инструментов.

Основной пример:

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03

Однако, когда инструмент появляется несколько раз, все становится сложно, поскольку я больше не могу использовать правила TOP и DISTINCT.

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03
200            12:04
200            12:05
100            12:06
100            12:07

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:07
200            12:04       12:05

Ideal Query Returns:
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03
200            12:04       12:05
100            12:06       12:07

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

SELECT * FROM (SELECT DISTINCT SPINDLE_POT FROM TBL_SPINDLE_DATA_M1 
WHERE TIME BETWEEN '4/3/20131:24:13 PM' AND '4/3/2013 3:07:33 PM') AS A 

CROSS APPLY

((SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT AND
TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM') AS NEWTABLE1

JOIN

(SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT 
AND TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM' ORDER BY TIME DESC) 
AS NEWTABLE2 ON (0=0))

Я ни в коем случае не являюсь экспертом по SQL Query! Приведенный выше запрос может быть ужасно неправильным, но на самом деле он возвращает то, что мне нужно. Можно ли как-то сгруппировать похожие элементы, но быть достаточно рассудительным, чтобы не группировать их, если их индексы не соприкасаются друг с другом?


sql sql-server sql-server-2012 gaps-and-islands
person Lee Harrison    schedule 04.04.2013    source источник
comment
Я предполагаю, что SQL Server используется из-за использования APPLY, но вы должны убедиться, что вы пометили свои вопросы соответствующей базой данных.   -  person Taryn    schedule 04.04.2013
comment
Спасибо, я тоже использую SQL 2012. Тег обновлен.   -  person Lee Harrison    schedule 04.04.2013
comment
Что именно является критерием для того, чтобы значения считались двумя отдельными пакетами вместо одного? Дело в том, что между ними не должно быть временного промежутка, то есть записи считаются одним пакетом, если они появляются непрерывно (поминутно)? Или дело в том, что между ними не должно быть никакого другого инструмента (по времени)?   -  person alexander.biskop    schedule 04.04.2013
comment
Инструменты можно загружать только по одному, поэтому пакеты должны постоянно (по времени) иметь один и тот же номер инструмента, чтобы их можно было сгруппировать вместе. Или, как вы выразились, между ними не должно быть никаких других записей инструментов (по времени).   -  person Lee Harrison    schedule 04.04.2013

Ответы (2)


arrow_upward
7
arrow_downward

Вот еще один подход с использованием LAG/LEAD:

DECLARE @rawdata TABLE(Tool_Number INT, [Time] TIME(0));

INSERT @rawdata VALUES
(100,'12:00'), (100,'12:01'), (100,'12:02'), (100,'12:03'),
(200,'12:04'), (200,'12:05'),
(100,'12:06'), (100,'12:07');

;WITH x AS
(
  SELECT Tool_Number, [Time], 
    s = CASE Tool_number WHEN LAG(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END,
    e = CASE Tool_number WHEN LEAD(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END
  FROM @rawdata
),
y AS 
(
  SELECT Tool_Number, s, [Time], e = LEAD([Time],1) OVER (ORDER BY [Time]) 
  FROM x WHERE 1 IN (s,e)
)
SELECT Tool_number, TIME_IN = [Time], TIME_OUT = e 
FROM y 
WHERE s = 1
ORDER BY TIME_IN;

Полученные результаты:

Tool_number  TIME_IN   TIME_OUT
-----------  --------  --------
100          12:00:00  12:03:00
200          12:04:00  12:05:00
100          12:06:00  12:07:00
person Aaron Bertrand    schedule 04.04.2013
comment
Это отлично работает, и это намного быстрее, чем текущая «сломанная» реализация, которую я собрал. Благодаря тонну! - person Lee Harrison; 04.04.2013
comment
Хороший. Жаль, что это не работает правильно, когда остров состоит из одной строки. - person Andriy M; 05.04.2013
comment
Не знаю, есть ли много способов исправить это, но изменение выражения e в y на e = CASE e WHEN 1 THEN [Time] ELSE LEAD([Time],1) OVER (ORDER BY [Time]) END кажется работает. - person Andriy M; 05.04.2013
comment
Ах, хороший улов @AndriyM, я не рассматривал однорядный остров! Позвольте мне вернуться к моему сценарию и посмотреть, есть ли другие точки зрения. - person Aaron Bertrand; 05.04.2013
comment
Полезно знать, хотя для моего набора данных это не проблема, поскольку отдельные строки данных не должны появляться. Если они это сделают, это плохие данные, которые мне все равно не нужны. - person Lee Harrison; 08.04.2013

arrow_upward
9
arrow_downward

Это называется «проблемой островов», и я видел в этом решение (кредит Ицика Бен Гана).

select  tool_number,
        min(time) 'in',
        max(time) 'out',
        count(*)
from    (
    select  tool_number,
            time,
            ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY Tool_Number ORDER BY time) AS Grp
    from    #temp
    ) as a
group by grp, tool_number
order by min(time)
person Nate    schedule 04.04.2013
comment
Хорошее решение, и в качестве бонуса будет работать на 2005+. Я добавил решение LAG/LEAD только потому, что мог (вопросы 2012 года довольно редки) и потому, что это, кажется, приводит к лучшему плану (мое решение имеет один вид по сравнению с тремя в вашем). - person Aaron Bertrand; 04.04.2013