Erlo

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

2025-08-18 07:30:35 发布   43 浏览  
页面报错/反馈
收藏 点赞

需求

在图表统计时,经常会有要按日期去统计数据的情况,如统计每日的点击量,使用量,查看量等数据,通过查看一段时间内的连续数据来感知指标的趋势变化。
img
这图表的数据需要每天的数据,即使当天没有数据也要能汇总结果0.

SQL语句

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

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

使用CTE(Mysql 8.0以上)

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

CTE 使用 WITH 语句定义,其基本语法如下:

-- WITH: 声明 CTE 的关键字。
-- cte_name: CTE 的名称,必须符合 MySQL 的标识符命名规则。
-- AS: 关键字,用于将 CTE 的定义与名称关联起来。
-- SELECT ... FROM ... WHERE ...: 定义 CTE 的查询语句。这个查询语句的结果将被存储在 CTE 中。
-- SELECT ... FROM cte_name: 使用 CTE 的查询语句。这个语句可以从 CTE 中选择数据。

WITH cte_name AS (
    SELECT ... FROM ... WHERE ...
)
SELECT ... FROM cte_name;

-- 可以在一个 WITH 语句中定义多个 CTE,用逗号分隔:
WITH cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ... FROM cte1
)
SELECT ... FROM cte2;

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

WITH daily_registrations AS (
  SELECT 
    DATE(created_at) AS register_date,        -- 按日期截取(去除时间部分)
    COUNT(*) AS registration_count           -- 统计当天注册用户数
  FROM user
  WHERE created_at IS NOT NULL
  GROUP BY DATE(created_at)
)
SELECT 
  register_date,
  registration_count
FROM daily_registrations
ORDER BY register_date DESC;

-- daily_registrations 生成了个临时结果集给后续的查询使用,仅用来展示CTE的使用

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

-- 统计区间内注册
-- 设置变量(实际使用中可替换为传参,如存储过程或应用层参数)
-- SET @start_date = '2025-04-01';
-- SET @end_date   = '2025-04-10';

-- WITH RECURSIVE date_series AS (
--   -- 锚点:从指定开始日期出发
--   SELECT @start_date AS date
--   UNION ALL
--   -- 递归:逐日递增,直到结束日期
--   SELECT date + INTERVAL 1 DAY
--   FROM date_series
--   WHERE date = d.date 
      AND u.created_at 

注:created_at 上建立了索引, 则避免在索引列上使用函数,否则索引会失效
数据库必须对 每一行的 created_at 值计算 DATE(),然后比较
即使 created_at 有索引,也无法直接使用索引查找
因为索引存储的是原始 datetime 值(如 '2025-04-05 10:30:00')
而不是 DATE() 计算后的结果('2025-04-05')

数据量大且查询频繁时:
LEFT JOIN … ON u.created_at >= d.date AND u.created_at 仍会导致范围查找,如果只关心“天”而不关心时分秒,可额外冗余一个 created_date DATE 字段并建索引,可改成 ON u.created_date = d.date。

使用子查询

创建日历辅助表

1)一次性建表(只需执行一次)

CREATE TABLE dim_calendar (
    day DATE PRIMARY KEY
);

-- 生成 2020-01-01 ~ 2030-12-31 共 4018 行
INSERT INTO dim_calendar (day)
SELECT DATE_ADD('2020-01-01', INTERVAL seq DAY)
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS seq
    FROM
      (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,
      (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,
      (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,
      (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
) t
WHERE seq 

2)按日期统计(查询)

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

创建连续数字辅助表

1)一次性建表

CREATE TABLE numbers (n TINYINT PRIMARY KEY);

INSERT INTO numbers (n)
SELECT a.N + b.N * 10 AS n
FROM
  (SELECT 0 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,
  (SELECT 0 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;

2)按日期统计(查询)100天以内,要更多可创建更大的辅助表

SET @start_date := '2024-03-01';
SET @end_date   := '2024-03-31';

SELECT
  d.reg_date,
  COUNT(u.id) AS reg_cnt
FROM (
    SELECT DATE_ADD(@start_date, INTERVAL n DAY) AS reg_date
    FROM numbers
    WHERE n BETWEEN 0 AND DATEDIFF(@end_date, @start_date)
) d
LEFT JOIN `user` u ON u.created_at >= d.reg_date
                   AND u.created_at 

优点:

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

echarts图表

echarts 配置

// 模拟接口数据返回
const userLoginArray = [
  { date: '2025-07-01', count: 10 },
  { date: '2025-07-02', count: 0 },
  { date: '2025-07-03', count: 5 },
  { date: '2025-07-04', count: 1 },
  { date: '2025-07-05', count: 8 },
  { date: '2025-07-06', count: 6 }
];

const [categoryDate, dateCount] = userLoginArray.reduce(
  ([dates, counts], { date, count }) => [
    [...dates, date],
    [...counts, count]
  ],
  [[], []]
);

option = {
  xAxis: { type: 'category', data: categoryDate },
  yAxis: { type: 'value'},
  series: [
    { type: 'bar', data: dateCount}
  ]
};

echarts dateset 格式配置

const userLoginArray = [
  {date: '2025-07-01', count: 10 },
  {date: '2025-07-02', count: 0 },
  {date: '2025-07-03', count: 5 },
  {date: '2025-07-04', count: 1 },
  {date: '2025-07-05', count: 8 },
  {date: '2025-07-06', count: 6 },
]

option = {
  dataset: {
    dimensions: ['date', 'count'],
    source: userLoginArray,
  },
  xAxis: { type: 'category' },
  yAxis: { type: 'value'},
  series: [{ type: 'bar'}]
};

登录查看全部

参与评论

评论留言

还没有评论留言,赶紧来抢楼吧~~

手机查看

返回顶部

给这篇文章打个标签吧~

棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认