日志分析sql

CREATE EXTERNAL TABLE if not exists nginx_20160619(
 host STRING,  
  uri STRING,  
  request_method STRING,  
  remote_addr STRING,  
  http_marketChannelName STRING,  
  http_hwMac STRING, 
  http_ethMac STRING,   
  http_hwModel STRING,  
  http_appVerName STRING,
  time_local_status STRING,
  body_bytes_sent string,
  bytes_sent string,
  gzip_ratio string,
  http_referer STRING, 
  http_cookie STRING,
  http_user_agent STRING, 
  http_x_forwarded_for STRING,
  upstream_addr STRING,
  upstream_response_time string,
  upstream_status STRING,
 request_time string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'  location '/nginx/logs_20160619’;



统计一天的日活
select count(distinct http_hwMac,http_ethMac) from nginx_20160619 

按照渠道统计
select http_marketChannelName,count(distinct http_hwMac,http_ethMac) as usernum from nginx_20160619 group by http_marketChannelName having usernum >=50 order by usernum desc  ;


新增用户

select count(*) from (select distinct nginx_20160620.http_hwMac from nginx_20160620) as new left join (select distinct nginx_20160619.http_hwMac from nginx_20160619) as old on new.http_hwMac=old.http_hwMac where old.http_hwMac is null;

有效启动次数
select count(*) from nginx_20160619 where trim(uri)='/api/channel/list';


累计用户
create table UserCount(UserNum bigint);
insert into table UserCount select count(distinct http_hwMac,http_ethMac) from nginx_20160619;

insert into table UserCount select count(*) from (select distinct nginx_20160620.http_hwMac from nginx_20160620) as new left join (select distinct nginx_20160619.http_hwMac from nginx_20160619) as old on new.http_hwMac=old.http_hwMac where old.http_hwMac is null;


select sum(*) from UserCount;



已有的用户
create table User(http_hwMac string,http_ethMac String,UserIncreaseDate Timestamp);
insert into table User select distinct http_hwMac,http_ethMac,now() from nginx_20160619; 

insert into table User select new.http_hwMac,new.http_ethMac,now() from (select distinct http_hwMac,http_ethMac,case when http_hwMac is null then http_ethMac when http_ethMac is null then http_hwMac else ' '  end  from nginx_20160620) as new left join (select * from User) as old on (new.http_hwMac=old.http_hwMac) and (new.http_ethMac=old.http_ethMac) where (old.http_hwMac is null) ;



select count(*) from User; 


每日用户次日留存率 



select count(*) from (select distinct http_hwMac,http_ethMac ,case when http_hwMac is null then http_ethMac when http_ethMac is null then http_hwMac else ' '  end  from nginx_20160620) as new inner join (select distinct http_hwMac,http_ethMac,case when http_hwMac is null then http_ethMac when http_ethMac is null then http_hwMac else ' '  end   from nginx_20160619) as old on (new.http_hwMac=old.http_hwMac) 



新增用户次日留存率

select count(*) from (select * from User where UserIncreaseDate like '2016-06-27 17:43:17.409') as old left join (select count(distinct nginx_20160620.http_hwMac,nginx_20160620.http_ethMac) from nginx_20160620) as new on new.http_hwMac=old.http_hwMac where new.http_hwMac is not null;


select count(*) from (select * from user_test where UserIncreaseDate like '2016-06-27 17:29:44.937') as old left join (select distinct test7.http_hwMac,test7.http_ethMac from test7) as new on new.http_hwMac=old.http_hwMac where new.http_hwMac is not null;



select uri,count(uri) as urinum from nginx_20160620 group by uri having urinum >=1000  order by urinum desc  ;
select http_hwModel,count( http_hwModel) as modelnum from nginx_20160620 group by http_hwModel having modelnum>=1000 order by modelnum desc  ;



select substring_index(time_local_status, ' ', 0) from nginx_20160619 limit 10;