收入分析

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'