Группировать последовательные строки с одинаковым значением, используя временные интервалы

ProgramBox

Группировать последовательные строки с одинаковым значением, используя временные интервалы

Post author:admin
Запись опубликована:16 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

Извините за расплывчатое название (я просто не знаю, как описать эту загадку)

Предоставьте следующую таблицу расписания для класса:

 ╔═══════════╦════════════╦═══════════╦═══════════╦═════════╗
║ Classroom ║ CourseName ║  Lesson   ║ StartTime ║ EndTime ║
╠═══════════╬════════════╬═══════════╬═══════════╬═════════╣
║      1001 ║ Course 1   ║ Lesson 1  ║      0800 ║    0900 ║
║      1001 ║ Course 1   ║ Lesson 2  ║      0900 ║    1000 ║
║      1001 ║ Course 1   ║ Lesson 3  ║      1000 ║    1100 ║
║      1001 ║ Course 2   ║ Lesson 10 ║      1100 ║    1200 ║
║      1001 ║ Course 2   ║ Lesson 11 ║      1200 ║    1300 ║
║      1001 ║ Course 1   ║ Lesson 4  ║      1300 ║    1400 ║
║      1001 ║ Course 1   ║ Lesson 5  ║      1400 ║    1500 ║
╚═══════════╩════════════╩═══════════╩═══════════╩═════════╝

Я хотел бы сгруппировать таблицу для отображения этого:

 ╔═══════════╦════════════╦═══════════╦═════════╗
║ Classroom ║ CourseName ║ StartTime ║ EndTime ║
╠═══════════╬════════════╬═══════════╬═════════╣
║      1001 ║ Course 1   ║      0800 ║    1100 ║
║      1001 ║ Course 2   ║      1100 ║    1300 ║
║      1001 ║ Course 1   ║      1300 ║    1500 ║
╚═══════════╩════════════╩═══════════╩═════════╝

В основном мы смотрим на расписание, которое показывает, какой crouse использует какой класс в течение определенного промежутка времени…

Моей первоначальной мыслью было: сгруппировать по Classroom и CourseName и взять Max и Min на startend время, но это не даст мне временных интервалов, которые он покажет, как если бы курс 1 использовал Classroom с 08:00 до 16:00 без перерыва в середине.

1. Я не знаю, может ли это вам помочь, но попробуйте это: dba.stackexchange.com/questions/31281/… Вероятно, это будет намного проще, если сделать это на языке, на котором вы используете базу данных. (Если вы используете)

2. На самом деле я этого не пробовал, но что, если вы row_number, чтобы получить уникальный номер для каждой последовательной группы (вы можете упорядочить по дате). После того, как вы это сделаете, вы сможете выполнить группировку, как вы предложили выше.

Ответ №1:

Если вы используете SQLServer 2012 или выше, вы можете использовать LAG для получения предыдущего значения столбца, а затем SUM() OVER (ORDER BY ...) для создания скользящей суммы, в данном случае той, которая учитывает изменение имени курса, которое можно использовать в качестве GROUP BY привязки

 With A AS (
  SELECT ClassRoom
       , CourseName
       , StartTime
       , EndTime
       , PrevCourse = LAG(CourseName, 1, CourseName) OVER (ORDER BY StartTime)
  FROM   Table1
), B AS (
  SELECT ClassRoom
       , CourseName
       , StartTime
       , EndTime
       , Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
                OVER (ORDER BY StartTime, CourseName)
  FROM   A
)
SELECT ClassRoom
     , CourseName
     , MIN(StartTime) StartTime
     , MAX(EndTime) EndTime
FROM   B
GROUP BY ClassRoom, CourseName, Ranker
ORDER BY StartTime

SQLFiddle demo

1. Отличный ответ! Разумное использование SUM() функции. Мне пришлось скорректировать ваш запрос в соответствии с моей проблемой, добавив partition by ClassRoom в OVER() предложение в функции LAG() and SUM() , чтобы иметь возможность обрабатывать несколько классов. Спасибо.

