У меня есть база данных MariaDB с пользователями и их соответствующей датой регистрации, например:
+----+----------+----------------------+
| ID | Username | RegistrationDatetime |
+----+----------+----------------------+
| 1 | A | 2022-01-03 12:00:00 |
| 2 | B | 2022-01-03 14:00:00 |
| 3 | C | 2022-01-04 23:00:00 |
| 4 | D | 2022-01-04 14:00:00 |
| 5 | E | 2022-01-05 14:00:00 |
+----+----------+----------------------+
Я хочу узнать общее количество пользователей в системе в конце каждой даты с помощью всего одного запроса - возможно ли это?
Таким образом, результат должен быть примерно таким:
+------------+-------+
| Date | Count |
+------------+-------+
| 2022-01-03 | 2 |
| 2022-01-04 | 4 |
| 2022-01-05 | 5 |
+------------+-------+
Да, это легко с одиночными запросами и перебором дат с помощью PHP, но как это сделать всего с одним запросом?
РЕДАКТИРОВАТЬ
Спасибо за все ответы, да, пользователи могут быть отменены/удалены, т.е. переход по максимальному (ID) за определенный период времени НЕВОЗМОЖЕН. В колонке могут быть пробелы ID
SELECT
date(RegistrationDatetime ),
sum(count(*)) over (order by date(RegistrationDatetime ))
FROM
mytable
GROUP BY
date(RegistrationDatetime );
выход:
см.: DBFIDDLE
Если у вас нет отмененных пользователей, вы можете сделать:
SELECT DATE(RegistrationDatetime) AS date_, MAX(Id) AS cnt
FROM tab
GROUP BY DATE(RegistrationDatetime)
Посмотрите демо здесь.
В противном случае вам может понадобиться использовать ROW_NUMBER для создания этого рейтинга:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY RegistrationDatetime) AS rn
FROM tab
)
SELECT DATE(RegistrationDatetime) AS date_, MAX(rn) AS cnt
FROM cte
GROUP BY DATE(RegistrationDatetime)
Посмотрите демо здесь.
SELECT t1.RegistrationDatetime AS Date,
(SELECT COUNT(*) FROM users t2 WHERE t2.RegistrationDatetime <= t1.RegistrationDatetime) AS Count
FROM users t1
GROUP BY t1.RegistrationDatetime
Спасибо за ваши усилия - это действительно работает, но кажется НЕВЕРОЯТНО медленным. В моей таблице 12 тыс. пользовательских строк :) Вероятно, отсутствует какой-то конкретный индекс в столбце «Дата регистрации», тогда как оконная функция COUNT выполняется в доли времени.
Используйте оконную функцию COUNT()
:
SELECT DISTINCT
DATE(RegistrationDatetime) AS Date,
COUNT(*) OVER (ORDER BY DATE(RegistrationDatetime)) AS Count
FROM tablename;
Смотрите демо.
Спасибо за ваши усилия - здесь так много отличных ответов, можно принять только один и выбрать "самый быстрый" постер, потому что каждое решение одинаково великолепно.
@tim самый быстрый не обязательно правильный: stackoverflow.com/revisions/75040807/1
Извините, я не совсем понимаю - добавленный SUM()
не очень актуален, COUNT(*) OVER
уже работал (как и у вас). И DBFiddle был только для демонстрационных целей.
@tim this: dbfiddle.uk/lQ2LcDH3 было решением, предложенным самым быстрым
@tim SUM() был добавлен позже, потому что он актуален при использовании GROUP BY. В моем запросе не используется GROUP BY, поэтому функция SUM() не нужна.
Спасибо за ваши усилия - здесь так много отличных ответов, можно принять только один и выбрать "самый быстрый" постер, потому что каждое решение одинаково великолепно.