MySQL，获取一周中所有天的平均值

Question

我有一个表，其中包含我所在位置的某个地点每小时不可用或免费的数据。我当前的查询获取一整天的平均入住率/空闲位置:

SELECT AVG(sz.occupied), AVG(sz.free)
FROM `hourly_cache` AS sz
WHERE   sz.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-02 00:00:00'
        AND HOUR(sz.time) BETWEEN 8 AND 22
        AND libID = 0

但是，如果我不仅要获取这一天的平均值，还要获取一周中每隔一天的平均值怎么办？说:我想要本周星期一加上星期二、星期三等的平均值。

我如何循环遍历每一天并根据今天是哪一天运行此查询？

我想用 PHP 来做，但想知道是否有一种方法适用于 MySQL？

Answer 1

可能有太多解决方案，具体取决于业务逻辑以及您希望如何呈现它。

1)将time字段分配给date和time，然后按date分组:

SELECT AVG(sz.occupied), AVG(sz.free), date, time
FROM `hourly_cache` AS sz
WHERE   
  (sz.date >= '2017-01-01' AND sz.date < '2017-01-07')
  AND HOUR(sz.time) BETWEEN 8 AND 22
  AND libID = 0
GROUP BY date;

2) 将时间转换为日期并按日期分组:

SELECT AVG(sz.occupied), AVG(sz.free), DATE(sz.time) as date
FROM `hourly_cache` AS sz
WHERE   
  (sz.time >= '2017-01-01 00:00:00' AND sz.time < '2017-01-07 00:00:00')
  AND HOUR(sz.time) BETWEEN 8 AND 22
  AND libID = 0
GROUP BY date;

3) 使用DAYOFWEEK函数

SELECT AVG(sz.occupied), AVG(sz.free), DAYOFWEEK(sz.time) as day_of_week
FROM `hourly_cache` AS sz
WHERE   
  (sz.time >= '2017-01-01 00:00:00' AND sz.time < '2017-01-07 00:00:00')
  AND HOUR(sz.time) BETWEEN 8 AND 22
  AND libID = 0
GROUP BY day_of_week;

4) 在hourly_cache表中添加day_of_week和week_num字段，设置插入或更新时。

然后像这样查询:

SELECT AVG(sz.occupied), AVG(sz.free), week_num, day_of_week
FROM `hourly_cache` AS sz
WHERE   
  (sz.time >= '2017-01-01 00:00:00' AND sz.time < '2017-01-07 00:00:00')
  AND HOUR(sz.time) BETWEEN 8 AND 22
  AND libID = 0
GROUP BY week_num, day_of_week;

5)如果你不能改变表结构你可以创建一个 View (但我不确定性能):

CREATE VIEW v_avarage_seats_by_date AS 
  SELECT 
    AVG(sz.occupied) as occupied, 
    AVG(sz.free) as free, 
    DATE(sz.time) as date,
    DAYOFWEEK(sz.time) as day_of_week
  FROM `hourly_cache` AS sz
  WHERE   
    (HOUR(sz.time) BETWEEN 8 AND 22) AND libID = 0
  GROUP BY date;

然后像这样查询:

SELECT * FROM v_avarage_seats_by_date WHERE date >= '2017-01-01' AND date =< '2017-01-07'