使用 MySQL 创建直方图 – 教程

select

date_format(sale_date, %y-%m) as sales_month,

count(*) as count

from

sales

group by

date_format(sale_date, %y-%m);

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 mont系统开发mhkj33h), %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系统开发mhkj33>

$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()

) {

$date系统开发mhkj33s[] = [

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

retu系统开发mhkj33rn array_map(function ($date) use ($sales) {

foreach ($sales as $sale) {

if ($date[day] === $sale[day]) {

return $sale;

}

}

}, $dates);