MariaDB/MySQL: частичное «общее» количество строк для перемещения временного окна

У меня есть база данных 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

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
2
0
54
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

SELECT 
   date(RegistrationDatetime ),
   sum(count(*)) over (order by date(RegistrationDatetime ))
FROM
   mytable
GROUP BY
   date(RegistrationDatetime );

выход:

дата (дата регистрации и время) сумма (количество (*)) по (порядок по дате (RegistrationDatetime )) 2022-01-03 2 2022-01-04 4 2022-01-05 5

см.: 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)

Посмотрите демо здесь.

Спасибо за ваши усилия - здесь так много отличных ответов, можно принять только один и выбрать "самый быстрый" постер, потому что каждое решение одинаково великолепно.

tim 07.01.2023 15:24
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 выполняется в доли времени.

tim 07.01.2023 15:24
Ответ принят как подходящий

Используйте оконную функцию COUNT():

SELECT DISTINCT 
       DATE(RegistrationDatetime) AS Date,
       COUNT(*) OVER (ORDER BY DATE(RegistrationDatetime)) AS Count
FROM tablename;

Смотрите демо.

Спасибо за ваши усилия - здесь так много отличных ответов, можно принять только один и выбрать "самый быстрый" постер, потому что каждое решение одинаково великолепно.

tim 07.01.2023 15:24

@tim самый быстрый не обязательно правильный: stackoverflow.com/revisions/75040807/1

forpas 07.01.2023 16:11

Извините, я не совсем понимаю - добавленный SUM() не очень актуален, COUNT(*) OVER уже работал (как и у вас). И DBFiddle был только для демонстрационных целей.

tim 07.01.2023 16:57

@tim this: dbfiddle.uk/lQ2LcDH3 было решением, предложенным самым быстрым

forpas 07.01.2023 16:59

@tim SUM() был добавлен позже, потому что он актуален при использовании GROUP BY. В моем запросе не используется GROUP BY, поэтому функция SUM() не нужна.

forpas 07.01.2023 17:01

Другие вопросы по теме