YnTGGvjT

1
2DROP PROCEDURE IF EXISTS generate_daily_activity_log;
3DELIMITER //
4
5CREATE PROCEDURE generate_daily_activity_log(window INT)
6BEGIN
7	
8    DECLARE currentUser VARCHAR(100) DEFAULT "";
9    DECLARE start_ts DATETIME;
10    DECLARE update_ts DATETIME;
11    DECLARE end_ts DATETIME;
12	DECLARE exit_loop BOOLEAN;
13    
14	DECLARE user_list_cursor CURSOR FOR 
15    SELECT us.user_key FROM user_sessions us WHERE us.start_timestamp > now() - INTERVAL window day	GROUP BY us.user_key	ORDER BY us.user_key asc;
16    
17    DECLARE activity_cursor CURSOR FOR
18    	SELECT 
19		p.user_key, 
20		p.start_timestamp, 
21		p.update_timestamp,
22		p.end_timestamp
23	FROM 
24		fusion_api.user_sessions p
25	WHERE 
26		p.start_timestamp > now() - INTERVAL window day
27	AND
28		(p.end_timestamp is not null OR p.update_timestamp is not null)
29	ORDER BY 
30		p.user_key asc,
31		p.start_timestamp DESC, 
32		p.update_timestamp DESC;
33        
34	
35    
36    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
37    
38	CREATE TEMPORARY TABLE IF NOT EXISTS activity_results (user_key VARCHAR(60), minutes_sum LONG);
39    CREATE TEMPORARY TABLE IF NOT EXISTS activity (user_key VARCHAR(60), ts_start datetime, ts_update datetime, ts_end datetime, minutes_sum LONG);
40    CREATE TEMPORARY TABLE IF NOT EXISTS user_table ( user_key varchar(60));
41    
42    INSERT INTO user_table
43	SELECT p.user_key FROM fusion_api.user_sessions p WHERE p.start_timestamp > now() - INTERVAL window day	GROUP BY p.user_key	ORDER BY p.user_key asc;
44    
45    select * from user_table;
46    
47    OPEN activity_cursor;
48	activityLoop: loop
49		FETCH activity_cursor INTO currentUser,start_ts, update_ts, end_ts;
50        IF exit_loop THEN
51			CLOSE activity_cursor;
52            LEAVE activityLoop;
53		END IF;
54        INSERT INTO activity (user_key, ts_start, ts_update, ts_end, minutes_sum)
55		SELECT 
56			currentUser, 
57			start_ts, 
58			update_ts,
59			end_ts,
60			(CASE
61				WHEN (end_ts is not null AND update_ts is null AND end_ts < DATE_ADD(end_ts, interval 300 minute)) THEN
62					(sum(TIME_TO_SEC(end_ts) - TIME_TO_SEC(start_ts))/60)
63				WHEN (end_ts is not null AND end_ts < DATE_ADD(update_ts, interval 30 minute)) THEN
64					(sum(TIME_TO_SEC(end_ts) - TIME_TO_SEC(start_ts))/60)
65				WHEN (update_ts is not null AND update_ts < DATE_ADD(update_ts, interval 30 minute)) THEN
66					(sum(TIME_TO_SEC(update_ts) - TIME_TO_SEC(start_ts))/60)
67				ELSE
68					0
69			END);
70	END loop activityLoop;
71    
72    select * from activity;
73        
74	OPEN user_list_cursor;
75	userLoop: loop
76		FETCH user_list_cursor INTO currentUser;
77        IF exit_loop THEN
78			CLOSE user_list_cursor;
79            LEAVE userLoop;
80		END IF;
81        INSERT INTO activity_results (user_key, minutes_sum)
82		SELECT a.user_key, SUM(a.minutes_sum) FROM activity a WHERE a.user_key = currentUser group by a.user_key;
83	END loop userLoop;
84	-- CLOSE user_list_cursor;
85    
86    select * from activity_results;
87
88   
89
90DROP TABLE IF EXISTS activity_results;
91DROP TABLE IF EXISTS activity;
92DROP TABLE IF EXISTS user_table;
93END //
94DELIMITER ;