Ответ №2:

Запрос определяет каждую строку EndTime , используя NOT EXISTS , чтобы убедиться, что между диапазоном курсов StartTime и EndTime и затем использует MIN и GROUP BY , чтобы найти StartTime .

NOT EXISTS Часть гарантирует отсутствие «разрывов» между диапазонами StartTime и EndTime путем поиска любых строк, которые имеют EndTime значение между StartTime и EndTime , но принадлежат другому CourseName или CourseRoom .

 SELECT    
    t0.ClassRoom,
    t0.CourseName,
    MIN(t0.StartTime),
    t0.EndTime
FROM (
    SELECT 
    t1.ClassRoom,
    t1.CourseName,
    t1.StartTime,
    (
        SELECT MAX(t2.EndTime)
        FROM tableA t2
        WHERE t2.CourseName = t1.CourseName
        AND t2.ClassRoom = t1.ClassRoom
        AND NOT EXISTS (SELECT 1 FROM tableA t3
            WHERE t3.EndTime < t2.EndTime 
            AND t3.EndTime > t1.EndTime
            AND (t3.CourseName <> t2.CourseName 
            OR t3.ClassRoom <> t2.ClassRoom)
        )
    ) EndTime
    FROM tableA t1
) t0 GROUP BY t0.ClassRoom, t0.CourseName, t0.EndTime

http://www.sqlfiddle.com /#!6/39d4b/9

1. Хорошо, это работает .. спасибо .. но мне трудно понять последнюю часть, где говорится, что НЕ СУЩЕСТВУЕТ …. можете ли вы подробнее рассказать о логике?

2. блестящее решение… 1 для этого

3. Не могли бы вы объяснить, почему, когда он запрашивает «первый курс 1», при вычислении Max (EndTime) он выдает (1100) вместо (1500)?

4. @Отметить, потому что подзапрос выбирает наибольшее время окончания, когда другой курс, прошедший между временем начала и окончания, не существует. между 0800 и 1500 был курс 2 в 1300, поэтому 1500 не подходит. между 0800 и 1100 другими курсами не было.

Ответ №3:

      CREATE TABLE Classroom(Classroom VARCHAR(100), CourseName  VARCHAR(100),  Lesson    VARCHAR(100), StartTime  VARCHAR(100), EndTime  VARCHAR(100))
 INSERT INTO Classroom
SELECT '1001','Course 1','Lesson 1 ','0800','0900'
UNION SELECT '1001','Course 1','Lesson 2 ','0900','1000'
UNION SELECT '1001','Course 1','Lesson 3 ','1000','1100'
UNION SELECT '1001','Course 2','Lesson 10','1100','1200'
UNION SELECT '1001','Course 2','Lesson 11','1200','1300'
UNION SELECT '1001','Course 1','Lesson 4 ','1300','1400'
UNION SELECT '1001','Course 1','Lesson 5 ','1400','1500'

SELECT * FROM Classroom

;WITH CTE_ClassRooms AS (
SELECT *,ROW_NUMBER() over(partition by classroom,CourseName order by StartTime) AS R FROM Classroom A 
WHERE NOT EXISTS(SELECT 1 FROM Classroom B WHERE B.Classroom = A.Classroom AND B.CourseName = A.CourseName AND B.StartTime = A.EndTime)
UNION ALL
SELECT B.*,R fROM CTE_ClassRooms A JOIN Classroom B ON B.Classroom = A.Classroom AND B.CourseName = A.CourseName AND A.StartTime = B.EndTime
)

--SELECT * FROM CTE_ClassRooms order by Classroom,CourseName,R

SELECT Classroom,CourseName,MIN(StartTime),MAX(EndTime)
FROM CTE_ClassRooms
GROUP BY Classroom,CourseName,R