通过表中记录的注册、注销时间,按日期统计每天的注册、注销数量:select * from ( select NVL(v1.register_count,0) register_count, NVL(v2.logout_count,0) logout_count, NVL(v1.count_date,v2.count_date) count_date from (select count(*) as register_count, trunc(CREATE_TIME) as count_date from user group by trunc(CREATE_TIME)) v1 full join (select count(*) as logout_count, trunc(logout_time) as count_date from user where logout_time is not null group by trunc(logout_time)) v2 on v1.count_date = v2.count_date ) where count_date between to_date('2022-06-01 00:00:00','yyyy-MM-dd HH24:mi:ss') and to_date('2022-07-30 00:00:00','yyyy-MM-dd HH24:mi:ss') order by v1.count_date desc;
一条SQL统计每天的用户注册、注销数
于 2022-07-08 15:26:11 首次发布
该SQL查询用于统计2022年6月1日至7月30日期间,每天的用户注册和注销数量。通过全连接查询从user表中分别获取注册(CREATE_TIME)和注销(logout_time,非空)数据,然后按日期进行聚合,确保即使某天没有数据也会显示为0。结果按日期降序排列。
摘要由CSDN通过智能技术生成