MySQL：查询每周获取日志条目数——包括空集

发布于 2024-09-09 17:13:17 字数 376 浏览 2 评论 0原文

我的网络应用程序有一个报告，显示特定用户每周的登录次数。然而，我正在努力让查询正确。我遇到的问题是，我似乎无法获取用户未登录的周数。

目前我的查询如下所示：

SELECT
    DATE_ADD(logDate, INTERVAL(1-DAYOFWEEK(logDate)) DAY) weekStart,
    DATE_ADD(logDate, INTERVAL(7-DAYOFWEEK(logDate)) DAY) weekEnd,
    COUNT(*) loginCount
FROM log
WHERE
    logDate > $startDate AND
    logDate < $endDate

原文

My web application has a report that shows the number of logins by a particular user each week. However, I'm struggling to get the query just right. The problem I'm running into is that I can't seem to get the weeks during which the user did not login at all.

Currently my query looks like this:

SELECT
    DATE_ADD(logDate, INTERVAL(1-DAYOFWEEK(logDate)) DAY) weekStart,
    DATE_ADD(logDate, INTERVAL(7-DAYOFWEEK(logDate)) DAY) weekEnd,
    COUNT(*) loginCount
FROM log
WHERE
    logDate > $startDate AND
    logDate < $endDate

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

缘字诀 2024-09-16 17:13:17

我将创建一个周数表：

CREATE TEMPORARY TABLE weeks (weeknum INT PRIMARY KEY);

然后用整数值 0..53 填充该表。

然后使用 WEEK()函数：

SELECT weeks.weeknum, COUNT(*) loginCount
FROM weeks LEFT OUTER JOIN log ON weeks.weeknum = WEEK(log.logDate)
WHERE log.logDate BETWEEN ? AND ?
GROUP BY weeks.weeknum;

如果需要此查询支持跨越多年的日期范围，请使用YEARWEEK() 相反，并用更多行填充临时表YYYYWW 格式类似于 YEARWEEK() 返回的值。

I would create a table for week numbers:

CREATE TEMPORARY TABLE weeks (weeknum INT PRIMARY KEY);

Then populate that table with integer values 0..53.

Then join it to your log table using the WEEK() function:

SELECT weeks.weeknum, COUNT(*) loginCount
FROM weeks LEFT OUTER JOIN log ON weeks.weeknum = WEEK(log.logDate)
WHERE log.logDate BETWEEN ? AND ?
GROUP BY weeks.weeknum;

If you need this query to support a date range that spans multiple years, use YEARWEEK() instead, and populate your temp table with more rows in the YYYYWW format like values returned by YEARWEEK().

回复收藏 0 原文

~没有更多了~