图表接口按日期的统计查询开发
<h2 id="需求">需求</h2><p>在图表统计时，经常会有要按日期去统计数据的情况，如统计每日的点击量，使用量，查看量等数据，通过查看一段时间内的连续数据来感知指标的趋势变化。<br>
<img src="https://img2023.cnblogs.com/blog/1249408/202502/1249408-20250208171234746-133292395.png" alt="img" loading="lazy"><br>
这图表的数据需要每天的数据，<strong>即使当天没有数据也要能汇总结果0</strong>.</p>
<h2 id="sql语句">SQL语句</h2>
<p>以下示例基于示例的用户表：</p>
<pre><code class="language-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 '最后登录时间'
)
</code></pre>
<table>
<thead>
<tr>
<th>name</th>
<th>type</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>id</td>
<td>bigint</td>
<td>id</td>
</tr>
<tr>
<td>created_at</td>
<td>datetime</td>
<td></td>
</tr>
<tr>
<td>updated_at</td>
<td>datetime</td>
<td></td>
</tr>
<tr>
<td>username</td>
<td>varchar</td>
<td>用户名称</td>
</tr>
<tr>
<td>email</td>
<td>varchar</td>
<td>邮箱</td>
</tr>
<tr>
<td>phone</td>
<td>varchar</td>
<td>电话</td>
</tr>
<tr>
<td>password</td>
<td>varchar</td>
<td>密码</td>
</tr>
<tr>
<td>is_active</td>
<td>tinyint</td>
<td>是否激活</td>
</tr>
<tr>
<td>last_login</td>
<td>datetime</td>
<td>最后登录时间</td>
</tr>
</tbody>
</table>
<h3 id="使用ctemysql-80以上">使用CTE（Mysql 8.0以上）</h3>
<p>Mysql 8 中增加了一个新特性 CTE（Common Table Expressions）通用表表达式，是一种命名的临时结果集，它只存在于单个 SQL 语句的执行范围内。你可以把它想象成一个临时视图，只在当前查询中有效。CTE 主要用于简化复杂的查询，提高可读性和可维护性。</p>
<p>CTE 使用 WITH 语句定义，其基本语法如下：</p>
<pre><code class="language-sql">-- 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;
</code></pre>
<p>使用CTE获取每日注册用户量</p>
<pre><code class="language-sql">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的使用
</code></pre>
<p>使用CTE获取每日注册用户量</p>
<pre><code class="language-sql">-- 统计区间内注册
-- 设置变量（实际使用中可替换为传参，如存储过程或应用层参数）
-- 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 < @end_date-- 注意：如果想包含 @end_date，就用 <；若传进来的是 datetime，注意处理
-- )
-- 统计最早注册日到今天的注册
WITH RECURSIVE date_series AS (
SELECT DATE(MIN(created_at)) AS date FROM user
UNION ALL
-- 递归生成下一天
SELECT date + INTERVAL 1 DAY
FROM date_series
WHERE date < CURDATE()-- 到昨天为止；若要包含今天，改为 <=
)
SELECT
d.date AS reg_date,
COUNT(u.id) AS reg_cnt-- COUNT 本身忽略 NULL，无需 COALESCE
FROM date_series d
-- LEFT JOIN user u
-- ON DATE(u.created_at) = d.date
LEFT JOIN user u
ON u.created_at >= d.date
AND u.created_at < d.date + INTERVAL 1 DAY-- 避免 DATE() 函数
GROUP BY d.date
ORDER BY d.date;
</code></pre>
<p>注：<code>created_at</code> 上建立了索引, 则避免在索引列上使用函数，否则索引会失效<br>
数据库必须对 每一行的 <code>created_at</code> 值计算 <code>DATE()</code>，然后比较<br>
即使 <code>created_at</code> 有索引，也无法直接使用索引查找<br>
因为索引存储的是原始 <code>datetime</code> 值（如 '2025-04-05 10:30:00'）<br>
而不是 <code>DATE()</code> 计算后的结果（'2025-04-05'）</p>
<p>数据量大且查询频繁时：<br>
<code>LEFT JOIN … ON u.created_at >= d.date AND u.created_at < d.date + INTERVAL 1 DAY</code>仍会导致范围查找，如果只关心“天”而不关心时分秒，可额外冗余一个 created_date DATE 字段并建索引，可改成 ON u.created_date = d.date。</p>
<h3 id="使用子查询">使用子查询</h3>
<h4 id="创建日历辅助表">创建日历辅助表</h4>
<p>1）一次性建表（只需执行一次）</p>
<pre><code class="language-sql">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 <= 4018;
</code></pre>
<p>2）按日期统计（查询）</p>
<pre><code class="language-sql">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;
</code></pre>
<h4 id="创建连续数字辅助表">创建连续数字辅助表</h4>
<p>1）一次性建表</p>
<pre><code class="language-sql">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;
</code></pre>
<p>2）按日期统计（查询）100天以内，要更多可创建更大的辅助表</p>
<pre><code class="language-sql">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 < DATE_ADD(d.reg_date, INTERVAL 1 DAY)
GROUP BY d.reg_date
ORDER BY d.reg_date;
-- 注：DATEDIFF 和 DATE_ADD 都是 MySQL 的内置日期函数，用于处理日期和时间的计算，适用于 MySQL 5.5 及以上版本。
-- SELECT DATEDIFF('2024-03-31', '2024-03-01');-- 结果：30
-- SELECT DATE_ADD('2024-03-01', INTERVAL 5 DAY); -- '2024-03-06'
</code></pre>
<p>优点：</p>
<ol>
<li>一张表顶 N 张表， 能映射成日期，也能映射成小时、分钟、周号、批次号、页码。</li>
<li>无任何业务耦合，不会和业务字段纠缠。</li>
</ol>
<h2 id="echarts图表">echarts图表</h2>
<p>echarts 配置</p>
<pre><code class="language-js">// 模拟接口数据返回
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 = userLoginArray.reduce(
(, { date, count }) => [
[...dates, date],
[...counts, count]
],
[[], []]
);
option = {
xAxis: { type: 'category', data: categoryDate },
yAxis: { type: 'value'},
series: [
{ type: 'bar', data: dateCount}
]
};
</code></pre>
<p>echarts dateset 格式配置</p>
<pre><code class="language-js">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'}]
};
</code></pre><br><br>
来源：https://www.cnblogs.com/flytree/p/18704649
頁:
[1]