使用 MySQL 创建直方图 – 教程

要使用 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);

如果您有兴趣阅读更多有关数据库的文章，您可以查看以下文章：

• 如何扩展 SQL 数据库
• 如何复制 MySQL 表
• 已解决 - 违反完整性约束
• 使用 Laravel 预加载每天节省 120 万次查询
• 如何通过智能 SQL 查询加速应用程序性能
• 使您的应用程序可扩展，优化 ORM 性能
• 已解决 – 使用 Laravel 队列和作业超出 MySQL 锁等待超时

免费监控您的 PHP 应用程序

Inspector是一款专为软件开发人员设计的代码执行监控工具。您无需在云基础设施上安装任何内容，只需安装 Laravel 软件包即可开始使用。

Inspector 超级易于使用，并且需要零配置。

如果您正在寻找 HTTP 监控、查询见解以及将警报和通知转发到您首选消息传递环境的功能，请免费尝试 Inspector。 [注册您的帐户+(https://app.inspector.dev/register)。

或在网站上了解更多信息：https://inspector.dev

以上是使用 MySQL 创建直方图 – 教程的详细内容。更多信息请关注PHP中文网其他相关文章！