使用 MySQL 建立直方圖 – 教學課程-mysql教程-PHP中文網

使用 MySQL 建立直方圖 – 教學課程

王林

發布： 2024-08-27 06:38:02

原創

1123 人瀏覽過

要使用 MySQL 建立統計查詢來建立直方圖，您可以使用 COUNT() 函數和 GROUP BY 來計算分組約束所建立的指定範圍或類別內的值的出現次數。

特別是對於時間序列數據，直方圖有很多用例，例如監控過去 30 天內每天註冊的用戶數量。我們在管理後端使用這些查詢來監控一些重要的 KPI。

不幸的是，SQL 資料庫從來沒有對錶示直方圖的本機支持，但它們是用於追蹤各種指標的最常用圖表之一。

在本文中，我將向您展示如何為此目的建立查詢並克服一些限制。假設您有一個名為 sales 的表，其中包含名為 sale_date 的列，其中包含每次銷售的日期和時間。您想要建立一個直方圖，顯示每月間隔內的銷售分佈。

SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
    COUNT(*) AS count
FROM 
    sales
GROUP BY 
    DATE_FORMAT(sale_date, '%Y-%m');

登入後複製

此查詢：

DATE_FORMAT(sale_date, '%Y-%m') 將 sale_date 欄位格式化為年月格式（例如，「2024-01」表示 2024 年 1 月）。
COUNT(*) 統計每個月內發生的銷售次數。
FROM sales 指定從中檢索資料的表。
GROUP BY DATE_FORMAT(sale_date, '%Y-%m') 根據格式化的銷售日期將銷售資料按月分組。

此查詢將為您提供每個月間隔內的銷售發生次數，允許您建立直方圖以視覺化銷售隨時間的分佈。

如何填補直方圖查詢中的空白
執行此查詢，您可能會在結果集中看到一些缺失的月份，可能是因為在某些月份沒有銷售，因此 GROUP BY 函數無法產生這些間隔的資料。

如何調整查詢以使用預設零值填補這些空白？否則直方圖仍然不完整。

為了填補結果集中的空白並確保顯示所有月份，即使某些月份沒有銷售，您也可以使用遞歸公用表表達式 (CTE) 動態產生所選日曆間隔的月份。

它產生涵蓋所需時間範圍的日期序列。然後，您可以將此日期序列與您的銷售資料左連接，以將所有月份納入結果集中。

WITH RECURSIVE DateRange AS (
    SELECT 
        DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 11 MONTH), '%Y-%m-01') AS min_date,
        DATE_FORMAT(NOW(), '%Y-%m-01') AS max_date
    UNION ALL
    SELECT 
        DATE_FORMAT(DATE_ADD(min_date, INTERVAL 1 MONTH), '%Y-%m-01'),
        max_date
    FROM 
        DateRange
    WHERE 
        DATE_ADD(min_date, INTERVAL 1 MONTH) <= max_date
)
SELECT 
    DATE_FORMAT(DateRange.min_date, '%Y-%m') AS sales_month,
    COUNT(sales.sale_date) AS count
FROM 
    DateRange
LEFT JOIN 
    sales ON DATE_FORMAT(DateRange.min_date, '%Y-%m') = DATE_FORMAT(sales.sale_date, '%Y-%m')
GROUP BY 
    sales_month
ORDER BY 
    sales_month;

登入後複製

DateRange CTE（通用表表達式）遞歸產生一系列月份，涵蓋銷售表中最小和最大銷售日期之間的時間範圍。

此查詢會根據銷售表中的最小和最大銷售日期動態產生所選日曆間隔的月份，確保所有月份都顯示在結果集中。

用程式碼填補空白

SQL 解決方案對於開發人員來說可能不太舒服，而且需要更多的自訂可能會導致更喜歡基於程式碼的解決方案。

在這種情況下，您可以透過三個簡單的步驟來獲得相同的結果：

用您感興趣的時間間隔建立數組；
取得GROUP BY查詢的結果；
合併它們。

這是使用 Laravel 和 Carbon 函式庫的程式碼片段：

$dates = [];

// Create the array with the time interval of your interests
for(
    $day = now()->subDays(31);
    $day->startOfDay()->lte(now());
    $day->addDay()
) {
    $dates[] = [
        'day' => $day->format('Y-m-d'),
        'total' => 0,
    ];
}

// Get the result from the GROUP BY query
$sales = $product->sales()->select(DB::raw('DATE(sale_at) as day, CAST(SUM(qty) AS UNSIGNED) as total'))
    ->where('sale_at', '>=', now()->subDays(31))
    ->groupBy('day')
    ->get();

// Merge them
return array_map(function ($date) use ($sales) {
    foreach ($sales as $sale) {
        if ($date['day'] === $sale['day']) {
            return $sale;
        }
    }
}, $dates);

登入後複製

如果您有興趣閱讀更多有關資料庫的文章，您可以查看以下文章：