SQL: в среднем в час, между временами, в течение нескольких дней

У меня есть таблица со столбцами START_DATE и END_DATE.

Я хочу получить среднюю разницу во времени между этими временными метками для каждого часа, но только между часами с 9:00 до 18:00 в течение последних нескольких дней. Я хотел бы, чтобы вывод выглядел так:

elapsed  hour
-------------
2.5      11 <--today at 11AM
1.7      10
2.4      9
1.9      18 <--this is yesterday
2.4      17
4.0      16

Я считаю, что я довольно близко, но я просто не могу заставить код работать. Вот что у меня есть:

SELECT 
    TRUNCATE(AVG(TIME_TO_SEC(TIMEDIFF(END_DATE, START_DATE))/60), 2) as elapsed,
    EXTRACT(HOUR FROM END_DATE) as hour
FROM 
    TIME_INFO
WHERE
    EXTRACT(HOUR FROM END_DATE) BETWEEN 9 AND 18 AND
    DATE(END_DATE) > CURDATE() - INTERVAL 3 DAY
GROUP BY 
    EXTRACT(HOUR FROM END_DATE)
ORDER BY 
    END_DATE DESC

Это близко, но возвращает только то, что я хочу, три дня назад, а не через несколько дней, как хотелось бы. Я использую mySQL 5.0, у кого-нибудь есть идеи?


mysql sql group-by
person Yottagray    schedule 25.03.2011    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Если вы хотите несколько дней, вам понадобится день, а также час в предложении GROUP BY. В настоящее время он усредняет все значения за один и тот же час в течение нескольких дней. (Значения сегодняшних 11, вчерашних 11 и т. д. усредняются вместе)

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

SELECT 
    TRUNCATE(AVG(TIME_TO_SEC(TIMEDIFF(END_DATE, START_DATE))/60), 2) as elapsed,
    EXTRACT(DAY FROM END_DATE) as day,
    EXTRACT(HOUR FROM END_DATE) as hour
FROM 
    TIME_INFO
WHERE
    EXTRACT(HOUR FROM END_DATE) BETWEEN 9 AND 18 AND
    DATE(END_DATE) > CURDATE() - INTERVAL 3 DAY
GROUP BY 
    EXTRACT(DAY FROM END_DATE),
    EXTRACT(HOUR FROM END_DATE)
ORDER BY 
    END_DATE DESC

Если вы хотите, чтобы он работал через месяц/год, вам, вероятно, понадобится DATE(END_DATE), а не EXTRACT(DAY FROM END_DATE), и в этом случае вывод может выглядеть примерно так:

elapsed  date        hour
------------------------- 
2.5      2011/03/25  11 <--today at 11AM
1.7      2011/03/25  10
2.4      2011/03/25  9
1.9      2011/03/24  18 <--this is yesterday
2.4      2011/03/24  17
4.0      2011/03/24  16
person Tim Sylvester    schedule 25.03.2011
comment
Да, это помогло. В конце концов, я разберусь с SQL, если буду продолжать получать такие отличные советы! Спасибо большое. - person Yottagray; 25.03.2011