Выберите значение столбца, где другой столбец является максимальным или минимальным
У меня есть таблица, которая выглядит так:
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()
Ответы 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
Смотрите демо.
@x89 strftime('%s', some_timestamp) возвращает количество секунд, прошедших с «1970-01-01 00:00:00» до some_timestamp. Деление на 3600 вернет часы. Вы можете найти больше здесь: sqlite.org/lang_datefunc.html
можете ли вы объяснить, почему мы используем %s в функции. Что такое исходная единица и почему мы делим на 3600