MariaDB / MySQL: Partial "total" number of rows for moving time-window

Question

I have a MariaDB Database with Users and their appropriate registration date, something like:

+----+----------+----------------------+
| 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 |
+----+----------+----------------------+

I want to know the total number of users in the system at the end of every date with just one query - is that possible?

So the result should be something like:

+------------+-------+
|   Date     | Count |
+------------+-------+
| 2022-01-03 |     2 |
| 2022-01-04 |     4 |
| 2022-01-05 |     5 |
+------------+-------+

Yes it's easy with single queries and looping over the dates using PHP, but how to do it with just one query?

EDIT Thanks for all the replies, yes, users could get cancelled / deleted, i.e. going by the max(ID) for a specific time period is NOT possible. There could be gaps in the column ID

Luuk · Answer 1 · 2023-01-07T13:57:07.273

2

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

output:

date(RegistrationDatetime )	sum(count(*)) over (order by date(RegistrationDatetime ))
2022-01-03	2
2022-01-04	4
2022-01-05	5

see: DBFIDDLE

edited Jan 07 '23 at 13:57

answered Jan 07 '23 at 13:46

Luuk

12,245
5
22
33

score 2 · Accepted Answer · answered Jan 07 '23 at 13:53

2

Use COUNT() window function:

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

See the demo.

answered Jan 07 '23 at 13:53

forpas

160,666
10
38
76

2

@tim the *quickest* is not necessarily correct: https://stackoverflow.com/revisions/75040807/1 – forpas Jan 07 '23 at 15:11
Sorry I dont quite get it- the `SUM()` added is not really relevant, the `COUNT(*) OVER` was already working (like yours does). And the DBFiddle was just for demo purposes – tim Jan 07 '23 at 15:57
1

@tim this: https://dbfiddle.uk/lQ2LcDH3 was the solution proposed by the *quickest* – forpas Jan 07 '23 at 15:59
1

@tim SUM() was added later because it is relevant when GROUP BY is used. My query does not use GROUP BY an this is why SUM() is not needed. – forpas Jan 07 '23 at 16:01

lemon · Answer 3 · 2023-01-07T13:54:01.043

If you have no cancelled users, you can do:

SELECT DATE(RegistrationDatetime) AS date_, MAX(Id) AS cnt
FROM tab
GROUP BY DATE(RegistrationDatetime)

Check the demo here.

Otherwise you may need to use a ROW_NUMBER to generate that ranking:

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)

Check the demo here.

score 1 · Answer 4 · answered Jan 07 '23 at 13:49

1

SELECT t1.RegistrationDatetime AS Date,
       (SELECT COUNT(*) FROM users t2 WHERE t2.RegistrationDatetime <= t1.RegistrationDatetime) AS Count
FROM users t1
GROUP BY t1.RegistrationDatetime

answered Jan 07 '23 at 13:49

underthevoid

513
1
6
17

Thanks for your efforts - this works indeed but seems INCREDIBLY slow. My table has 12k user rows :) Probably missing some specific index on the Registration date column, whereas the COUNT window function runs in fractions of the time. – tim Jan 07 '23 at 14:24

MariaDB / MySQL: Partial "total" number of rows for moving time-window

4 Answers4