sqlserver的sp，存储过程

待我称王封你为后i 2022-06-02 05:38 270阅读 0赞

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