Блог

Главная - Вопросы по программированию - Функция SQL для получения суммы значений столбцов datetime для каждого идентификатора

Функция SQL для получения суммы значений столбцов datetime для каждого идентификатора

#sql #sql-server #database #sql-function

#sql #sql-сервер #База данных #sql-функция

Вопрос:

У меня есть идентификатор столбца, который имеет значения 10,20,10,10,60,60 и так далее. У меня есть другой столбец, который является diff (modified_date — created_date) как TS, который имеет значения datetime, такие как 6 5:28:13.0 (6 дней), 1 1:34:54.0 и так далее для каждого идентификатора. Как я могу получить общее количество дней и время для каждого значения идентификатора? Например: как я могу получить общее значение TS для ID = 10?

пример изображения данных

Из изображения: для каждого значения eowstage_nbr столбца, как я могу получить общее количество дней и времени в столбце TS ?

Комментарии:

1. Пожалуйста, предоставьте образцы данных и желаемые результаты в виде текстовых таблиц . Кроме того, отметьте используемую вами базу данных.

2. хотелось бы видеть ваши усилия в форме запросов, которые вы пробовали

3. @Codeek — во-первых, могут ли складываться два значения в формате datetime? я пробовал функцию avg, но возвращает мне ошибку типа данных.

4. @sree если приведенные ниже ответы помогут вам, тогда было бы неплохо пометить их как ответ и закрыть вопрос. Если они не сообщают нам, чего им не хватает для вашей проблемы

Ответ №1:

Регулярная sum() агрегация по столбцу ID в сочетании с некоторыми преобразованиями типов данных должна сделать это…

Пример данных

 create table data
(
  id int,
  created datetime,
  updated datetime
);

insert into data (id, created, updated) values
(10, '2020-01-01 00:00:00', '2020-01-01 05:00:10'),
(10, '2020-01-02 00:00:00', '2020-01-03 11:00:03'),
(20, '2020-02-05 00:00:00', '2020-02-05 01:02:03'),
(20, '2020-02-15 00:10:00', '2020-02-17 00:00:10');

Решение

Описание шагов на простом английском языке будет звучать как:

  1. Возьмите разницу между created updated меткой времени и, выраженную в секундах;
  2. добавьте их в секундах для каждого id ;
  3. преобразуйте секунды в подходящий формат даты.

Я позаботился о шаге 3, добавив секунды до контрольной даты 1900-01-01 .

Версия запроса:

 select d.id,
       dateadd(second, sum(datediff(second, d.created, d.updated)), '1900-01-01 00:00:00') as diff_sum
from data d
group by d.id;

Результат

Значения не отформатированы, как на вашем скриншоте, но это должно сводиться к выбору типа данных, форматированию даты и времени и локализации.

 id diff_sum
-- -----------------------
10 1900-01-02 16:00:13.000
20 1900-01-03 00:52:13.000

Скрипка

Ответ №2:

Поскольку вы не предоставили никаких выборочных данных и, глядя на ваш образ, я предполагаю, что у вас есть данные в столбце TS в соответствии с вашим изображением. (Я использовал фиктивные данные для репликации вашего столбца TS). Итак, шаги будут заключаться в том, чтобы отделить день от времени в вашем столбце TS, затем преобразовать это время в секунды и выполнить суммирование этих секунд и дней отдельно, и, наконец, объединить дни и секунды, преобразованные в правильный формат d hh: mm: ss.

 select ID, 
cast((sum(noofdays)   SUM(DATEDIFF(S, '00:00:00', noofhrs))/3600/24) as varchar(5))   ' '   
right('00' CAST(SUM(DATEDIFF(S, '00:00:00', noofhrs))/3600%24 AS VARCHAR(2)),2)   ':'
  right('00' CAST(SUM(DATEDIFF(S, '00:00:00', noofhrs))%3600/60 AS VARCHAR(2)),2)   ':'
  right('00' CAST(((SUM(DATEDIFF(S, '00:00:00', noofhrs))%3600)%60) AS VARCHAR(2)),2)
from 
(
 select ID, case left(TS,charindex(' ',TS)) when '' then 0 else left(TS,charindex(' ',TS)) end as noofdays,
cast(right(TS,len(TS)-charindex(' ',TS)) as datetime) as noofhrs from 
(
select 1 ID,'6 5:28:13.0' TS union
select 1,'1 8:28:13.0' a union
select 1,'2 12:28:13.0' a union
select 2,'5:28:13.0' a union
select 2,'6 5:28:13.0' a
)
tablename
) A
group by ID