Laravel按年月统计交易数据：success与failed计数异常问题

1. 计数异常的核心原因 #

count()函数会统计所有非NULL的表达式结果，而state="success"这类比较语句在MySQL中会返回1（成立）或0（不成立），这两个值都不属于NULL，所以count(state="success")会把所有行都计入统计，最终和总交易数完全一致。

正确的统计方式有两种：

方式一：使用SUM()统计符合条件的行数 #

利用条件成立时返回1、不成立返回0的特性，求和得到符合条件的记录数：

DB::raw('SUM(state = "success") as success'),
DB::raw('SUM(state != "success") as failed'),

方式二：使用COUNT()配合CASE语句 #

当条件不成立时让CASE返回NULL，count()会自动忽略NULL值：

DB::raw('COUNT(CASE WHEN state = "success" THEN 1 END) as success'),
DB::raw('COUNT(CASE WHEN state != "success" THEN 1 END) as failed'),

2. WHERE条件逻辑错误 #

当前的时间范围查询用了orWhere，逻辑完全错误，会导致查询出所有created_at >= $start 或者 created_at >= $end的记录，这显然不是你要的时间区间范围。正确的写法应该是查询created_at在$start到$end之间的记录：

->where(function ($query) use ($start, $end) {
    return $query->where('created_at', '>=', $start)
        ->where('created_at', '<=', $end); // 把orWhere改成where，并且匹配结束时间
})

修正后的完整代码 #

return $this->transactions()
            ->select(
                DB::raw('count(state) as count'),
                DB::raw('SUM(state = "success") as success'), // 修正success统计
                DB::raw('SUM(state != "success") as failed'), // 修正failed统计
                DB::raw("DATE_FORMAT(created_at, '%m-%Y') as date"),
                DB::raw('YEAR(created_at) year, MONTH(created_at) month')
            )
            ->where(function ($query) use ($start, $end) {
                return $query->where('created_at', '>=', $start)
                    ->where('created_at', '<=', $end); // 修正时间范围逻辑
            })
            ->groupBy('year', 'month')
            ->orderBy('created_at', 'asc')
            ->get();

额外说明 #

如果state字段可能存在NULL值，那么state != "success"会包含NULL的情况，如果你希望failed仅统计state为"failed"的记录，需要把条件改成state = "failed"，避免把NULL值计入失败数：

DB::raw('SUM(state = "failed") as failed'),

内容的提问来源于stack exchange，提问作者Mahmoud Niypoo