图表接口按日期的统计查询开发

需求

在图表统计时，经常会有要按日期去统计数据的情况，如统计每日的点击量，使用量，查看量等数据，通过查看一段时间内的连续数据来感知指标的趋势变化。

这图表的数据需要每天的数据，即使当天没有数据也要能汇总结果0.
SQL语句

以下示例基于示例的用户表：

1. CREATE TABLE `user`  (
2.   `id` bigint NOT NULL AUTO_INCREMENT,
3.   `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
4.   `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
5.   `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名称',
6.   `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
7.   `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
8.   `password` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
9.   `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否激活',
10.   `last_login` datetime(6) NULL DEFAULT NULL COMMENT '最后登录时间'
11. )

复制代码

nametypeDescriptionidbigintidcreated_atdatetimeupdated_atdatetimeusernamevarchar用户名称emailvarchar邮箱phonevarchar电话passwordvarchar密码is_activetinyint是否激活last_logindatetime最后登录时间使用CTE（Mysql 8.0以上）

Mysql 8 中增加了一个新特性 CTE（Common Table Expressions）通用表表达式，是一种命名的临时结果集，它只存在于单个 SQL 语句的执行范围内。你可以把它想象成一个临时视图，只在当前查询中有效。CTE 主要用于简化复杂的查询，提高可读性和可维护性。
CTE 使用 WITH 语句定义，其基本语法如下：

1. -- WITH: 声明 CTE 的关键字。
2. -- cte_name: CTE 的名称，必须符合 MySQL 的标识符命名规则。
3. -- AS: 关键字，用于将 CTE 的定义与名称关联起来。
4. -- SELECT ... FROM ... WHERE ...: 定义 CTE 的查询语句。这个查询语句的结果将被存储在 CTE 中。
5. -- SELECT ... FROM cte_name: 使用 CTE 的查询语句。这个语句可以从 CTE 中选择数据。
7. WITH cte_name AS (
8.     SELECT ... FROM ... WHERE ...
9. )
10. SELECT ... FROM cte_name;
12. -- 可以在一个 WITH 语句中定义多个 CTE，用逗号分隔：
13. WITH cte1 AS (
14.     SELECT ...
15. ),
16. cte2 AS (
17.     SELECT ... FROM cte1
18. )
19. SELECT ... FROM cte2;

复制代码

使用CTE获取每日注册用户量

1. WITH daily_registrations AS (
2.   SELECT
3.     DATE(created_at) AS register_date,        -- 按日期截取（去除时间部分）
4.     COUNT(*) AS registration_count           -- 统计当天注册用户数
5.   FROM user
6.   WHERE created_at IS NOT NULL
7.   GROUP BY DATE(created_at)
8. )
9. SELECT
10.   register_date,
11.   registration_count
12. FROM daily_registrations
13. ORDER BY register_date DESC;
15. -- daily_registrations 生成了个临时结果集给后续的查询使用，仅用来展示CTE的使用

复制代码

使用CTE获取每日注册用户量

1. -- 统计区间内注册
2. -- 设置变量（实际使用中可替换为传参，如存储过程或应用层参数）
3. -- SET @start_date = '2025-04-01';
4. -- SET @end_date   = '2025-04-10';
6. -- WITH RECURSIVE date_series AS (
7. --   -- 锚点：从指定开始日期出发
8. --   SELECT @start_date AS date
9. --   UNION ALL
10. --   -- 递归：逐日递增，直到结束日期
11. --   SELECT date + INTERVAL 1 DAY
12. --   FROM date_series
13. --   WHERE date < @end_date  -- 注意：如果想包含 @end_date，就用 <；若传进来的是 datetime，注意处理
14. -- )
16. -- 统计最早注册日到今天的注册
17. WITH RECURSIVE date_series AS (
18.   SELECT DATE(MIN(created_at)) AS date FROM user
19.   UNION ALL
20.   -- 递归生成下一天
21.   SELECT date + INTERVAL 1 DAY
22.   FROM date_series
23.   WHERE date < CURDATE()  -- 到昨天为止；若要包含今天，改为 <=
24. )
25. SELECT
26.   d.date AS reg_date,
27.   COUNT(u.id) AS reg_cnt  -- COUNT 本身忽略 NULL，无需 COALESCE
28. FROM date_series d
29. -- LEFT JOIN user u
30. --        ON DATE(u.created_at) = d.date
31. LEFT JOIN user u
32.        ON u.created_at >= d.date
33.       AND u.created_at < d.date + INTERVAL 1 DAY  -- 避免 DATE() 函数
34. GROUP BY d.date
35. ORDER BY d.date;

复制代码

优点：

• 一张表顶 N 张表， 能映射成日期，也能映射成小时、分钟、周号、批次号、页码。
  
• 无任何业务耦合，不会和业务字段纠缠。
  

echarts图表

echarts 配置

1. CREATE TABLE dim_calendar (
2.     day DATE PRIMARY KEY
3. );
5. -- 生成 2020-01-01 ~ 2030-12-31 共 4018 行
6. INSERT INTO dim_calendar (day)
7. SELECT DATE_ADD('2020-01-01', INTERVAL seq DAY)
8. FROM (
9.     SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS seq
10.     FROM
11.       (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
12.       (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
13.       (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
14.       (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
15. ) t
16. WHERE seq <= 4018;

复制代码

echarts dateset 格式配置

1. SELECT
2.   c.day                               AS reg_date,
3.   COALESCE(COUNT(u.id), 0)            AS reg_cnt
4. FROM dim_calendar c
5. LEFT JOIN `user` u
6.        ON DATE(u.created_at) = c.day
7. WHERE c.day BETWEEN DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND CURDATE()
8. GROUP BY c.day
9. ORDER BY c.day;

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！