Сводная группа SQL по строкам и столбцам

ProgramBox

Сводная группа SQL по строкам и столбцам

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

#sql #sql-server #pivot

Вопрос:

Итак, у меня есть таблица активности водителей, которую я хочу рассчитать за день. Примеры данных:

Имя	Дата и время начала	Дата и время окончания	Начальный текст	Конечный текст	Рабочее состояние
Джим	2021-09-09 06:28:16	2021-09-09 07:28:16	«Начальная точка 0»	«Конечное место 0»	«Пауза»
Джим	2021-09-09 07:28:16	2021-09-09 08:28:16	«Начальная точка 1»	«Конечное место 1»	«Загрузка»
Джим	2021-09-09 08:28:16	2021-09-09 09:28:16	«Начальная площадка 2»	«Конечное место 2»	«Вождение»
Джим	2021-09-09 10:28:16	2021-09-09 11:28:16	«Начальная точка 4»	«Конечная точка 4»	«Загрузка»
Джим	2021-10-09 09:28:16	2021-10-09 10:28:16	«Начальная точка 5»	«Конечное место 5»	«Вождение»
Джим	2021-10-09 10:28:16	2021-10-09 11:28:16	«Начальная точка 6»	«Конечное место 6»	«Загрузка»
Джим	2021-10-09 11:28:16	2021-10-09 14:28:16	«Начальная точка 7»	«Конечная точка 7»	«Вождение»

Желаемый результат: (У меня есть только 3 возможных рабочих состояния)

Имя	Дата и время начала	Дата и время окончания	Начальный текст	Конечный текст	Загрузка	Вождение
Джим	2021-09-09 06:28:16	2021-09-09 11:28:16	«Начальная точка 0»	«Конечная точка 4»	00:02:00	00:01:00
Джим	2021-10-09 09:28:16	2021-09-09 14:28:16	«Начальная точка 5»	«Конечная точка 7»	00:01:00	00:04:00

Пауза	День прошел успешно
00:01:00	2021-09-09
00:00:00	2021-10-09

Что у меня есть на данный момент на мой взгляд : (краткое изложение)

  --CREATE view vwWorkingTimesPerDay as
select  
   w.DriverName, 
   convert(date, w.StartDateAndTime) as DayPerformed,
   MIN(w.StartDateAndTime) as StartTime,
   MAX(w.EndDateAndTime) as EndTime,
   StartPositionText,
   EndPositionText

from  ( 
  SELECT *,
  StartPositionText = FIRST_VALUE(StartPosText) OVER (PARTITION BY w.DriverName, convert(date, 
  w.StartDateAndTime)
        ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  EndPositionText   = LAST_VALUE(EndPosText) OVER (PARTITION BY w.DriverName, convert(date, 
  w.StartDateAndTime)
        ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM webfleet.tblWorkingTimes w) w

  group by w.DriverName, 
     convert(date, w.StartDateAndTime),
     StartPositionText,
     EndPositionText,

Чтобы показать продолжительность в исходной таблице, я использовал:

 right('00'   convert(varchar, datepart(hour, dateadd(s, sum(datediff(second, 
    w.StartDateAndTime, w.EndDateAndTime)), 0))), 2)   ':'
   right('00'   convert(varchar, datepart(minute, dateadd(s, sum(datediff(second, 
  w.StartDateAndTime, w.EndDateAndTime)), 0))), 2)   ':'
    right('00'   convert(varchar, datepart(second, dateadd(s, sum(datediff(second, 
  w.StartDateAndTime, w.EndDateAndTime)), 0))), 2) 
   as Duration

Я попробовал эту статью https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns

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

1. Можете ли вы объяснить логику: вы хотите просто рассчитать чередующиеся наборы Loading``Driving``Pause ? Или вы хотите подвести итог за день?

2. В зависимости от штата работы я хочу рассчитать, сколько часов они тратят на это в день, чтобы мы знали, сколько мы должны им платить. (в месяц-это следующая задача) Проблема в том, что компания, которая отправляет мне данные, не имеет сводки за день, поэтому я должен рассчитать ее сам.

3. Также извините за поздний ответ, мы не работаем по выходным.

Ответ №1:

Для этого потребуется несколько шагов

Мы используем CAST AS date , чтобы получить дату без времени
ROW_NUMBER и LEAD помогите нам определить начало и конец каждой группы
Затем просто агрегируйте по Name дате
И используйте условную агрегацию, чтобы получить желаемые результаты
Чтобы получить значение времени, мы добавляем ко 0:00:00 времени количество секунд

 SELECT
  Name,
  DayPerformed,
  StartDateAndTime = MIN(StartDateAndTime),
  EndDateAndTime = MAX(EndDateAndTime),
  StartText = MIN(CASE WHEN rn = 1 THEN StartText END),
  EndText = MIN(CASE WHEN nxt IS NULL THEN EndText END),
  Loading = DATEADD(ms,
      ISNULL(SUM(CASE WHEN WorkState = 'Loading' THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0),
      CAST('0:00:00' AS time)),
  Driving = DATEADD(ms, 
      ISNULL(SUM(CASE WHEN WorkState = 'Driving' THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0),
      CAST('0:00:00' AS time)),
  Pause   = DATEADD(ms,
      ISNULL(SUM(CASE WHEN WorkState = 'Pause'   THEN DATEDIFF(ms, StartDateAndTime, EndDateAndTime) END), 0), 
      CAST('0:00:00' AS time))
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Name, DayPerformed ORDER BY StartDateAndTime),
      nxt = LEAD(WorkState) OVER (PARTITION BY Name, DayPerformed ORDER BY StartDateAndTime)
    FROM tblWorkingTimes wt
    CROSS APPLY (VALUES (CAST(StartDateAndTime AS date))) v(DayPerformed)
) wt
GROUP BY
  Name,
  DayPerformed;

Для агрегирования по месяцам просто замените DayPerformed на

 CROSS APPLY (VALUES (EOMONTH(StartDateAndTime))) v(DayPerformed)

db<>скрипка