时间戳

首页 > 解决方案 > MySQL，获取一周中所有日子的平均值

mysql - MySQL，获取一周中所有日子的平均值

问题描述

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

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 中工作？

标签： mysqlaggregateaverage

解决方案

根据业务逻辑和您希望如何呈现它，可能有太多的解决方案。


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）向表中添加day_of_week和week_num字段hourly_cache，在插入或更新时设置它们。

然后像这样查询它：

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）如果你不能改变表结构你可以创建一个视图（但我不确定性能）：

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'

推荐阅读