DELIMITER $$

DROP PROCEDURE IF EXISTS `mztest`.`proc_test1`$$
CREATE PROCEDURE `mztest`.`proc_test1` ()

-- 创建文章aid 与 总的点击次数click的映射表
create table archive_click
(
aid int PRIMARY KEY,
click int
);
insert into archive_click(aid,click)
select aid, count(*)
from access
group by aid
order by aid;

-- 创建具有少量必要信息的临时表
-- tmp1_table大小约为1,000,000
-- 因为archive_click表中aid为主码，所以速度不会很慢
create table tmp1_table
select aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;

-- 分组统计,得到union_ip
-- tmp2_table大小约为3831*52 = 200,000
-- 主要是一个排序过程，时间复杂度为O(nlogn) = 4,000,000
create table tmp2_table
select aid, date, count(distinct(ipadress)) union_ip
from tmp1_table
group by aid,date;

-- 将所有信息融合到一个表中
-- 由于都是主码上的连接，所以速度也不会很慢
-- 如果慢的话，这里改起来也容易
create table editor_record
select U.ID uid, U.userid username, T2.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T1.click click, T2.union_ip union_ip, T2.date time
from tmp2_table T2, tmp1_table T1, archives A, user U
where T2.aid = T1.aid and T2.aid = A.id and A.adminID = U.ID;

drop table archive_click;

drop table tmp1_table;

drop table tmp2_table;

END   $$

DELIMITER ;

-- 选择数据库
use mztest;


-- 以下是创建楼主需要的表，用于测试
create table user
(
 ID int(10) PRIMARY KEY auto_increment   ,
 userid varchar(30)
);
create table archives
(
ID  int(11)   unsigned   PRIMARY KEY auto_increment ,
click  int(11)   unsigned ,
title   varchar(80) ,
pubdate int(11),
adminID int(11) ,
maxpage int(4)
);
create table access
 (
 id int(4) PRIMARY KEY auto_increment  ,
aid int(4),
ipaddress varchar(15),
time bigint(4)
);


-- 创建文章aid与总的点击次数click的映射表
create table archive_click
(
aid int(11) PRIMARY KEY,
click int(11)
);
create table tmp1_table
select A.aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;


-- 创建具有少量必要信息的临时表
-- 得到tmp1_table大小约为1,000,000
-- 执行过程中因为archive_click表中aid为主码，所以速度不会很慢
create table tmp2_table
select aid, date, count(*) click, count(distinct(ipaddress)) union_ip
from tmp1_table
group by aid,date;


-- 分组统计,得到单日的click和union_ip
-- 得到tmp2_table大小约为3831*52 = 200,000
-- 执行主要是对tmp1_table表中元组一个排序过程，时间复杂度为O(nlogn)=20,000,000 
create table editor_record
select U.ID uid, U.userid username, T.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T.click click, T.union_ip union_ip, T.date time
from tmp2_table T, archives A, user U
where T.aid = A.id and A.adminID = U.ID;


-- 删除临时表
drop table tmp1_table;
drop table tmp2_table;
drop table archive_click;

-- 选择数据库
use mztest;


-- 以下是创建楼主需要的表，用于测试
create table user
(
 ID int(10) PRIMARY KEY auto_increment   ,
 userid varchar(30)
);
create table archives
(
ID  int(11)   unsigned   PRIMARY KEY auto_increment ,
click  int(11)   unsigned ,
title   varchar(80) ,
pubdate int(11),
adminID int(11) ,
maxpage int(4)
);
create table access
 (
 id int(4) PRIMARY KEY auto_increment  ,
aid int(4),
ipaddress varchar(15),
time bigint(4)
);


-- 创建文章aid与总的点击次数click的映射表
create table archive_click
(
aid int(11) PRIMARY KEY,
click int(11)
);
insert into archive_click(aid,click)
select aid, count(*)
from access
group by aid
order by aid;


-- 创建具有少量必要信息的临时表
-- 得到tmp1_table大小约为1,000,000
-- 执行过程中因为archive_click表中aid为主码，所以速度不会很慢
create table tmp1_table
select A.aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;


-- 分组统计,得到单日的click和union_ip
-- 得到tmp2_table大小约为3831*52 = 200,000
-- 执行主要是对tmp1_table表中元组一个排序过程，时间复杂度为O(nlogn)=20,000,000 
create table tmp2_table
select aid, date, count(*) click, count(distinct(ipaddress)) union_ip
from tmp1_table
group by aid,date;


-- 将所有信息融合到一个表中
-- 由于都是主码上的连接，所以速度也不会很慢
create table editor_record
select U.ID uid, U.userid username, T.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T.click click, T.union_ip union_ip, T.date time
from tmp2_table T, archives A, user U
where T.aid = A.id and A.adminID = U.ID;


-- 删除临时表
drop table tmp1_table;
drop table tmp2_table;
drop table archive_click;