Как рассчитать продолжительность между метками времени для каждого пользователя в таблице, сгруппированной по дням?
У меня есть таблица транзакций, из которой я хочу получить данные. Таблица транзакций записывает временную метку для каждой записи, отправленной в таблицу, в дополнение к другим полям, включая пользователя, с которым она связана.
Я хочу, чтобы выходные данные группировали транзакции по дням для каждого человека, но я также хочу рассчитать продолжительность между каждой меткой времени для каждого конкретного пользователя. Я могу получить продолжительность между метками времени MAX и MIN за весь день для каждого человека, но я не могу получить продолжительность между каждой отдельной транзакцией за каждый день. Я безуспешно пробовал сценарии CTE с использованием номеров строк. Вот рабочий скрипт, который тянет продолжительность на весь день:
SELECT
MAX(TRANSACTION_DATE),
MIN(TRANSACTION_DATE),
[TRANSACTION_DESCRIPTION],
[TRANSACTION_LOCATION],
[TRANSACTION_ID],
[LAST_NAME],
[FIRST_NAME],
DATEDIFF(HOUR,MIN(TRANSACTION_DATE),MAX(TRANSACTION_DATE)) AS [DURATION]
FROM [DB].[DBO].[TRANSACTION_TABLE]
GROUP BY
CAST(TRANSACTION_DATE AS DATE),
[TRANSACTION_DESCRIPTION],
[TRANSACTION_LOCATION],
[TRANSACTION_ID],
[LAST_NAME],
[FIRST_NAME]
ORDER BY
[LAST_NAME]
SQL Server <> MySQL и MySQL не используют T-SQL. Я удалил конфликтующие теги; пожалуйста отредактируйте свой вопрос, чтобы (пере) пометить фактическую (R) СУБД, которую вы используете. Почему пометка нескольких продуктов РСУБД делает мой вопрос неясным?
Если вы используете SQL Server, то timestamp (rowversion) вам не поможет; это binary(8) ценность, не имеющая ничего общего со временем.
Возможно, эта рабочий пример поможет dbfiddle.uk/hY55gE3P
@BartMcEndree Да, это помогло. Вывод - это то, что я получаю, но я ищу продолжительность между всеми транзакциями за каждый день для каждого пользователя (а не MAX и MIN). Итак, для Боба Смита я бы хотел увидеть продолжительность между 1-й и 2-й транзакциями, а затем 2-ю и 3-ю транзакцию для 1/1.
Будьте осторожны с датой, особенно на уровне часов. Он не возвращает количество часов, прошедших между двумя датами. Вместо этого он возвращает количество пересечений часовых порогов. Например, dateiff для часа вернет 1, когда минута равна 59 и начинается следующая минута. select datediff(hour, '20240809 10:59', '20240809 11:00')
Ответы 1
Возможно, вам будет полезно использовать LAG вместо группировки по дням.
SELECT
TRANSACTION_DATE,
[TRANSACTION_DESCRIPTION],
[TRANSACTION_LOCATION],
[TRANSACTION_ID],
[LAST_NAME],
[FIRST_NAME],
DATEDIFF(HOUR,LAG(TRANSACTION_DATE) OVER (PARTITION BY [LAST_NAME] ORDER BY [LAST_NAME],TRANSACTION_DATE),TRANSACTION_DATE) AS [DURATION]
FROM [TRANSACTION_TABLE]
ORDER BY
[LAST_NAME],TRANSACTION_DATE
Это сработало, если я установил дату транзакции WHERE [TRANSACTION_DATE] >= CAST(GETDATE() AS DATE)). Если я хочу выполнить запрос по нескольким датам, есть ли способ сбросить продолжительность до 0 для каждого дня для каждого человека? Прямо сейчас, если я выполняю МЕЖДУ через несколько дней, продолжительность будет текущим подсчетом предыдущего дня для первой транзакции.
Попробуйте добавить дату в раздел LAG. [Last_Name], cast(TRANSACTION_DATE как DATE)
Предоставьте примеры данных и таблицу, показывающую ожидаемый результат.