remo postmotemの活用について

こんにちは 2020年9月26日,27日とおこなわれた ジョブレインボーLGBT仕事博も大盛況のうちに終わりました。

そして今はその次の日。

熱も冷めやらぬまま各種数字のまとめに入るところです。

1. mysqlに取り込んでみる

以下のテーブルを作りました

create table if not exists remo_attendee_record (
  id integer not null auto_increment primary key,
  fullname varchar(512),
  email varchar(256),
  event varchar(256),
  registered_at datetime,
  login_at datetime,
  logout_at datetime,
  time_spent integer )
ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

csvをexcelに読み込んだ後は、以下の式を同じ行に貼り付ければインサート文になります

="insert into remo_attendee_record (fullname, email, event, registered_at, login_at, logout_at, time_spent
) values ('"&A2&"','"&B2&"','"&F2&"','"&TEXT(G2,"yyyy-mm-dd hh:mm")&"','"&TEXT(H2,"yyyy-mm-dd hh:mm")&"','"&TEXT(I2,"yyyy-mm-dd hh:mm")&"',"&J2&");"

そのまま読み込むのであれば、上のSQLで充分なのですが、
今回は複数のイベントを2にかけて行ったため、分析を楽にするために event_idと外部システム連携用に real_event_id という外部向けカラム、そして取り込み失敗時にまとめてdeleteできるように import_ver カラムを追加します

drop table remo_attendee_record;
create table if not exists remo_attendee_record (
  id integer not null auto_increment primary key,
  fullname varchar(512),
  email varchar(256),
  event_id integer,
  real_event_id integer,
  event varchar(256),
  registered_at datetime,
  login_at datetime,
  logout_at datetime,
  time_spent integer,
  import_ver varchar(32),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP )
ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

なので、例えば

="insert into remo_attendee_record (fullname, email, event, registered_at, login_at, logout_at, time_spent, event_id, real_event_id,import_ver) values ('"&A2&"','"&B2&"','"&F2&"','"&TEXT(G2,"yyyy-mm-dd hh:mm")&"','"&TEXT(H2,"yyyy-mm-dd hh:mm")&"','"&TEXT(I2,"yyyy-mm-dd hh:mm")&"','"&J2&"',"&K2&","&L2&",'"&M2&"');"

というExcel関数を使ってinsert文を作ったりしています

ついでに分析用に参加予定リストもインポートします

create table if not exists expo_registered (  
  id integer not null auto_increment primary key,  
  email varchar(256)  
 )  
ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;  

2. 分析例

同時に講演会イベントと展示ブースが別eventとして実施されていて、それが、26日、27日の午前・午後の4回、計8イベントがあるとかの場合

1. 奇数イベントが講演会で、偶数イベントが展示ブースのため、その種別を出力
2. loginの時間を基準に、「流入時間別の入場者数」「平均滞在時間」を一覧表にする

→ mysqlだと、case when .. then .. end と group byを使います

select  
 case mod(event_id, 2) when 1 then 'stage' when 0 then 'booth' end 'type' , 
 count(email),
  avg(time_spent),
case 
when login_at>'2020-09-26 09:30' and login_at<'2020-09-26 10:05' then '26日10:05 以前'
when login_at>'2020-09-26 10:05' and login_at<'2020-09-26 10:35' then '26日10:05～'
when login_at>'2020-09-26 10:35' and login_at<'2020-09-26 11:05' then '26日10:35～'
when login_at>'2020-09-26 11:05' and login_at<'2020-09-26 11:35' then '26日11:05～'
when login_at>'2020-09-26 11:35' and login_at<'2020-09-26 12:05' then '26日11:35～'
when login_at>'2020-09-26 12:05' and login_at<'2020-09-26 12:35' then '26日12:05～'
when login_at>'2020-09-26 12:35' and login_at<'2020-09-26 13:05' then '26日12:35～'
when login_at>'2020-09-26 13:05' and login_at<'2020-09-26 13:35' then '26日13:05～'
when login_at>'2020-09-26 14:35' and login_at<'2020-09-26 15:05' then '26日15:05 以前'
when login_at>'2020-09-26 15:05' and login_at<'2020-09-26 15:35' then '26日15:05～'
when login_at>'2020-09-26 15:35' and login_at<'2020-09-26 16:05' then '26日15:35～'
when login_at>'2020-09-26 16:05' and login_at<'2020-09-26 16:35' then '26日16:05～'
when login_at>'2020-09-26 16:35' and login_at<'2020-09-26 17:05' then '26日16:35～'
when login_at>'2020-09-26 17:05' and login_at<'2020-09-26 17:35' then '26日17:05～'
when login_at>'2020-09-26 17:35' and login_at<'2020-09-26 18:05' then '26日17:35～'
when login_at>'2020-09-26 18:05' and login_at<'2020-09-26 18:35' then '26日18:05～'
when login_at>'2020-09-27 09:30' and login_at<'2020-09-27 10:05' then '27日10:05 以前'
when login_at>'2020-09-27 10:05' and login_at<'2020-09-27 10:35' then '27日10:05～'
when login_at>'2020-09-27 10:35' and login_at<'2020-09-27 11:05' then '27日10:35～'
when login_at>'2020-09-27 11:05' and login_at<'2020-09-27 11:35' then '27日11:05～'
when login_at>'2020-09-27 11:35' and login_at<'2020-09-27 12:05' then '27日11:35～'
when login_at>'2020-09-27 12:05' and login_at<'2020-09-27 12:35' then '27日12:05～'
when login_at>'2020-09-27 12:35' and login_at<'2020-09-27 13:05' then '27日12:35～'
when login_at>'2020-09-27 13:05' and login_at<'2020-09-27 13:35' then '27日13:05～'
when login_at>'2020-09-27 14:35' and login_at<'2020-09-27 15:05' then '27日15:05 以前'
when login_at>'2020-09-27 15:05' and login_at<'2020-09-27 15:35' then '27日15:05～'
when login_at>'2020-09-27 15:35' and login_at<'2020-09-27 16:05' then '27日15:35～'
when login_at>'2020-09-27 16:05' and login_at<'2020-09-27 16:35' then '27日16:05～'
when login_at>'2020-09-27 16:35' and login_at<'2020-09-27 17:05' then '27日16:35～'
when login_at>'2020-09-27 17:05' and login_at<'2020-09-27 17:35' then '27日17:05～'
when login_at>'2020-09-27 17:35' and login_at<'2020-09-27 18:05' then '27日17:35～'
when login_at>'2020-09-27 18:05' and login_at<'2020-09-27 18:35' then '27日18:05～'
 else 'other' end 入場時間帯
from remo_attendee_record
where time_spent >0 
group by event_id, 入場時間帯