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;
日志分析sql
最新推荐文章于 2022-06-11 09:18:29 发布