mysql - 仅使用 MySQL 查询即可检索复杂数据(本周各天的总访问量)

Question

我花了几天时间尝试一种仅使用 MySQL(而不是 MySQL + PHP)来完成此操作的方法，但我不太擅长 SQL 查询。我的表格的想法是这样的(简短版本):

每当用户访问我的网站时，都会在该表中添加一行，包括访问的时间日期和访问类型(对于这个特定问题，我只需要输入“profile_visit”。我需要提供一个包含以下内容的图表:本周每天的总访问量。

所以，我需要检索一个数组，例如 Mon(2) Tue(0) Wed(1) 等等。您认为仅使用 MySQL 查询可以做到这一点吗？感谢您的帮助!

Answer 1

SELECT
  COUNT(DAYOFWEEK(`datetime`) = 1 OR NULL) AS Sun,
  COUNT(DAYOFWEEK(`datetime`) = 2 OR NULL) AS Mon,
  COUNT(DAYOFWEEK(`datetime`) = 3 OR NULL) AS Tue,
  COUNT(DAYOFWEEK(`datetime`) = 4 OR NULL) AS Wed,
  COUNT(DAYOFWEEK(`datetime`) = 5 OR NULL) AS Thu,
  COUNT(DAYOFWEEK(`datetime`) = 6 OR NULL) AS Fri,
  COUNT(DAYOFWEEK(`datetime`) = 7 OR NULL) AS Sat
FROM atable
WHERE `type` = 'profile_visit'
  AND `datetime` >= CURRENT_DATE() - INTERVAL (DAYOFWEEK(CURRENT_DATE()) - 1) DAY
  AND `datetime` <  CURRENT_DATE() + INTERVAL 1 DAY

而不是

COUNT(DAYOFWEEK(`datetime`) = 1 OR NULL)

你也可以使用

SUM(DAYOFWEEK(`datetime`) = 1)

当涉及计数时，我倾向于使用 COUNT，但这只是个人喜好。这两种方法返回的结果没有区别，而且在性能方面也应该没有区别。

COUNT 表达式的 OR NULL 部分解释如下:

• > Why do I need "OR NULL" in MySQL when counting rows with a condition

<小时/>

更新

从周一开始的几周内，请尝试执行与上述脚本等效的以下操作:

SELECT
  COUNT(WEEKDAY(`datetime`) = 0 OR NULL) AS Mon,
  COUNT(WEEKDAY(`datetime`) = 1 OR NULL) AS Tue,
  COUNT(WEEKDAY(`datetime`) = 2 OR NULL) AS Wed,
  COUNT(WEEKDAY(`datetime`) = 3 OR NULL) AS Thu,
  COUNT(WEEKDAY(`datetime`) = 4 OR NULL) AS Fri,
  COUNT(WEEKDAY(`datetime`) = 5 OR NULL) AS Sat,
  COUNT(WEEKDAY(`datetime`) = 6 OR NULL) AS Sun
FROM atable
WHERE `type` = 'profile_visit'
  AND `datetime` >= CURRENT_DATE() - INTERVAL ( WEEKDAY(CURRENT_DATE()) ) DAY
  AND `datetime` <  CURRENT_DATE() + INTERVAL 1 DAY

引用文献:

• DAYOFWEEK() (MySQL)

• WEEKDAY() (MySQL)