基本SQL:当每个所需的AVG()值使用不同的WHERE子句时,在一个查询中多次从同一列中选择AVG()值 [英] basic sql : selecting AVG() values from the same column multiple times in one query, when each wanted AVG() value uses different WHERE clause
问题描述
我想从一张表中的一列(value_to_count)中获得三个不同的平均值,其中所有这些平均值都具有不同的WHERE子句(根据时间).
I want to get three different average values from one column (value_to_count) inside one table where all of those average values has a different WHERE clause (according to time).
###services#### Table
service_id value_to_count time
----------- ----------------------- ---------
604 2054 04:04:50
604 3444 05:00:15
604 2122 07:12:50
604 2144 09:10:50
604 2001 13:12:53
602 2011 15:00:12
602 2115 17:22:35
602 1411 20:22:12
602 1611 21:04:52
602 2111 23:43:45
我现在正在使用此查询来获取18到23之间的平均值:
I'm using this query at the moment to get the average value on time between 18 and 23:
SELECT
service_id AS service, AVG(value_to_count) AS primetime
FROM services
WHERE HOUR(time) BETWEEN 18 AND 23
GROUP BY service_id
它给了我这样的结果:
### Result ####
service primetime
----------- --------------
604 2154
602 2444
现在,我想获得除已经获得的平均值以外的其他平均值.这次我只想获取"06和18之间的小时(时间)"和"23和06之间的小时(时间)"的平均值.
Now I want to get other average values next to the one I already got. This time I just want to get averages by 'HOUR(time) BETWEEN 06 AND 18' and 'HOUR(time) BETWEEN 23 AND 06' aswell.
这是我想要获得的结果形式:
This is the form of result I want to get:
### Wanted Result ####
service primetime other_time_interval_1 other_time_interval_2
----------- -------------- ---------------- ------------------
604 2154 2352 1842
602 2444 4122 1224
推荐答案
这应该做到:
SELECT service_id AS service,
AVG(case when HOUR(time) BETWEEN 18 AND 23 then value_to_count else null end) AS primetime,
AVG(case when HOUR(time) BETWEEN 06 AND 18 then value_to_count else null end) AS other_time_interval_1
FROM services
GROUP BY service_id
这篇关于基本SQL:当每个所需的AVG()值使用不同的WHERE子句时,在一个查询中多次从同一列中选择AVG()值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!