如何获取分组数据的统计信息

nrerum 发布于 2019-02-11 mysql 最后更新 2019-02-11 21:14 9 浏览

我有一个客户访问表,其中包含日期栏和客户信息栏。

+------+---------------------+------+
| id   | checkin             | name | ...
+------+---------------------+------+
|    1 | 2010-01-01 00:12:00 | Joe  | 
|    2 | 2010-01-01 00:18:00 | John |
|    3 | 2010-01-01 00:22:00 | Jane |
...
我想在一周中的某一天获取统计数据,即按星期几列出的平均,最小和最大访问次数。就像是:
+---------+---------+---------+
| day     | average | minimum | ...
+---------+---------+---------+
| Monday  | 45      | 30      | 
| Tuesday | 60      | 35      |
...
我相信我需要使用子查询或加入才能做到这一点,但无法弄清楚。 该查询将每天的访问总数
SELECT dayname(checkin) as day, count(*) as total
FROM customer_log
group by date(checkin)
然后在上面的结果我需要运行类似的东西
SELECT sum(total), max(total), min(total)
from {above table}
group by day.
谢谢!
已邀请:

psunt

赞同来自:

试试这个:

SELECT dayname(checkinDate) day,
  sum(visitsPerDay) totalVisits,
  avg(visitsPerDay) avgVisits,
  max(visitsPerDay) maxVisits,
  min(visitsPerDay) minVisits
FROM (
  SELECT date(checkin) checkinDate, count(*) visitsPerDay FROM Customer_log
  GROUP BY checkinDate
) AS visitsPerDaySub
GROUP BY dayofweek(checkinDate)