收入分析
2013-07-29 20:10 l25321937 阅读(325) 评论(0) 编辑 收藏 举报USE [MogoData] GO /****** Object: StoredProcedure [dbo].[AppStat_1DnN] Script Date: 07/11/2013 20:20:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: App统计单日多平台相关数据 -- ============================================= ALTER PROCEDURE [dbo].[AppStat_1DnN] @uid uniqueidentifier, --user ID @apps varchar(10), --app类型:all,iphone,andriod,ipad,wp,single_app @aid uniqueidentifier=null, --app ID @date datetime, --日期 @adtype varchar(10), --广告类型:banner,fullscreen,customsize,video,splash,offer @dtype varchar(10) --查询的数据类型: total_mogo,hour,app,country,network AS set transaction isolation level read uncommitted; BEGIN DECLARE @app_sql nvarchar(1000); DECLARE @adtype_sql nvarchar(1000); DECLARE @sql nvarchar(4000); DECLARE @ParmDefinition nvarchar(1000); -- /********* 加载要查询的App ************************************/ IF(@apps='all') SET @app_sql=' AND a.UID=@p_uid' IF(@apps='iphone') SET @app_sql=' AND a.UID=@p_uid AND a.[platform]=1' IF(@apps='andriod') SET @app_sql=' AND a.UID=@p_uid AND a.[platform]=2' IF(@apps='ipad') SET @app_sql=' AND a.UID=@p_uid AND a.[platform]=3' IF(@apps='wp') SET @app_sql=' AND a.UID=@p_uid AND a.[platform]=4' IF(@apps='single_app') SET @app_sql=' AND a.UID=@p_uid AND a.id=@p_aid' /********* 加载要查询的广告类型************************************/ IF(@adtype='banner') SET @adtype_sql=' AND (sd.adtype<=4 OR sd.adtype=8)' IF(@adtype='fullscreen') SET @adtype_sql=' AND (sd.adtype=6 OR sd.adtype=9)' IF(@adtype='customsize') SET @adtype_sql=' AND sd.adtype=10' IF(@adtype='video') SET @adtype_sql=' AND sd.adtype=11' IF(@adtype='splash') SET @adtype_sql=' AND sd.adtype=12' IF(@adtype='offer') SET @adtype_sql=' AND sd.adtype=512' /********* 加载要查询的广告类型************************************/ IF (@dtype='total_mogo') BEGIN SET @sql = 'SELECT null as ID,null as Title, sum(Impressions+blanks) as req, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Impressions END) as imp, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Clicks END) as clk, 0 as inc FROM Stat_Day sd JOIN App a ON AID = a.ID WHERE sd.[DateTime] = @p_Date ' + @app_sql + @adtype_sql END IF (@dtype='hour') BEGIN SET @sql = 'SELECT null as ID,DATEPART(hh ,dateadd(hh,8,[DateTime])) as Title, sum(Impressions+blanks) as req, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Impressions END) as imp, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Clicks END) as clk, 0 as inc FROM Stat_Hour sd JOIN App a ON AID = a.ID WHERE sd.[DateTime] >= dateadd(hh,-8,@p_date) AND sd.[DateTime]< dateadd(hh,16,@p_date)' + @app_sql + @adtype_sql + 'GROUP BY DATEPART(hh,[DateTime]) ORDER BY Title' END IF (@dtype='app') BEGIN SET @sql = 'SELECT aid as ID,a.name as Title, sum(Impressions+blanks) as req, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Impressions END) as imp, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Clicks END) as clk, 0 as inc FROM Stat_Day sd JOIN App a ON AID = a.ID WHERE sd.[DateTime] = @p_Date' + @app_sql + @adtype_sql + 'GROUP BY aid,name ORDER BY imp desc' END IF (@dtype='country') BEGIN SET @sql = 'SELECT null as ID, Country as Title, sum(Impressions+blanks) as req, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Impressions END) as imp, sum(CASE WHEN sd.Type=100 THEN 0 ELSE Clicks END) as clk, 0 as inc FROM Stat_Day sd JOIN App a ON AID = a.ID WHERE sd.[DateTime] = @p_Date AND sd.[Type] = @p_nid' + @app_sql + @adtype_sql + 'GROUP BY Country ORDER BY imp desc ' END IF (@dtype='network')--返回当日广告平台的芒果数据和收入 BEGIN SET @sql = 'select null as ID,t_sd.nwid as title, t_sd.req req, t_sd.imp imp, t_sd.clk clk, t_inc.Inc Inc FROM (select [type] as nwid,sum(Requests) req,sum(Impressions)imp, sum(Clicks) clk FROM Stat_Day WHERE [DateTime] = @P_Date' + @app_sql + @adtype_sql + 'GROUP BY NWID ) as t_sd LEFT JOIN (select NWID, sum(Inc) Inc FROM Network_Stat_Day sd JOIN App a ON a.id=sd.AID WHERE [Date] = @P_Date' + @app_sql + @adtype_sql + 'GROUP BY NWID) as t_inc on t_sd.NWID = t_inc.NWID' END SET @ParmDefinition = N'@p_uid uniqueidentifier, @p_apps varchar(10), @p_aid uniqueidentifier, @p_nid int, @p_date datetime'; print @sql; EXECUTE sp_executesql @sql, @ParmDefinition, @p_uid = @uid, @p_apps=@apps, @p_aid = @aid, @p_date = @date END -- --select -- null as ID,t_sd.nwid as title, -- t_sd.req tsd_req, -- t_sd.imp, -- t_sd.clk, -- t_sd.Inc, -- t_inc.Req, -- t_inc.INc, -- t_inc.Clk, -- t_inc.Inc -- from ( -- select nwid,sum(Requests) req,sum(Impressions)imp,sum(Clicks) clk,sum(Incomes)Inc -- FROM Stat_Day WHERE [DateTime] = '2013-2-1' GROUP BY NWID ) as t_sd -- LEFT JOIN -- (select NWID, sum(Req) Req ,sum(Imp) Imp,sum(Clk) Clk,sum(Inc) Inc FROM Network_Stat_Day WHERE [Date] = '2013-2-1' GROUP BY NWID) as t_inc -- on t_sd.NWID = t_inc.NWID exec [AppStat_1DnN] 'C925E123-5A54-419A-9C3B-ED6C1CCD57EE', 'andriod', '2de8e84f-9e59-4698-8a6e-de773df4eb2b', '2013-6-6', 'banner', 'total_mogo'