php - 查找具有静态最大日期和最小日期的每日 session 的平均值

Question

PHP 5.X，MySQL 5.7.15

获取按星期几分组的数据，以使用最大日期和最小日期提供每日使用的平均 session 数。在此场景中，最大日期和最小日期应用于星期几，这导致每种情况下的周数不同。

构建这些结果的查询定义为下面的查询 1。本质上，最后 4 列中的信息应该是静态的，我使用 SELF JOIN 完成了这一点，下面将详细介绍。在该示例中，session_count 爆炸了，因为我只在开发环境中工作。

那么出了什么问题，为什么？

  session_count | weekday | maxDate             |minDate              | numOfDayDiff| numOfWeeks
'29'            | 'Friday'|'2017-03-10 12:16:47'|'2016-08-12 12:31:28'| '210'       | '30'
'26'            |'Monday' |'2017-03-06 17:10:59'|'2016-08-08 14:31:16'| '210'       | '30'
'6'            |'Saturday'|'2017-03-04 23:26:12'|'2016-08-20 23:10:47'| '196'       | '28'
'10'            |'Sunday' |'2017-03-12 18:28:51'|'2016-08-14 16:26:30'| '210'       | '30'
'22'           |'Thursday'|'2017-03-09 15:45:27'|'2016-06-30 12:42:19'| '252'       | '36'
'22'            |'Tuesday'|'2017-03-21 15:00:08'|'2016-06-28 19:51:21'| '266'       | '38'
'22'          |'Wednesday'|'2017-03-01 23:57:34'|'2016-07-06 16:17:39'| '238'       | '34'

我希望最大日期和最小日期是静态的。我尝试使用 Self JOIN Syntax 的 w3schools 解释

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

我尝试应用此查询，maxDate、minDate、numDaysDiff 和 numOfWeeks 与上述最高周数的值一致，但 session_count 的统计数据爆炸了。

这是获取上表的查询 1:

SELECT
CEIL(COUNT(ss.session_id) / (FLOOR (DATEDIFF(MAX(ss.date),MIN(ss.date))/7))) as session_count,
DAYNAME((ss.date)) as weekday,
MAX(ss.date) as maxDate,
MIN(ss.date) as minDate,
DATEDIFF(MAX(ss.date),MIN(ss.date)) as numOfDayDiff,
FLOOR(DATEDIFF(MAX(ss.date),MIN(ss.date))/7) as numOfWeeks
FROM  session ss
JOIN user u ON ss.user = u.id
JOIN account a on u.account = a.id
WHERE a.isTest=false
GROUP BY weekday
ORDER BY weekday;

这里是使用SELF JOIN语法修改的Q-2，后跟输出表:

SELECT
CEIL(COUNT(ss.session_id) / (FLOOR (DATEDIFF(MAX(xx.date),MIN(xx.date))/7))) as session_count,
DAYNAME((ss.date)) as weekday,
MAX(xx.date) as maxDate,
MIN(xx.date) as minDate,
DATEDIFF(MAX(xx.date),MIN(xx.date)) as numOfDayDiff,
FLOOR(DATEDIFF(MAX(xx.date),MIN(xx.date))/7) as numOfWeeks
FROM  session xx, session ss
JOIN user u ON ss.user = u.id
JOIN account a on u.account = a.id
WHERE a.isTest=false
GROUP BY weekday
ORDER BY weekday;

结果是:

session_count | weekday | maxDate             |minDate              | numOfDayDiff| numOfWeeks
99357         |Friday   |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
88062         |Monday   |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
16829         |Saturday |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
32505         |Sunday   |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
90136         |Thursday |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
94516         |Tuesday  |2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38
83451         |Wednesday|2017-03-21 15:00:08  |2016-06-28 19:51:21  |266          |38

任何人都可以告诉我我是如何犯错误的吗？我将看看是否可以创建一个 mysql fiddle 并附加到此处作为示例。

Answer 1

为了减轻数据库压力，我决定使用查询 1 的修改版本，并使用结果来确定哪一个具有最长的周数，并将计数除以周数在 PHP 中。

这是修改后的查询1

SELECT
COUNT(ss.session_id) as session_count,
DAYNAME((ss.date)) as weekday,
MAX(ss.date) as maxDate,
MIN(ss.date) as minDate,
CEIL(DATEDIFF(MAX(ss.date),MIN(ss.date))/7) as numOfWeeks
FROM  session ss
JOIN user u ON ss.user = u.id
JOIN account a on u.account = a.id
WHERE a.isTest=false
GROUP BY weekday
ORDER BY weekday;

session_count | weekday | maxDate            | minDate           | numOfWeeks
2017          | Friday  |2017-03-17 23:39:56 |2016-09-09 01:06:36|  27
1908          | Monday  |2017-03-20 23:59:53 |2016-09-12 14:52:29|  27
943           | Saturday|2017-03-18 23:39:14 |2016-09-17 14:49:56|  26
825           | Sunday  |2017-03-19 23:56:37 |2016-09-18 22:28:45|  26
2429          | Thursday|2017-03-16 23:51:23 |2016-09-08 12:02:54|  27
2341          | Tuesday |2017-03-21 23:52:20 |2016-09-06 12:06:39|  28
2569          |Wednesday|2017-03-22 17:03:16 |2016-09-07 02:43:10|  28

注意因数据库不同，数据有所不同。