DECLARE @key_id bigint
DECLARE @site_id bigint --站点编号
DECLARE @user_id bigint --用户编号
DECLARE @date_id int--日期编号
DECLARE @reftype_id int--来源类型
DECLARE @refsite_id bigint
DECLARE @hour_id int
DECLARE @p_id bigint
DECLARE @pt_id int
DECLARE @ipno bigint
DECLARE @ad_id int
DECLARE @add_time datetime
DECLARE @from_id int
DECLARE @keyword_id bigint
DECLARE @fromSite bigint
DECLARE @join_url bigint
DECLARE @visit_no int
DECLARE @pages_no int
DECLARE @is_old int
DECLARE @is_cookie int
DECLARE @settle_no int
DECLARE @suserId int
DECLARE @isMoni int
DECLARE @address_id int
DECLARE @fatherId int
DECLARE @ly_guest int
DECLARE @ly_items int
DECLARE @ly_ad int
DECLARE @ly_type int
DECLARE @ly_appid int
DECLARE @ref_site varchar(50)
DECLARE @ref_name varchar(50)
DECLARE @ref_keyword varchar(200)
DECLARE @url_join varchar(500)
DECLARE @is_true bigint
DECLARE @vyear varchar(50)
DECLARE @vmonth varchar(50)
DECLARE @vday varchar(50)
DECLARE @vhour varchar(50)
DECLARE @hour varchar(50)
DECLARE @true_site int
DECLARE @addsn int
DECLARE @talkid bigint
DECLARE @Userid bigint
DECLARE @etl_date varchar(50)
DECLARE @etl_state int
DECLARE @will_etldate varchar(50)
Set @vyear = LTRIM(str(Year(getdate())))
Set @vmonth = LTRIM(str(Month(getdate())))
Set @vday = LTRIM(str(Day(getdate())))
Set @vhour = LTRIM(str(DATEPART (hour, getdate())))
set @hour=@vyear+'-'+@vmonth+'-'+@vday+' '+@vhour+':00:00'
SELECT top 1 @etl_date=[etl_date],@etl_state=[etl_state]
FROM [Analytics_DB].[dbo].[ETL_Note]
where etl_type=8
order by etl_date desc
set @will_etldate =DATEADD(hour, 1, @etl_date)
if @etl_state=2 and DATEDIFF(hour, @will_etldate,@hour)>0 --上一次执行完毕 并且不是当前小时
begin
INSERT INTO [Analytics_DB].[dbo].[ETL_Note]
([etl_date],[etl_year],[etl_month],[etl_day],[etl_hour],[etl_state],etl_type)
VALUES
(DATEADD(hour, 1, @etl_date),LTRIM(str(Year(@will_etldate))),LTRIM(str(Month(@will_etldate))),LTRIM(str(Day(@will_etldate))),LTRIM(str(DATEPART (hour, @will_etldate))),1,8)
--定义游标
DECLARE Employee_OpenPV CURSOR FOR
SELECT [key_id],[site_id],[user_id],[ipno],[pt_id],[visit_no],[pages_no],[ref_type],[ref_site],
[ref_name],[ref_keyword],[url_join],[ad_id],[suser_id],[is_moni],[start_time],address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id
FROM [Analytics_DB].[dbo].[OpenPV] with (nolock)
where DATEDIFF(day, start_time,@will_etldate)=0 and DATEPART(hour, start_time)=DATEPART (hour, @will_etldate) and is_moni=0 and pt_id<>2
OPEN Employee_OpenPV
FETCH NEXT FROM Employee_OpenPV
INTO @key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId
WHILE @@FETCH_STATUS = 0
BEGIN
--检查站点编号
SELECT @true_site=count(*) FROM [Analytics_DW].[dbo].[Stat_Site] where site_id=@site_id
If @true_site=0
Begin
INSERT INTO [Analytics_DW].[dbo].[Error_PV]
([key_id],[site_id],[user_id],[ipno],[pt_id],[visit_no],[pages_no],[ref_type],[ref_site],[ref_name]
,[ref_keyword],[url_join],[ad_id],[suser_id],[is_moni],[add_time],[address_id],[ly_guest],[ly_items],[ly_ad]
,[ly_type],[ly_appid])
VALUES(@key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid)
End
Else
Begin
Set @vyear = LTRIM(str(Year(@add_time)))
Set @vmonth = LTRIM(str(Month(@add_time)))
Set @vday = LTRIM(str(Day(@add_time)))
Set @vhour = LTRIM(str(DATEPART (hour, @add_time)))
set @hour=@vyear+'-'+@vmonth+'-'+@vday+' '+@vhour+':00:00'
--处理小时编号
set @hour_id=@vhour
if @fatherId is null
set @fatherId=8
--处理日期编号
SELECT top 1 @date_id=[date_id] FROM [Analytics_DW].[dbo].[Stat_Date] where d_year=@vyear and d_month=@vmonth and d_day=@vday
if @date_id is null
set @date_id=1
if @isMoni=1
begin
set @p_id=1
set @refsite_id=1
set @from_id=1
set @keyword_id=1
INSERT INTO [All_OpenPV]
(key_id,[talk_id],[site_id],[user_id],[ip_no],[date_id],[hour_id],[reftype_id],
[refsite_id],from_id,keyword_id,[p_id],[pt_id],[ad_id],[suser_id],[is_moni],add_time,address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id)
VALUES
(@key_id,0,@site_id,@user_id,@ipno,@date_id,@hour_id,@reftype_id,@refsite_id,@from_id,@keyword_id,@p_id
,@pt_id,@ad_id,@suserId,@isMoni,@hour,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId)
end
else
begin
--处理页面编号
set @p_id=1
SELECT top 1 @p_id=[p_id] FROM [Analytics_DW].[dbo].[Stat_LyPage] where p_name=@url_join and site_id=@site_id
if @p_id is null
set @p_id=1
--处理来路站点编号
set @refsite_id=1
SELECT top 1 @refsite_id=[refsite_id] FROM [Analytics_DW].[dbo].[RefSite] where refsite_name=@ref_site and site_id=@site_id
if @refsite_id is null
set @refsite_id=1
--处理搜索引擎
if @ref_keyword is null or @ref_keyword=''
begin
set @from_id=1
set @keyword_id=1
end
else
begin
set @from_id=1
SELECT top 1 @from_id=[from_id] FROM [Analytics_DW].[dbo].[Stat_FromWeb] where from_name=@ref_name
if @from_id is null
set @from_id=1
end
set @keyword_id=1
--处理来自广告转换 搜索引擎 关键词转换
if @pt_id=1 or @pt_id=2
begin
if @keyword_id=1 and @ad_id=1
begin
select top 1 @ad_id=ad_id,@fatherId=father_id from [Analytics_DB].[dbo].[OpenPV] with(nolock)
where is_moni=1 and ipno=@ipno and add_time > DATEADD(day, -1, @add_time) order by key_id desc
if @ad_id<>1
begin
set @from_id=1
set @keyword_id=1
end
else
begin
set @ref_name=null
select top 1 @ref_name=ref_name from [Analytics_DB].[dbo].[OpenPV] with(nolock)
where is_moni=0 and ipno=@ipno and add_time > DATEADD(day, -1, @add_time) order by key_id desc
set @from_id=1
SELECT top 1 @from_id=[from_id] FROM [Analytics_DW].[dbo].[Stat_FromWeb] where from_name=@ref_name
if @from_id is null
set @from_id=1
end
end
end
--处理留言站点编号
set @is_true=null
if @pt_id=2 and @ref_site<>'9.txooo.com'
begin
SELECT top 1 @is_true=site_id FROM [Analytics_DW].[dbo].[Stat_Site] with(nolock) where siteurl=@ref_site
if @is_true is not null
set @site_id=@is_true
end
--写入PV
INSERT INTO [All_OpenPV]
(key_id,[talk_id],[site_id],[user_id],[ip_no],[date_id],[hour_id],[reftype_id],
[refsite_id],from_id,keyword_id,[p_id],[pt_id],[ad_id],[suser_id],[is_moni],add_time,address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id)
VALUES
(@key_id,0,@site_id,@user_id,@ipno,@date_id,@hour_id,@reftype_id,@refsite_id,@from_id,@keyword_id,@p_id
,@pt_id,@ad_id,@suserId,@isMoni,@hour,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId)
End
end
--break
--获取下一条
FETCH NEXT FROM Employee_OpenPV
INTO @key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId
END
--关闭游标
CLOSE Employee_OpenPV
DEALLOCATE Employee_OpenPV
--执行完毕
UPDATE [Analytics_DB].[dbo].[ETL_Note]
SET [etl_state] = 2,[endtime] = getdate()
WHERE etl_date=@will_etldate and etl_type=8
end
