技術棧
- vue3
- VChart
- egg.js
- MySQL
需求
根據(jù)已有任務數(shù)據(jù)到腥，獲取連續(xù)天的任務完成的數(shù)量个唧，并且通過接口返回后做成圖表庇忌。預期數(shù)據(jù)如下：
[
{
"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
},
// ...
]
非連續(xù)數(shù)據(jù)
最簡單的查詢方式當然就是 GROUP BY 了睛挚，但是呢有些日期里面是沒有任何數(shù)據(jù)的。
SELECT
DATE (FinishTime),
COUNT(*) AS count
FROM
tasks
WHERE
FinishTime >= '2024-01-01'
AND FinishTime <= '2024-02-01'
GROUP BY
DATE (FinishTime)
所以需要手動補全這些日期溃蔫，將其數(shù)據(jù)設置為 0健提。網(wǎng)上查了一些方案，有不少騷操作伟叛，不過我還是更喜歡和日期表聯(lián)表查詢的方式矩桂。感覺這種更合理些。
插入日期表
在建表后痪伦，我是通過 node 遍歷的方式插入的數(shù)據(jù)，感覺很 low 的方式……不過暫時能用就行雹锣。后面再學習更優(yōu)雅的寫法网沾。
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 條日期數(shù)據(jù)蕊爵。
聯(lián)表查詢?nèi)蝿樟?SQL 寫法
在有了兩個表后辉哥，就可以使用 LEFT JOIN 來進行聯(lián)表查詢了。由于要補全連續(xù)日期攒射，所以先查日期表醋旦，然后再聯(lián)表查詢了任務表。
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 優(yōu)化下：
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;
如此就感覺優(yōu)雅多了会放。
圖形渲染
有始有終饲齐，最后把任務量以折線圖的方式呈現(xiàn)出來~
node 端進行接口實現(xiàn)
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' })
// 創(chuàng)建 vchart 實例
// 繪制
vchart.renderSync()
}
</script>
<style lang="scss" scoped></style>
效果圖如下捂人，非常完美~
整理一些學到的后端知識
- 查詢的目標不一定非得是固定的表，也可以用
()括號來查詢目標數(shù)據(jù)集矢沿。無論是 FROM 還是 LEFT JOIN 都是如此滥搭。 - IFNULL() 函數(shù)可以在數(shù)據(jù)為空的時候返回一個默認值。
- COALESCE() 函數(shù)也是用來處理缺失值的捣鲸，感覺和 IFNULL 差不多瑟匆。
- 查詢?nèi)掌诜秶臅r候，不需要連續(xù)用大于等于小于加上 AND栽惶，可以直接用 BETWEEN...AND... 來實現(xiàn)愁溜。
- DATE() 函數(shù)可以從日期疾嗅、時間數(shù)據(jù)中提取日期部分，如 YYYY-MM-DD祝谚。
- MySQL 服務器也分好多版本宪迟，網(wǎng)上資料來看 8.0+ 是最新的，而穩(wěn)定版本一般是 5.7交惯。另外如果想切換云服務器的 MySQL 版本需要備份刪庫……
- 在 egg.js 中需要在 mysql 配置中打開
dateStrings: true來確保時區(qū)為當前時區(qū)次泽。否則存取日期的時候會差八個小時（東八區(qū)）。
最后
以上就是我在學習圖表后端接口實現(xiàn)的過程中的一些心得席爽，感覺數(shù)據(jù)方向上 SQL 的各種應用還是很廣的意荤，可以通過各種不同維度、指標只锻、篩選條件玖像，產(chǎn)出各類不同數(shù)據(jù)。
可以預見后面可以折騰的東西還有很多~
