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

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);

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

• SQL データベースをスケーリングする方法
• MySQL テーブルを複製する方法
• 解決済み - 整合性制約違反
• Laravel Eager Loading で 1 日あたり 120 万クエリを節約
• スマート SQL クエリでアプリケーションのパフォーマンスを高速化する方法
• ORM パフォーマンスを最適化してアプリケーションをスケーラブルにします
• 解決済み – Laravel キューとジョブを使用すると MySQL ロック待機タイムアウトを超過しました

PHP アプリケーションを無料で監視する

Inspector は、ソフトウェア開発者向けに特別に設計されたコード実行監視ツールです。クラウド インフラストラクチャに何もインストールする必要はありません。Laravel パッケージをインストールするだけで準備完了です。

Inspector は非常に使いやすく、設定は必要ありません。

HTTP モニタリング、クエリ分析、アラートや通知を好みのメッセージング環境に転送する機能をお探しの場合は、Inspector を無料でお試しください。 [アカウントを登録+(https://app.inspector.dev/register).

または、Web サイトで詳細をご覧ください: https://inspector.dev

以上がMySQL を使用してヒストグラム チャートを作成する – チュートリアルの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。