基本sql：在一个查询中多次从同一列中选择AVG（）值，当每个想要的AVG（）值使用不同的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).

我想从一个表中的一个列(value_to_count)中获得三个不同的平均值,其中所有这些平均值具有不同的WHERE子句(根据时间)。

Example Data:

###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

I'm using this query at the moment to get the average value on time between 18 and 23:

我现在正在使用此查询来获得18到23之间的平均值:

Query

SELECT 
service_id AS service, AVG(value_to_count) AS primetime 
FROM services 
WHERE HOUR(time) BETWEEN 18 AND 23 
GROUP BY service_id

And it gives me this kind of results:

它给了我这样的结果:

### Result #### 
service          primetime
-----------      --------------      
     604               2154           
     602               2444

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.

现在我想得到我已经得到的其他平均值。这次我只想通过'时间'和06和06之间的'小时(时间)'以及在23和06之间的'小时(时间)'获得平均值。

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

2 个解决方案

#1

This should do it:

这应该这样做:

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

#2

I'd first define the periods for each piece of data, then worry about grouping and averaging:

我首先定义每个数据的周期,然后担心分组和平均:

;With ServicePeriods as (
    SELECT Service_id,value_to_count,
       CASE WHEN HOUR(time) between 18 and 22 THEN 1
            WHEN HOUR(time) between 06 and 17 THEN 2
            ELSE 3 END as period
    FROM Services
)
select Service_Id,
    AVG(CASE WHEN period=1 THEN value_to_count END) as prime_time,
    AVG(CASE WHEN period=2 THEN value_to_count END) as other_time_interval_1,
    AVG(CASE WHEN period=3 THEN value_to_count END) as other_time_interval_2
from
    ServicePeriods
group by Service_id

(Actually, at first I did it as outputting as 3 separate rows, but now I've pivoted as per your requested results)

(实际上,起初我把它作为3个单独的行输出,但现在我按照你要求的结果进行了调整)

Because my first CASE covers all periods, I don't need to think about "between 23 and 06". Note that I've also adjusted the ranges to avoid double counting.

因为我的第一个CASE涵盖了所有时期,所以我不需要考虑“23到06之间”。请注意,我还调整了范围以避免重复计算。

If you want to explicitly check for HOUR(time) between 23 and 06 (or 05), you can do HOUR(time) >= 23 or HOUR(time) <= 6 (or <). Note that you want OR, not AND

如果要明确检查23到06(或05)之间的HOUR(时间),可以执行HOUR(时间)> = 23或HOUR(时间)<= 6(或<)。请注意,您想要OR,而不是AND

#1