技術(shù)棧

• vue3
• VChart
• egg.js
• MySQL

需求

根據(jù)已有任務(wù)數(shù)據(jù)，獲取連續(xù)天的任務(wù)完成的數(shù)量，并且通過接口返回后做成圖表。預(yù)期數(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ù)

最簡單的查詢方式當(dāng)然就是 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)

所以需要手動補(bǔ)全這些日期，將其數(shù)據(jù)設(shè)置為 0。網(wǎng)上查了一些方案，有不少騷操作，不過我還是更喜歡和日期表聯(lián)表查詢的方式。感覺這種更合理些。

插入日期表

在建表后，我是通過 node 遍歷的方式插入的數(shù)據(jù)，感覺很 low 的方式……不過暫時能用就行。后面再學(xué)習(xí)更優(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

運(yùn)行起來很慢，慢慢悠悠的用 dayjs 插入最近的 1000 條日期數(shù)據(jù)。

聯(lián)表查詢?nèi)蝿?wù)量 SQL 寫法

在有了兩個表后，就可以使用 LEFT JOIN 來進(jìn)行聯(lián)表查詢了。由于要補(bǔ)全連續(xù)日期，所以先查日期表，然后再聯(lián)表查詢了任務(wù)表。

    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)雅多了。

圖形渲染

有始有終，最后把任務(wù)量以折線圖的方式呈現(xiàn)出來~

node 端進(jìn)行接口實現(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 準(zhǔn)備一個具備大?。▽捀撸┑?DOM，當(dāng)然你也可以在 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>

效果圖如下，非常完美~

整理一些學(xué)到的后端知識

• 查詢的目標(biāo)不一定非得是固定的表，也可以用 () 括號來查詢目標(biāo)數(shù)據(jù)集。無論是 FROM 還是 LEFT JOIN 都是如此。
• IFNULL() 函數(shù)可以在數(shù)據(jù)為空的時候返回一個默認(rèn)值。
• COALESCE() 函數(shù)也是用來處理缺失值的，感覺和 IFNULL 差不多。
• 查詢?nèi)掌诜秶臅r候，不需要連續(xù)用大于等于小于加上 AND，可以直接用 BETWEEN...AND... 來實現(xiàn)。
• DATE() 函數(shù)可以從日期、時間數(shù)據(jù)中提取日期部分，如 YYYY-MM-DD。
• MySQL 服務(wù)器也分好多版本，網(wǎng)上資料來看 8.0+ 是最新的，而穩(wěn)定版本一般是 5.7。另外如果想切換云服務(wù)器的 MySQL 版本需要備份刪庫……
• 在 egg.js 中需要在 mysql 配置中打開 dateStrings: true 來確保時區(qū)為當(dāng)前時區(qū)。否則存取日期的時候會差八個小時（東八區(qū)）。

最后

以上就是我在學(xué)習(xí)圖表后端接口實現(xiàn)的過程中的一些心得，感覺數(shù)據(jù)方向上 SQL 的各種應(yīng)用還是很廣的，可以通過各種不同維度、指標(biāo)、篩選條件，產(chǎn)出各類不同數(shù)據(jù)。

可以預(yù)見后面可以折騰的東西還有很多~