报表数据采集procedure_备份

-- Procedure "sns_login_reg" DDL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sns_login_reg`(in start_day varchar(255),in end_day varchar(255))
begin

declare counts int;

declare next_id int default 1;

declare currentday varchar(255);

declare all_user int;

declare new_booker int;

declare old_sns int;

declare new_sns int;

declare today date;

declare days int;

CREATE TEMPORARY TABLE user_login_reg(

row_id int auto_increment primary key ,

rang_day varchar(255) ,

sum_user int,

new_user_booker int,

old_user int,

new_user_sns int

);


set today= start_day;

if(end_day>start_day)

then

SELECT DATEDIFF(end_day,start_day) into days;

while days>0

do

insert into user_login_reg(rang_day) values(today);

set today=ADDDATE(today,1);

set days=days-1;

end while;

end if;

select count(row_id) into counts from user_login_reg;

while counts>0 do

select rang_day into currentday from user_login_reg where row_id=next_id;

select count(distinct(uid)) into all_user from sns.uchome_actionlog where substring(dateline,1,10)=currentday and uid!=-1 and uid !=cid;

select count(uid) into new_booker from sns.uchome_space where substring(FROM_UNIXTIME(dateline),1,10) =currentday and productId=1 ;

select count(uid) into new_sns from sns.uchome_space where substring(FROM_UNIXTIME(dateline),1,10) =currentday and productId=1000 ;


/*select count(uid) from (select uid,dateline from sns.uchome_space where uid in (select distinct(uid) from sns.uchome_actionlog where substring(dateline,1,10)=currentdayand uid!=-1)) as old_user where substring(from_unixtime(old_user.dateline),1,10)!=currentday */

/*select count(distinct(uid)) into old_sns from sns.uchome_actionlog where substring(dateline,1,10)=currentday and uid!=-1 and uid not in(select uid from sns.uchome_space where substring(from_unixtime(dateline),1,10)=currentday);*/

set old_sns=all_user-new_booker-new_sns;

update user_login_reg set sum_user=all_user,new_user_booker=new_booker,old_user=old_sns,new_user_sns=new_sns where rang_day=currentday;

set next_id=next_id+1;

set counts=counts-1;

end while;

select * from user_login_reg;

drop table user_login_reg;

end;
***************************************************************************************************************


-- Procedure "sns_statistics_pv" DDL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sns_statistics_pv`(in end_day varchar(255))
begin

declare counts int;

declare next_id int default 1;

declare currentday varchar(255);

declare pv int;

declare login_user_pv int;

declare anonymous_user_pv int;

declare app_pv int;

declare login_user_app_pv int;

declare anonymous_user_app_pv int;

CREATE TEMPORARY TABLE statistics4pv(

row_id int auto_increment primary key ,

rang_day varchar(255) ,

pv_count int,

login_average_pv int,
anonymous_average_pv int,

app_pv_count int,

app_login_average_pv int,

app_anonymous_average_pv int

);

insert into statistics4pv(rang_day) select distinct(substring(dateline,1,10)) from sns.uchome_actionlog where dateline<end_day;

select count(row_id) into counts from statistics4pv;

while counts>0 do

select rang_day into currentday from statistics4pv where row_id=next_id;

select count(logid) into pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday;

select ifnull(count(logid) /count(distinct(uid)),'0') into login_user_pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday and uid!=-1 ;

select ifnull(count(logid) /count(distinct(cid)),'0') into anonymous_user_pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday and uid=-1 ;

select count(logid) into app_pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday and actionid>60 and actionid<72;

select ifnull(count(logid) /count(distinct(uid)),'0') into login_user_app_pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday and actionid>60 and actionid<72 and uid!=-1 ;

select ifnull(count(logid) /count(distinct(cid)),'0') into anonymous_user_app_pv from sns.uchome_actionlog where substring(dateline,1,10)=currentday and actionid>60 and actionid<72 and uid =-1 ;

update statistics4pv set pv_count=pv,login_average_pv=login_user_pv,anonymous_average_pv=anonymous_user_pv,app_pv_count=app_pv,app_login_average_pv=login_user_app_pv,app_anonymous_average_pv=anonymous_user_app_pv where rang_day=currentday;

set next_id=next_id+1;

set counts=counts-1;

end while;

select * from statistics4pv;

drop table statistics4pv;

end;