如何使用Raw SQl / Laravel - Eloquent ORM进行累积计数

Question

这更像是一个SQL问题，而不是一个Laravel问题。

我想基于我的User模型和created_at字段完成如下操作。(table users in database)

<表类> created_at user_id tbody><<tr>2022-04-3012022-05-0222022-05-034

如果你的mysql版本支持窗口函数，你可以尝试使用SUM窗口函数来做累计计数

DB::table(DB::raw('(select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date 
from `users` 
where `users`.`deleted_at` is null 
group by `date`) t1'))
->select('created_users_to_this_date','date',DB::raw('SUM(created_users_to_this_date) OVER(ORDER BY date) total_users_created_to_date'))
->get();

对应的SQL将是

SELECT DATE ,
@running_number:=@running_number+created_users_to_this_date AS created_users_to_this_date
FROM (SELECT 
COUNT(*) AS created_users_to_this_date,
DATE_FORMAT(created_at, '%Y-%m') DATE
FROM
users 
where users.deleted_at is null 
GROUP BY `date` 
ORDER BY `date` ASC ) final
JOIN (SELECT @running_number:=0) rn

User::query()
->select('
DB::raw('COUNT(DATE_FORMAT(created_at, '%Y-%m') = DATE_FORMAT(now(), '%Y-%m')) as created_users_to_this_date'),
DB::raw('COUNT(DATE_FORMAT(created_at, '%Y-%m') <= DATE_FORMAT(now(), '%Y-%m')) as total_users_created_to_date'),
DB::raw('DATE_FORMAT(created_at, '%Y-%m') as date')
')->orderBy('date')->groupBy('date')->get();