使用 MySQL 创建直方图 – 教程

php中文网 2024-10-15 11:47:51

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



<p>daterange cte（通用表表达式）递归地生成一系列月份，涵盖销售表中最小和最大销售日期之间的时间范围。</p>

<p>此查询根据销售表中的最小和最大销售日期动态生成所选日历间隔的月份，确保所有月份都出现在结果集中。</p>

<h2>
  
  
  用代码填补空白
</h2>

<p>sql 解决方案对于开发人员来说可能不太舒服，而且需要更多的自定义可能会导致更喜欢基于代码的解决方案。</p>

<p>在这种情况下，您可以通过三个简单的步骤获得相同的结果：</p>

用你感兴趣的时间间隔创建数组；
获取group by查询结果；
合并它们。

这是使用 laravel 和 carbon 库的代码片段：

$dates = [];

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

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

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

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