Выберите значение столбца, где другой столбец является максимальным или минимальным

У меня есть таблица, которая выглядит так:

user      timestamp1                     timestamp2
a         2020-10-07T15:36:01.323Z       2020-10-07T15:36:01.323Z
b         2020-10-07T15:36:01.323Z       2020-10-07T15:36:01.323Z

Я могу рассчитать максимальную или минимальную разницу во времени следующим образом:

min((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS min
max((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS max

но как я могу сформулировать запрос sql (sqlite) таким образом, чтобы я мог извлечь имя пользователя + минимальное максимальное значение в таблице?

Окончательный результат может выглядеть так:

type    user     time
max     a        679..
min     b        12..

Я пробовал что-то вроде этого, чтобы просто проверить:

"select user, ((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) AS val from mytable where ((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) == min((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) 

Я надеялся, что это вернет имя пользователя и минимальное значение, что-то вроде этого:

user    val
a       12...

но я получаю сообщение об ошибке:

PandaSQLException: (sqlite3.OperationalError) misuse of aggregate function min()
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
1
0
21
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Самым простым решением было бы использовать агрегатные функции MIN() и MAX() в двух отдельных запросах и UNION ALL для результатов:

SELECT 'max' type, user, MAX((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) time FROM tablename
UNION ALL
SELECT 'min' type, user, MIN((JULIANDAY(timestamp2) - JULIANDAY(timestamp1)) * 24) time FROM tablename

Этот запрос использует функцию SQLite голые столбцы.

Я бы также предложил использовать strftime() вместо JULIANDAY(), потому что это обеспечит лучшую точность:

SELECT 'max' type, user, MAX((strftime('%s', timestamp2) - strftime('%s', timestamp1)) / 3600.0) time FROM tablename
UNION ALL
SELECT 'min' type, user, MIN((strftime('%s', timestamp2) - strftime('%s', timestamp1)) / 3600.0) time FROM tablename

Смотрите демо.

можете ли вы объяснить, почему мы используем %s в функции. Что такое исходная единица и почему мы делим на 3600

x89 10.04.2022 13:11

@x89 strftime('%s', some_timestamp) возвращает количество секунд, прошедших с «1970-01-01 00:00:00» до some_timestamp. Деление на 3600 вернет часы. Вы можете найти больше здесь: sqlite.org/lang_datefunc.html

forpas 10.04.2022 13:14

Другие вопросы по теме