How can I count the visits according to the date?

Question

The VISITS table

USER_ID	VISITED_IN
518	2022-04-13 20:37:04
518	2021-12-29 22:26:50
518	2021-03-04 04:22:46
518	2021-08-13 02:14:54
518	2022-05-26 20:49:01
518	2022-05-05 17:47:46
518	2021-09-12 08:58:33
518	2021-04-07 18:36:59
518	2021-06-14 04:47:52
518	2021-12-26 22:16:47

Let's suppose the current time is 28-5-2022 15:00:00, I'm trying to filter the visits by the date.

The expected result when executing the command

LAST_60_MINUTES	LAST_24_HOURS	LAST_7_DAYS	LAST_30_DAYS	LAST_6_MONTHS	LAST_12_MONTHS
0	0	1	2	5	8

Here is what I tried but does not work as I want

SELECT 
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE))) AS LAST_60_MINUTES,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR))) AS LAST_24_HOURS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY))) AS LAST_7_DAYS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY))) AS LAST_30_DAYS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH))) AS LAST_6_MONTHS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH))) AS LAST_12_MONTHS
    FROM VISITS WHERE
    USER_ID = 518

For more information look at this question

A Summary table (see https://stackoverflow.com/a/72426694/1766831) would work nicely if your titles were "last 60 min", "earlier today", "week ending yesterday", etc. — Rick James, May 29 '22 at 19:55

score 1 · Accepted Answer · answered May 29 '22 at 13:37

1

Either use CASE expressions:

SELECT 
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE) THEN 1 END) AS LAST_60_MINUTES,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) AS LAST_24_HOURS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) AS LAST_7_DAYS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) AS LAST_30_DAYS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 1 END) AS LAST_6_MONTHS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH) THEN 1 END) AS LAST_12_MONTHS
FROM VISITS 
WHERE USER_ID = 518;

or SUM() instead of COUNT():

SELECT 
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE)) AS LAST_60_MINUTES,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) AS LAST_24_HOURS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS LAST_7_DAYS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS LAST_30_DAYS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS LAST_6_MONTHS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AS LAST_12_MONTHS
FROM VISITS 
WHERE USER_ID = 518;

See the demo.

answered May 29 '22 at 13:37

forpas

160,666
10
38
76

The query is very very slow, Can you check the link in the question above? – Taha Sami May 29 '22 at 13:48
1

@luq891 what about that question? You accepted an answer there which means that the performance problem is solved. My answer is about the current question. – forpas May 29 '22 at 14:15
Yes the problem is solved, But when I run your command, The query take a lot of time I don't know why. I will find the problem and tell you. – Taha Sami May 29 '22 at 14:21
Why you put THEN 1? – Taha Sami May 29 '22 at 14:23
1

@luq891 COUNT() counts any non-null value, so it makes no difference if there is THEN 1 or THEN 2 or even THEN 0, as long as that value (which is returned by the CASE expression) is not null. – forpas May 29 '22 at 14:31
1

Try to create an index for 'VISITED_IN" as descending, and also for User_ID – isatsara May 29 '22 at 18:25
1

"very slow" -- Do you have specifically this composite index? `PRIMARY KEY(user_id, visited_in)`? `DESC` is not critical. – Rick James May 29 '22 at 19:49
2

@SatsaraGunaratne - separate indexes are not useful. A composite index is what is needed here. – Rick James May 29 '22 at 19:52
@RickJames Good morning, Could you look at [this image](https://ibb.co/yd8XZC0)? I created a composite index (USER_ID, VISITED_IN), I know the index is good if there is a where clause, But the command in this answer does not contain a where clause for VISITED_IN, Should I remove the composite index and convert it to a single index with (USER_ID)? – Taha Sami May 30 '22 at 08:23
1

@luq891 - `INDEX(user_id, visited_id)` can handle any needs for `INDEX(user_id)`. So, do not add that single-column index. (That image does not load for me.) – Rick James May 30 '22 at 17:35
@RickJames [The image](https://i.stack.imgur.com/S9QrZ.png). Sorry, I did not understand you well, If I remove the VISITED_IN and convert it to a single index (USER_ID), Does that will affect the query time because there is no a where clause in the command above? – Taha Sami May 30 '22 at 20:12
1

A "covering" index is one where _all_ the needed columns (anywhere in the `SELECT`) are in a single `INDEX`. This is 'somewhat' faster because all the work can be in the BTree containing the `INDEX`, without having to reach over to the data's BTree to get more columns. – Rick James May 30 '22 at 23:56

How can I count the visits according to the date?

1 Answers1

Linked