mysql 统计日活、周活、月活

1. 昨天的日活

select COUNT(DISTINCT device_id) INTO day_active_user FROM drs_app_startup_log where to_days( now( ) ) - to_days( FROM_UNIXTIME(ROUND(created_at_ms / 1000),'%Y-%m-%d %H:%i:%s')) <= 1;

2. 当前周的周活

SELECT COUNT(DISTINCT device_id) INTO week_active_user FROM drs_app_startup_log WHERE YEARWEEK(FROM_UNIXTIME(ROUND(created_at_ms / 1000),'%Y-%m-%d')) = YEARWEEK(now());

3. 当前月的月活

select COUNT(DISTINCT device_id) INTO month_active_user from drs_app_startup_log where FROM_UNIXTIME(ROUND(created_at_ms / 1000),'%y%m') = date_format( curdate( ) , '%y%m' );