Как сделать групировку запроса по месяцу и вложеный подсчет результатов по условию?

Question

[alexfyodrv]

Приветствую,
подскажите пожалуйста как можно реализовать групировку кол-ва строк до определенной даты и затем посчитать строки внутри групы по условию?
Например:
Каждый месяц добавляются новые пользователи и мне нужно вывести статистику о них как кол-во пользователей увеличивалось. Мне нужно от текущей даты отнять ровно месяц, сегодня 12 июля, затем 12 июня, 12 мая и т.д и узнать сколько пользователей было на тот момент, далее пользователи имеют группы: user, customer и группа "все".

Сейчас запрос рекурсивно достает пользователей из базы для каждого месяца, далее я сам создаю из этого массив данных. Если это можно реализовать через Eloquent буду благодарен за пример.

select count(*) from `users` where created_at <= `2019-07-12`
select count(*) from `users` where created_at <= `2019-07-12` and type = 'user'
select count(*) from `users` where created_at <= `2019-07-12` and type = 'customer'

select count(*) from `users` where created_at <= `2019-06-12`
select count(*) from `users` where created_at <= `2019-06-12` and type = 'user'
select count(*) from `users` where created_at <= `2019-06-12` and type = 'customer'

$stats[] = [
            '2019-07-12' => [
              'all' => $this->countAll(),
              'users' => $this->countUsers(),
              'customers' => $this->countCustomers(),
            ]
        ];

Answer 1

[miki131]

Как вариант чтобы не делать по 3 запроса

select count(*), sum(type = 'user'), sum(type = 'customer') from `users` where created_at <= `2019-07-12`

Comments

[alexfyodrv]

у меня где-то 50 месяцев в статистике и прийдется 50 раз выполнить такой запрос, можно ли как-то сделать чтобы без цикла?

[miki131]

select
sum(created_at <= `2019-07-12`), sum(type = 'user' and created_at <= `2019-07-12`), sum(type = 'customer' and created_at <= `2019-07-12`) ,
sum(created_at <= `2019-07-11`), sum(type = 'user' and created_at <= `2019-07-11`), sum(type = 'customer' and created_at <= `2019-07-11`) 
from `users`

а потом парсить ответ

Answer 2

[NubasLol]

Можно как-то так.

select
    sum(case when type = "all" then 1 else 0 end) as "all",
from (values ('2019-01-12', '2019-07-12')) AS dates (dateStart, dateEnd)
 inner join users on users.created_at > dates.dateStart::date and users.created_at < dates.dateEnd::date

group by dates.dateStart::date

Но правильно такую групировку длеать уже в php. А в sql

select dates.dateStart::date, type, count(*)
group by dates.dateStart::date

Answer 3

[Константин Тимошено]

Для работы с датами я использую Carbon. Загоняют в цикл ( например addMonth или subMonth) и условие и на выходе получаю массив данных с готовыми результатами. Может мой ответ вам слабо поможет, так как не люблю "сырые" запросы