SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10') , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2016-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2016-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2016-10-10', 'pc' , '2016-10-10') , ('U007', 'F', '1950-07-18', '2016-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2016-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2016-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2016-10-16', 'pc' , NULL ) ; DROP TABLE IF EXISTS action_log; CREATE TABLE action_log( session varchar(255) , user_id varchar(255) , action varchar(255) , category varchar(255) , products varchar(255) , amount integer , stamp varchar(255) ); INSERT INTO action_log VALUES ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('989004ea', 'U001', 'view' , NULL , NULL , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'favorite', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'review' , 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 18:01:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001,D002', NULL, '2016-11-03 18:02:00') , ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 19:00:00') , ('47db0370', 'U002', 'purchase', 'drama' , 'D001' , 1000, '2016-11-03 20:00:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 20:30:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A004' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A005' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A006' , NULL, '2016-11-04 12:00:00') , ('9afaf87c', 'U002', 'purchase', 'drama' , 'D002' , 1000, '2016-11-04 13:00:00') , ('9afaf87c', 'U001', 'purchase', 'action', 'A005,A006', 1000, '2016-11-04 15:00:00') ; WITH stats AS ( --ログ全体のユニークユーザー数を求める SELECT COUNT(DISTINCT session) AS total_uu FROM action_log ) SELECT 1.action, --アクションUU COUNT(DISTINCT 1.session) AS action_uu, --アクション数 COUNT(1) AS action_count, --全体UU s.total_uu, 100.0*COUNT(DICTINCT 1.session) / s.total_uu AS usage_rate, --一人当たりアクション数:アクション数/アクションuu 1.0 * COUNT(1) / COUNT(DISTINCT 1.session) AS count_per_user FROM action_log AS 1 --ログ全体のユニークユーザー数を全レコードに結合する CROSS JOIN stats AS s GROUP BY 1.action, s.total_uu ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear