sqlserver的sp，存储过程

USE [Top_Tool]
GO
/****** Object:  StoredProcedure [dbo].[op_search_flow_port_analysis_src_account]    Script Date: 2018/1/5 9:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================


-- Author: <Author,Amy>


-- Create date: <20170227>


-- Description: <解密流量_流量口分析>


--op_search_flow_port_analysis_src_account '736522816','','20160219','20170228','','','','','1'


--op_search_flow_port_analysis_src_account '736522816','','20170219','20170228','','访客数','desc','1','2'


--op_search_flow_port_analysis_src_account '736522816','42044399475','20170219','20170228','','访客数','desc','1','3'


--Modify:  <转移到塔内 20170914>
-- =============================================


 


ALTER PROCEDURE [dbo].[op_search_flow_port_analysis_src_account]


    (


      @sellerid BIGINT ,


      @auction_id BIGINT ,


      @begintime BIGINT ,


      @endtime BIGINT ,


      @remark NVARCHAR(50) ,


      @sortname NVARCHAR(100) ,


      @sorttype NVARCHAR(50) ,


      @page INT ,


      @tp INT-- 1：模糊查询推荐 2.店铺各宝贝流量来源概况、流量来源下各宝贝概况 3.宝贝下撰列表


    )


AS


    BEGIN


        SET NOCOUNT ON;


        DECLARE @SQL1 NVARCHAR(500) ,


            @SQL NVARCHAR(500) ,


            @name NVARCHAR(200) ,


            @begin INT ,


            @end INT ,


            @name1 NVARCHAR(100)






        --IF LEN(@sortname) = 0


        --    BEGIN


        --        SELECT  @sortname = '访客数' ,


        --                @sorttype = 'desc'


        --    END






        IF @begintime = ''


            BEGIN


                SELECT  @endtime = MAX(thedate) FROM   ysf_ShopTraffic  WHERE   seller_id = @sellerid


                SELECT  @begintime = REPLACE(DATEADD(DAY, -6, CONVERT(DATE, CONVERT(VARCHAR(20), @endtime))),'-', '') 


            END 






        --IF @tp = '1'


        --    BEGIN


        --        SELECT DISTINCT


        --                a.thedate ,


        --                a.src_id ,


        --                b.src_parent_id_name ,


        --                a.uv


        --        INTO    #PC


        --        FROM    [dbo].[Shop_ysf_ShopSrcLastEffect_Integrate] a ( NOLOCK ) ,


        --                [dbo].[Dim_src] b ( NOLOCK )


        --        WHERE   seller_id = @sellerid


        --                AND thedate BETWEEN @begintime AND @endtime


        --                AND a.src_id = b.src_id


        --                AND b.[platform] = '2'


     


        --        INSERT  INTO #PC


        --                SELECT DISTINCT


        --                        thedate ,


        --                        src_id ,


        --                        '其他' ,


        --                        uv


        --                FROM    [dbo].[Shop_ysf_ShopSrcLastEffect_Integrate] NOLOCK


        --                WHERE   seller_id = @sellerid


        --                        AND thedate BETWEEN @begintime AND @endtime


        --                        AND src_level = '2'


        --                        AND src_id NOT IN (


        --                        SELECT DISTINCT


        --                                src_id


        --                        FROM    [dbo].[Dim_src] NOLOCK


        --                        WHERE   [platform] = '2' ) 






        --        SELECT DISTINCT


        --                a.thedate ,


        --                a.src_id ,


        --                b.src_parent_id_name ,


        --                a.uv


        --        INTO    #WIFI


        --        FROM    [dbo].[ShopWirelessPlatformSrc] a ( NOLOCK ) ,


        --                [dbo].[Dim_src] b ( NOLOCK )


        --        WHERE   seller_id = @sellerid


        --                AND thedate BETWEEN @begintime AND @endtime


        --                AND a.src_id = b.src_id


        --                AND b.[platform] = '1'


     


        --        INSERT  INTO #WIFI


        --                SELECT DISTINCT


        --                        thedate ,


        --                        src_id ,


        --                        '其他' ,


        --                        uv


        --              FROM    [dbo].[ShopWirelessPlatformSrc] NOLOCK


        --                WHERE   seller_id = @sellerid


        --                        AND thedate BETWEEN @begintime AND @endtime


        --                        AND src_level = '3'


        --                        AND src_id NOT IN (


        --                        SELECT DISTINCT


        --                                src_id


        --                        FROM    [dbo].[Dim_src] NOLOCK


        --                        WHERE   [platform] = '1' ) 






        --        SELECT DISTINCT


        --                src_parent_id_name ,


        --                SUM(uv) AS uv


        --        INTO    #PC_last


        --        FROM    #PC


        --        GROUP BY src_parent_id_name


        --        SELECT DISTINCT


        --                src_parent_id_name ,


        --                SUM(uv) AS uv


        --        INTO    #WIFI_last


        --        FROM    #WIFI


        --        GROUP BY src_parent_id_name






        --        SELECT DISTINCT


        --                src_parent_id_name ,


        --                uv ,


        --                '1' AS [platform]


        --        INTO    #ALL


        --        FROM    #WIFI_last


        --        INSERT  INTO #ALL


        --                SELECT DISTINCT


        --                        src_parent_id_name ,


        --                        uv ,


        --                        '2'


        --                FROM    #PC_last






        --        SELECT DISTINCT


        --                src_parent_id_name ,


        --                SUM(uv) AS UV


        --        FROM    #ALL


        --        GROUP BY src_parent_id_name


        --        RETURN


        --    END






        IF LEN(@sortname) = 0


            BEGIN


                SELECT  @sortname = '访客数' ,


                        @sorttype = 'desc' 


            END






        IF @sortname = '浏览量/访客数'


            BEGIN


                SELECT  @sortname = '[浏览量/访客数]'


            END






        SELECT DISTINCT


                a.thedate ,


                a.src_id ,


                b.src_parent_id_name ,


                a.item_id ,


                a.iuv ,


                a.ipv ,


                a.pay_ord_byr_cnt_holotree_lastbe_guide ,


                a.pay_ord_amt_holotree_lastbe_guide ,


                b.[platform]


        INTO    #ALL1


        FROM    ysf_ItemSourceLastEffect  a  ,


                [dbo].[Dim_src] b  


        WHERE   seller_id = @sellerid


                AND thedate BETWEEN @begintime AND @endtime


                AND a.src_id = b.src_id


                AND b.[platform] = '2'


                AND b.src_id_name <> '购物车'






        INSERT  INTO #ALL1


                SELECT DISTINCT


                        a.thedate ,


                        a.src_id ,


                        b.src_id_name ,


                        a.item_id ,


                        a.iuv ,


                        a.ipv ,


                        a.pay_ord_byr_cnt_holotree_lastbe_guide ,


                        a.pay_ord_amt_holotree_lastbe_guide ,


                        '2'


                FROM    ysf_ItemSourceLastEffect a  ,


                        ysf_DimPcsrc b  


                WHERE   seller_id = @sellerid


                        AND a.thedate BETWEEN @begintime AND @endtime


                        AND a.src_id = b.src_id


                        AND b.src_id_name = '自主访问'


AND b.thedate =20170725
    


        INSERT  INTO #ALL1


                SELECT DISTINCT


                        a.thedate ,


                        a.src_id ,


                        b.src_parent_id_name ,


                        a.item_id ,


                        a.iuv ,


                        a.ipv ,


                        a.pay_ord_byr_cnt_holotree_lastbe_guide ,


                        a.pay_ord_amt_holotree_lastbe_guide ,


                        b.[platform]


                FROM    ysf_ItemAppSrcPlatform a   ,


                       [dbo].[Dim_src] b ( NOLOCK )


                WHERE   seller_id = @sellerid


                        AND thedate BETWEEN @begintime AND @endtime


                        AND a.src_id = b.src_id


                        AND b.[platform] = '1'


                        AND b.src_id_name <> '购物车'






        INSERT  INTO #ALL1


                SELECT DISTINCT


                        a.thedate ,


                        a.src_id ,


                        '自主访问' ,


                        a.item_id ,


                        a.iuv ,


                        a.ipv ,


                        a.pay_ord_byr_cnt_holotree_lastbe_guide ,


                        a.pay_ord_amt_holotree_lastbe_guide ,


                        '1'


                FROM     ysf_ItemAppSrcPlatform a  ,


                        ysf_DimWirelessSrc b  


                WHERE   seller_id = @sellerid


                        AND a.thedate BETWEEN @begintime AND @endtime


                        AND a.src_id = b.src_id


                        AND b.src_id in ('21.1','21.2','21.3','41.3.1','41.3.2','41.3.3','42.3.1','42.3.2','43.3.1','43.3.2')


and  b.thedate =20170725
 
        SELECT DISTINCT


                item_id ,


                src_parent_id_name ,


                SUM(iuv) AS iuv ,


                SUM(ipv) AS ipv ,


                ( CASE WHEN SUM(iuv) > 0


                       THEN CONVERT(DECIMAL(18, 2), ( CONVERT(DECIMAL(18, 2), SUM(ipv))


                                                      / SUM(iuv) ))


                       ELSE 0


                  END ) AS [ipv/iuv] ,


                SUM(pay_ord_byr_cnt_holotree_lastbe_guide) AS pay_ord_byr_cnt_holotree_lastbe_guide ,


                CONVERT(DECIMAL(18, 2), SUM(pay_ord_amt_holotree_lastbe_guide)) AS pay_ord_amt_holotree_lastbe_guide


        INTO    #All_last


        FROM    #ALL1


        GROUP BY item_id ,


                src_parent_id_name






        INSERT  INTO #All_last


                SELECT DISTINCT


                        item_id ,


                        'ALL' ,


                        SUM(iuv) AS iuv ,


                        SUM(ipv) AS ipv ,


                        ( CASE WHEN SUM(iuv) > 0


                               THEN CONVERT(DECIMAL(18, 2), ( CONVERT(DECIMAL(18,


                                                              2), SUM(ipv))


                                                              / SUM(iuv) ))


                               ELSE 0


                          END ) AS [ipv/iuv] ,


                        SUM(pay_ord_byr_cnt_holotree_lastbe_guide) AS pay_ord_byr_cnt_holotree_lastbe_guide ,


                        CONVERT(DECIMAL(18, 2), SUM(pay_ord_amt_holotree_lastbe_guide)) AS pay_ord_amt_holotree_lastbe_guide


                FROM    #ALL1


                GROUP BY item_id






        SELECT DISTINCT


                a.item_id AS 宝贝ID ,


                b.auction_picture AS 图片路径 ,


                b.auction_name AS 标题 ,


                a.src_parent_id_name AS 来源路径 ,


                a.iuv AS 访客数 ,


                a.ipv AS 浏览量 ,


                a.[ipv/iuv] AS [浏览量/访客数] ,


                a.pay_ord_byr_cnt_holotree_lastbe_guide AS 成交人数 ,


                CONVERT(DECIMAL(18, 2), a.pay_ord_amt_holotree_lastbe_guide) AS 成交金额


        INTO    #Last


        FROM    #All_last a ,


                open_auction b ( NOLOCK )


        WHERE   b.seller_id = @sellerid


                AND a.item_id = b.auction_id










        IF @tp = '1'


            BEGIN


                SELECT TOP 10


                        宝贝ID ,


                        图片路径 ,


                        标题


                FROM    #Last


                WHERE   来源路径 = 'ALL'


                ORDER BY 访客数 DESC
                RETURN    
            END






        IF @tp = '2'


            BEGIN


                SELECT  @begin = ( @page - 1 ) * 10 + 1 ,


                        @end = @page * 10


                SELECT  @name = '宝贝ID,图片路径,标题,访客数,浏览量,[浏览量/访客数],成交人数,成交金额'






    IF LEN(@remark) = 0


                    BEGIN


                        SELECT  @SQL1 = 'select count(*) as 宝贝个数 from #Last WHERE 来源路径='''


                                + 'ALL' + ''''


                        SELECT  @SQL = 'select ' + @name


                                + ' from #Last WHERE 标题 in (SELECT 标题 FROM (SELECT 标题,'


                                + @sortname + ' ,ROW_NUMBER() OVER(ORDER BY '


                                + @sortname + ' ' + @sorttype


                                + ') AS RowRank  FROM #Last WHERE 来源路径='''


                                + +'ALL' + ''') AS t1 WHERE RowRank BETWEEN '


                                + CONVERT(NVARCHAR(10), @begin) + ' AND '


                                + CONVERT(NVARCHAR(10), @end) + ' and 来源路径='''


                                + 'ALL' + ''') ORDER BY  ' + @sortname + ' '


                                + @sorttype





                    END


                ELSE


                    BEGIN


                        SELECT  @SQL1 = 'select count(*) as 宝贝个数 from #Last WHERE 来源路径='''


                                + 'ALL''' + ' and (标题 like ''' + '%' + @remark


                                + '%' + '''' + ' or 宝贝ID like ''' + '%'


                                + @remark + '%' + '''' + ')'


                        SELECT  @SQL = 'select ' + @name


                                + ' from #Last WHERE 标题 in (SELECT 标题 FROM (SELECT 标题,'


                                + @sortname + ' ,ROW_NUMBER() OVER(ORDER BY '


                                + @sortname + ' ' + @sorttype


                                + ') AS RowRank  FROM #Last WHERE 来源路径='''


                                + +'ALL' + '''' + ' and (标题 like ''' + '%'


                                + @remark + '%' + '''' + ' or 宝贝ID like '''


                                + '%' + @remark + '%' + ''''


                                + ')) AS t1 WHERE RowRank BETWEEN '


                                + CONVERT(NVARCHAR(10), @begin) + ' AND '


                                + CONVERT(NVARCHAR(10), @end) + ' and 来源路径='''


                                + 'ALL' + ''') ORDER BY ' + @sortname + ' '


                                + @sorttype


                    END 


               


        EXEC(@SQL)
EXEC(@SQL1)
    
            END






        IF @tp = '3'


            BEGIN


                SELECT  @SQL = 'SELECT DISTINCT 宝贝ID,图片路径,标题,来源路径,访客数,浏览量,[浏览量/访客数],成交人数,成交金额 FROM #Last WHERE 宝贝ID='


                        + CONVERT(NVARCHAR(50), @auction_id)


                        + ' AND  来源路径<>''' + 'ALL' + ''' ORDER BY '


                        + @sortname + ' ' + @sorttype
                
 
                EXEC(@SQL)


            END 










    END