mysql如何实现用户行为分析

 CREATE TABLE user_behavior (     id BIGINT AUTO_INCREMENT PRIMARY KEY,     user_id INT NOT NULL,     action_type VARCHAR(50) NOT NULL,  -- 如 'login', 'click', 'purchase'     page_url VARCHAR(255),             -- 访问页面     referrer VARCHAR(255),             -- 来源页面     device VARCHAR(50),                -- 设备类型     ip_address VARCHAR(45),     created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); 

 SELECT      DATE(created_at) AS date,     COUNT(DISTINCT user_id) AS dau FROM user_behavior  WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY date; 

行者AI

行者ai绘图创作，唤醒新的灵感，创造更多可能

100

查看详情

 WITH steps AS (     SELECT          user_id,         MAX(action_type = 'login') AS has_login,         MAX(action_type = 'add_to_cart') AS has_cart,         MAX(action_type = 'purchase') AS has_purchase     FROM user_behavior      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)     GROUP BY user_id ) SELECT      COUNT(*) AS total_users,     SUM(has_login) AS login_count,     SUM(has_cart) AS cart_count,     SUM(has_purchase) AS purchase_count,     ROUND(SUM(has_login)/COUNT(*),2) AS login_rate,     ROUND(SUM(has_purchase)/SUM(has_login),2) AS conv_rate FROM steps; 

 SELECT      page_url,     COUNT(*) AS visits FROM user_behavior  WHERE action_type = 'page_view' GROUP BY page_url ORDER BY visits DESC LIMIT 10; 

 -- 找出首日活跃用户，并检查第二天是否再次出现 SELECT      DAY(first_active) AS cohort_day,     COUNT(*) AS new_users,     COUNT(second_day.user_id) AS retained_users,     ROUND(COUNT(second_day.user_id)/COUNT(*),2) AS retention_rate FROM (     SELECT user_id, MIN(DATE(created_at)) AS first_active     FROM user_behavior      GROUP BY user_id ) first_day LEFT JOIN (     SELECT user_id, DATE(created_at) AS activity_day     FROM user_behavior      GROUP BY user_id, DATE(created_at) ) second_day  ON first_day.user_id = second_day.user_id  AND second_day.activity_day = DATE_ADD(first_day.first_active, INTERVAL 1 DAY) GROUP BY cohort_day;