Средняя разница Oracle между двумя полями даты
У меня есть такая таблица в базе данных:
unit arrival_date departure_date
---- ------------- --------------
1 27/1/2017 08:01:20 a. m. 27/1/2017 08:04:27 a. m.
1 27/1/2017 08:05:35 a. m. 27/1/2017 08:09:28 a. m.
Мне нужно рассчитать среднюю разницу во времени между прибытиями пользователей и датами отправления и показать результат в формате часов, минут и секунд (ЧЧ: МИ: СС).
Если бы я сделал этот arrival_date - departure_date
Я получаю результат в течение нескольких дней, но мне трудно получить среднее значение в часах, минутах и секундах.
Поля являются полями DATE, а не полями TIMESTAMP.
Заранее спасибо.
Ответы 2
select unit, avg(departure_date - arrival_date) as avg_date
from mytable
Мне этот ответ кажется нормальным.
Вот пример.
При вычитании двух значений типа данных даты результатом будет количество дней. Он показывает INTER CTE. Если умножить его на 24 (количество часов в дне), 60 (количество минут в час) и 60 (количество секунд в минуте), то получится количество секунд (DIFF_SECS).
AVERAGES CTE показывает, как применить функцию AVG к предыдущим результатам; В этом нет ничего специальный, просто обратите внимание, что у вас есть GROUP, это BY столбец UNIT.
Наконец, примените форматирование TO_CHAR к расчету (некоторые вызовы TRUNC и MOD для извлечения часов, минут и секунд из значения AVG_DIFF_SECS).
Я предлагаю вам запускать каждое CTE отдельно, шаг за шагом, чтобы легче было следить за выполнением. Надеюсь, поможет.
SQL> with test (unit, arr, dep) as
2 (select 1, to_date('27.01.2017 08:01:20', 'dd.mm.yyyy hh24:mi:ss'),
3 to_date('27.01.2017 08:04:27', 'dd.mm.yyyy hh24:Mi:ss')
4 from dual union all
5 select 1, to_date('27.01.2017 08:05:35', 'dd.mm.yyyy hh24:mi:ss'),
6 to_date('27.01.2017 08:09:28', 'dd.mm.yyyy hh24:Mi:ss')
7 from dual
8 ),
9 inter as
10 (select unit, (dep - arr) diff_days,
11 (dep - arr) * 24 * 60 * 60 diff_secs
12 from test
13 ),
14 averages as
15 (select unit,
16 avg(dep - arr) avg_diff_days,
17 avg((dep - arr) * 24 * 60 * 60) avg_diff_secs
18 from test
19 group by unit
20 )
21 select
22 to_char(trunc(avg_diff_secs / 3600), 'fm00') || ':' || -- hours
23 to_char(trunc(mod(avg_diff_secs , 3600) / 60), 'fm00') || ':' || -- minutes
24 to_char(mod(avg_diff_secs, 60), 'fm00') -- seconds
25 avg_diff_formatted
26 from averages;
AVG_DIFF_FORMATTED
--------------------
00:03:30
SQL>
выполнить запрос и сообщить мне ???