Рассчитать продолжительность с помощью T-SQL
У меня есть таблица, содержащая такие столбцы Date и PumpStatus:
Date PumpStatus
---------------------------
1/1/24 0:15 Running
1/1/24 1:25 Stop
1/1/24 2:21 Running
1/2/24 12:15 Stop
1/2/24 14:15 Running
Как написать запрос выбора для расчета общего состояния работы и общего состояния остановки насоса в минутах, используя приведенные выше данные в заданном диапазоне дат?
Я искал, но не нашел никакой помощи как таковой. большинство сообщений связаны с функцией datediff, которую я не понимаю, как использовать в этом сценарии.
Ответы 2
Чтобы рассчитать время работы насоса в каждом состоянии (Работа, остановка) в минутах, вы можете использовать приведенный ниже запрос.
SELECT *
, DATEDIFF(minute, Date, LEAD(Date) OVER (ORDER BY Date)) AS Time
FROM {table}
Этот запрос будет подсчитывать разницу между двумя последовательными статусами, что даст вам такой результат:
01-01-24 00:15:00 |Running |70
01-01-24 01:25:00 |Stop |56
01-01-24 02:21:00 |Running |45234
01-02-24 12:15:00 |Stop |120
01-02-24 14:15:00 |Running |null
Теперь вы можете найти время для каждого статуса. Время вывода в минутах, как вы хотите.
DATEDIFF — функция SQL Server для поиска разницы во времени.
Параметры для DATEDIFF:
DATEDIFF(interval, time1, time2)
Для интервала вам необходимо передать желаемый тип вывода, в данном случае это minute.
Функция LEAD() используется для получения значения из строки, следующей за текущей.
Поэтому мы использовали функцию LEAD(), чтобы получить значение даты следующей строки.
Это отлично сработало, заменив функцию DATEDIFF() :)
Да, вы правы. Функция SQL-сервера DATEDIFF. TIMESTAMPDIFF работает в MYSQL, но не является функцией сервера sql.
В качестве решения:
CREATE TABLE "a table containing columns"
(Date DATETIME2(0),
PumpStatus VARCHAR(16)
);
INSERT INTO "a table containing columns" VALUES
('1/1/24 0:15', 'Running'),
('1/1/24 1:25', 'Stop'),
('1/1/24 2:21', 'Running'),
('1/2/24 12:15', 'Stop'),
('1/2/24 14:15', 'Running');
SELECT *, DATEDIFF(minute, "Date", COALESCE(LAG("Date") OVER (ORDER BY "Date" DESC), CURRENT_TIMESTAMP)) AS Duration_MN
FROM "a table containing columns"
ORDER BY "Date" DESC;
Вам понадобится оконная функция (например, lag), чтобы получить значение предыдущих строк, а затем dateiff, чтобы получить минуты.