技术栈
- vue3
- VChart
- egg.js
- MySQL
需求
根据已有任务数据，获取连续天的任务完成的数量，并且通过接口返回后做成图表。预期数据如下：
[
{
"x": "2024-01-01",
"y": 0
},
{
"x": "2024-01-02",
"y": 26
},
{
"x": "2024-01-03",
"y": 43
},
{
"x": "2024-01-04",
"y": 39
},
{
"x": "2024-01-05",
"y": 22
},
{
"x": "2024-01-06",
"y": 0
},
// ...
]
非连续数据
最简单的查询方式当然就是 GROUP BY 了，但是呢有些日期里面是没有任何数据的。
SELECT
DATE (FinishTime),
COUNT(*) AS count
FROM
tasks
WHERE
FinishTime >= '2024-01-01'
AND FinishTime <= '2024-02-01'
GROUP BY
DATE (FinishTime)
所以需要手动补全这些日期，将其数据设置为 0。网上查了一些方案，有不少骚操作，不过我还是更喜欢和日期表联表查询的方式。感觉这种更合理些。
插入日期表
在建表后，我是通过 node 遍历的方式插入的数据，感觉很 low 的方式……不过暂时能用就行。后面再学习更优雅的写法。
const Service = require('egg').Service
const dayjs = require('dayjs')
class DateService extends Service {
async createDates() {
const startDay = dayjs().subtract(600, 'day')
for (let i = 0; i < 1000; i++) {
const currentDateStr = startDay.add(i, 'day').format('YYYY-MM-DD')
await this.app.mysql.insert('dates', {
date: currentDateStr,
})
}
return '插入成功'
}
}
module.exports = DateService
运行起来很慢，慢慢悠悠的用 dayjs 插入最近的 1000 条日期数据。
联表查询任务量 SQL 写法
在有了两个表后，就可以使用 LEFT JOIN 来进行联表查询了。由于要补全连续日期，所以先查日期表，然后再联表查询了任务表。
SELECT
DATE(dt.dt_date) as x,
IFNULL (tk.tk_count, 0) AS y
FROM
(
SELECT
DATE (date) AS dt_date
FROM
dates
WHERE
date >= '2024-01-01'
AND date <= '2024-02-01
) dt
LEFT JOIN (
SELECT
DATE (FinishTime) as tk_date,
COUNT(*) AS tk_count
FROM
tasks
WHERE
FinishTime >= '2024-01-01
AND FinishTime <= '2024-02-01'
GROUP BY
DATE (FinishTime)
) tk ON dt.dt_date = tk.tk_date
用 AI GPT 优化下：
SELECT
DATE(dt.dt_date) AS x,
COALESCE(tk.tk_count, 0) AS y
FROM (
SELECT
DATE(date) AS dt_date
FROM
dates
WHERE
date BETWEEN '2024-01-01' AND '2024-02-01'
) dt
LEFT JOIN (
SELECT
DATE(FinishTime) AS tk_date,
COUNT(*) AS tk_count
FROM
tasks
WHERE
FinishTime BETWEEN '2024-01-01' AND '2024-02-01'
GROUP BY
DATE(FinishTime)
) tk ON dt.dt_date = tk.tk_date;
如此就感觉优雅多了。
图形渲染
有始有终，最后把任务量以折线图的方式呈现出来~
node 端进行接口实现
async getTaskCount() {
const { start_date, end_date } = this.ctx.query
// 这里做了简单的正则匹配
const reg = /^\d{4}-\d{2}-\d{2}$/
if (!reg.test(start_date) || !reg.test(end_date)) {
return []
}
const sql = `
SELECT
DATE(dt.dt_date) as x,
IFNULL (tk.tk_count, 0) AS y
FROM
(
SELECT
DATE (date) AS dt_date
FROM
dates
WHERE
date >= ?
AND date <= ?
) dt
LEFT JOIN (
SELECT
DATE (FinishTime) as tk_date,
COUNT(*) AS tk_count
FROM
tasks
WHERE
FinishTime >= ?
AND FinishTime <= ?
GROUP BY
DATE (FinishTime)
) tk ON dt.dt_date = tk.tk_date
`
const result = await this.app.mysql.query(sql, [
start_date,
end_date,
start_date,
end_date,
])
return result
}
前端部分通过 VChart 快速渲染
<template>
<!-- 为 vchart 准备一个具备大小（宽高）的 DOM，当然你也可以在 spec 配置中指定 -->
<div id="chart" style="width: 600px; height: 400px"></div>
</template>
<script setup>
import axios from 'axios'
import VChart from '@visactor/vchart'
import { onMounted, ref } from 'vue'
onMounted(() => {
getChartData()
})
const chartData = ref([])
function getChartData() {
return axios
.get('/api/yang/chart', {
params: {
start_date: '2024-01-01',
end_date: '2024-02-01',
},
})
.then(({ data }) => {
chartData.value = data
renderChart()
})
}
function renderChart() {
const spec = {
type: 'line',
data: {
values: chartData.value,
},
xField: 'x',
yField: 'y',
}
const vchart = new VChart(spec, { dom: 'chart' })
// 创建 vchart 实例
// 绘制
vchart.renderSync()
}
</script>
<style lang="scss" scoped></style>
效果图如下，非常完美~
整理一些学到的后端知识
- 查询的目标不一定非得是固定的表，也可以用
()括号来查询目标数据集。无论是 FROM 还是 LEFT JOIN 都是如此。 - IFNULL() 函数可以在数据为空的时候返回一个默认值。
- COALESCE() 函数也是用来处理缺失值的，感觉和 IFNULL 差不多。
- 查询日期范围的时候，不需要连续用大于等于小于加上 AND，可以直接用 BETWEEN...AND... 来实现。
- DATE() 函数可以从日期、时间数据中提取日期部分，如 YYYY-MM-DD。
- MySQL 服务器也分好多版本，网上资料来看 8.0+ 是最新的，而稳定版本一般是 5.7。另外如果想切换云服务器的 MySQL 版本需要备份删库……
- 在 egg.js 中需要在 mysql 配置中打开
dateStrings: true来确保时区为当前时区。否则存取日期的时候会差八个小时（东八区）。
最后
以上就是我在学习图表后端接口实现的过程中的一些心得，感觉数据方向上 SQL 的各种应用还是很广的，可以通过各种不同维度、指标、筛选条件，产出各类不同数据。
可以预见后面可以折腾的东西还有很多~
