查询最近7天每一天的数据（包括今天）

最新推荐文章于 2024-08-12 03:07:08 发布

转载最新推荐文章于 2024-08-12 03:07:08 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/Johnson8888/p/15812636.html

文章标签：

#sql #mysql #数据库

MySQL 专栏收录该内容

11 篇文章

订阅专栏

该SQL查询从当前日期回溯7天，获取每天的日期，并通过LEFTJOIN与sys_log表结合，计算每一天的页面浏览量(PV)和独立访客数(UV)。通过对sys_log表中的created_time按日期分组并计数，得出流量数据。

SELECT a.date,
IFNULL(pv, 0) as pv,
IFNULL(uv, 0) as uv
FROM (
SELECT date_sub(curdate(), INTERVAL 6 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 5 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 4 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 3 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 2 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 1 DAY) AS date
UNION ALL
SELECT date_sub(curdate(), INTERVAL 0 DAY) AS date
) a
LEFT JOIN (
SELECT date(created_time) AS datetime,
count(ip_address) AS pv,
COUNT(DISTINCT ip_address) AS uv
FROM sys_log
GROUP BY date(created_time)
) b ON a.date = b.datetime;