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

Question

[road_warrior12]

Всем привет) Сразу оговорюсь, что задача учебная, но никак не могу разобраться, как реализовать ее решение.
Есть таблица registers, в которой есть поля user_id и register_date( формат date)
Выглядит она вот так:
user_id register_date
001 2021-02-01
002 2021-02-15
003 2021-02-15
004 2021-04-12
005 2021-06-01
006 2021-06-02
007 2021-06-15

Суть задачи в следующем - вывести количество пользователей, которые были уже зарегестрированы к моменту июня 2021. Вывод должен быть таким:
report_date cnt_all_couriers
2021-06-01 5
2021-06-02 6
2021-06-03 6
2021-06-04 6
---
2021-06-15 7

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

написал следующий код:
CREATE TABLE registers (
user_id int,
register_date date
);

INSERT INTO registers VALUES
(001, '2021-02-01'), (002, '2021-02-15'), (003, '2021-02-15'), (004, '2021-06-01'), (005, '2021-06-02'), (006, '2021-06-03'), (007, '2021-06-15');

SELECT register_date, sum(user_id) AS cnt_all_users
FROM registers
WHERE register_date LIKE "_%06%_"
GROUP BY register_date

Но он не выводит дни, в которых нет записей, как можно это исправить ? Спасибо !

Answer 1

[Rsa97]

user_id соответсвует количеству зарегистрированных клиентов и написал следующий код

Неудачное решение. Нет никаких гарантий, что id будут идти строго последовательно.

Решение для MySQL 8.0 и выше

WITH RECURSIVE `dates` (`date`) AS (
  SELECT '2021-06-01'
  UNION
  SELECT `date` + INTERVAL 1 DAY
    FROM `dates`
    WHERE `date` < '2021-06-30'
)
SELECT `d`.`date`, MAX(`c`.`count`) OVER `win` AS `registered`
  FROM `dates` AS `d`
  LEFT JOIN (
    SELECT DISTINCT `date`, COUNT(*) OVER `win` AS `count`
      FROM `registers`
      WINDOW `win` AS (ORDER BY `date`)
  ) AS `c` ON `c`.`date` = `d`.`date`
  WINDOW `win` AS (ORDER BY `d`.`date`)

Comments

[road_warrior12]

Спасибо ! Но в Вашем решении очень много пока еще неизвестных для меня функций, я немного доработал свой изначальный код, можете, пожалуйста, подсказать, реально ли дополнив мой код сделать вывод всех дней месяца ? Или же нужно прям с нуля переписывать, как исполнили Вы. Собственно, доработанный код:
CREATE TABLE registers (
user_id int,
register_date date
);

INSERT INTO registers VALUES
(001, '2021-02-01'), (002, '2021-02-15'), (003, '2021-02-15'), (004, '2021-06-01'), (005, '2021-06-02'), (006, '2021-06-03'), (007, '2021-06-15');

SELECT register_date, sum(user_id) AS cnt_all_users
FROM registers
WHERE register_date LIKE "_%06%_"
GROUP BY register_date

[Rsa97]

road_warrior12,
1. В чём смысл суммировать user_id?
2. А если в таблицу добавить 2006 год?

[road_warrior12]

1) Не понимаю, как иначе добиться вывода кол-ва зарегистрированных пользователей, если использую count, то из-за группировки по датам он везде в итоге выводит единицу
2) Поправил на WHERE register_date LIKE "%2021%06%_" . Так будет учитываться только июнь 2021

[Rsa97]

road_warrior12,
1.Либо через подсчёт количества записей, которые идут раньше текущей, чем и занимается одно из WINDOW в моём запросе, либо через переменную.
2. LIKE - медленная и не всегда индексируемая операция. Её следует по возможности избегать. В данном случае `date` BETWEEN '2021-06-01' AND '2021-06-30' будет работать лучше.