如何根据日期计算访问？

Question

访问表

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- 2021-08--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-2021-04-07 18:36:59
518	2021-06-14 04:47:52
518	2021-12-26 22:16:47

让我们假设当前时间是28-5-2022 15:00:00:00我正在尝试在日期之前过滤访问。

执行命令时的预期结果

last_60_minutes last_24_hours	your	last_7_days	last_30_days last_6_6_6_months	for	last_months last_12_months
0	1	2	5	8	8

这是我尝试过的，但我想要的是我想要的，因为我想要 for your strong> for your strong>

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

>此 a>

Answer 1

要么使用案例表达式：

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;

或sum（）而不是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;

请参阅 demo 。
。