create_user_code | create_terminal | create_time | dt |
001 | d1 | 2023-08-15 10:20:22 | 20230815 |
001 | d2 | 2023-08-15 12:20:22 | 20230815 |
场景描述:
有一张用户操作记录表,一天内每5分钟算一个批次,如果5分钟内有一个操作记录则这5分钟有效,累计到总的有效时长里面。
需要统计用户在每个设备的总的有效时长
SELECT COUNT(1)*5
from (
SELECT create_terminal, COUNT(*) AS operation_count
from (
SELECT
create_terminal,
FLOOR(UNIX_TIMESTAMP(create_time) / (5 * 60)) AS batch
from temp.xx where create_time>='2023-08-10 00:49:34' and create_time<='2023-08-10 01:15:01' and create_user_code = '02802523'
) t
GROUP BY
create_terminal,
batch
HAVING
operation_count > 0;
) cc