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