basic sql : selecting AVG() values from the same c

问题:

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).

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:

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.

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

回答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)

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.

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