MySQL を使用してヒストグラムチャートを作成する – チュートリアル-mysql チュートリアル-php.cn

MySQL を使用してヒストグラムチャートを作成する – チュートリアル

王林

リリース： 2024-08-27 06:38:02

オリジナル

1121 人が閲覧しました

MySQL でヒストグラムチャートを構築するための統計クエリを作成するには、COUNT() 関数を GROUP BY とともに使用して、グループ化制約によって作成された指定範囲またはカテゴリ内の値の出現をカウントします。

特に時系列データの場合、過去 30 日間の毎日の間隔で登録されたユーザーの数を監視するなど、ヒストグラムの使用例が数多くあります。これらのクエリを管理バックエンドで使用して、いくつかの重要な KPI を監視します。

残念ながら、SQL データベースはヒストグラムを表すためのネイティブサポートを持っていませんでしたが、ヒストグラムはあらゆる種類のメトリクスを追跡するために最もよく使用されるグラフの 1 つです。

この記事では、この目的でクエリを作成し、いくつかの制限を克服する方法を説明します。 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 年 1 月の場合は「2024-01」)。
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 ソリューションは開発者にとって少し快適ではない可能性があり、また、より多くのカスタマイズが必要になるため、コードベースのソリューションが好まれる可能性があります。

この場合、次の 3 つの簡単な手順で同じ結果を達成できます。

興味のある時間間隔で配列を作成します。
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);

ログイン後にコピー

データベースに関する投稿をさらに読みたい場合は、以下の記事をご覧ください: