使用SQL的案例分析

小編給大家分享一下使用SQL的案例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

需求

所有部門匯總的結(jié)果的打分。大部分指標(biāo)是根據(jù)部門匯總(SUM或AVG)結(jié)果打分。
但面談率和前10%加班平均小時(shí)數(shù)倆指標(biāo)，不是根據(jù)所有部門(TEAM)匯總(SUM或AVG)結(jié)果打分。而是需要根據(jù)每個(gè)部門的
數(shù)據(jù)做排序或累加后的結(jié)果打分
面談率計(jì)算方式：TEAM1中季度1月份，面談人數(shù)比例目標(biāo)是33% -》 目標(biāo)面談人數(shù) = 部門人數(shù)*33%
               實(shí)際面談人數(shù) = 10
    TEAM1中季度2月份，面談人數(shù)比例目標(biāo)是60% -》 目標(biāo)面談人數(shù) = 部門人數(shù)*60%
               實(shí)際面談人數(shù) = 2 + 10（1月份實(shí)際面談人數(shù)）
    TEAM1中季度3月份，面談人數(shù)比例目標(biāo)是100% -》目標(biāo)面談人數(shù) = 部門人數(shù)*100%
               實(shí)際面談人數(shù) = 1 + 12（2月份實(shí)際面談人數(shù)）
這里部門人數(shù)是按季度統(tǒng)計(jì)每個(gè)季度部門的總?cè)藬?shù)(且不算領(lǐng)導(dǎo)，在之前存儲(chǔ)過程中部門人數(shù)已確定，CVS_DEPT_PAX_CNT表示），因?yàn)槊考径炔块T人數(shù)或部門關(guān)系都可能變更
計(jì)算每月實(shí)際面談率 = 實(shí)際面談人數(shù) / CAST(目標(biāo)面談人數(shù) AS NUMERIC(19,2)) * 100  
注意 這里是把整數(shù)轉(zhuǎn)換成小數(shù)保證不用整除計(jì)算。要求是小數(shù)
具體SQL如下
    /*每個(gè)group的統(tǒng)計(jì)*/
           
    DECLARE @CVS_SING_STAT AS TABLE
    ( 
       TEAM_CD VARCHAR(20)         
      ,GRP_CD VARCHAR(20)         
      ,CHECK_RADIO numeric(19,2) 
     
    )
     INSERT INTO @CVS_SING_STAT
     SELECT T.TEAM_CD,T.DEPT_CD GRP_CD,T.CHECK_RADIO
     FROM /*如下內(nèi)層查詢是在SQL SERVER 2008上做每行的累積運(yùn)算*/
       SELECT   SUBSTRING(BASE.DHSTC_CD,0,5) DH_YEAR ,BASE.STC_MONTH ,SUBSTRING(BASE.DHSTC_CD,5,8) DH_MONTH
         ,BASE.TEAM_CD,BASE.DEPT_CD,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT
         ,SUM(ROLL.H09_CNT) ACC
         ,SUM(ROLL.H09_CNT)/BASE.CVS_DEPT_PAX_CNT CVS_RAT
         --,33.0/100.0
         --,CAST(33.0/100.0 AS NUMERIC(19,2))
         --,SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) 就是實(shí)際面談人數(shù)/目標(biāo)面談人數(shù)
         --,CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2)) FLOATTEST
         ,CASE WHEN BASE.STC_MONTH = '01' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2))
         WHEN BASE.STC_MONTH = '02' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(66.0/100.0)) AS NUMERIC(19,2))
         WHEN BASE.STC_MONTH = '03' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(100.0/100.0)) AS NUMERIC(19,2))
        END CHECK_RADIO
      FROM T_DM_DHSTC_DEPT BASE
       ,T_DM_DHSTC_DEPT ROLL
      WHERE SUBSTRING(BASE.DHSTC_CD,0,5) = SUBSTRING(ROLL.DHSTC_CD,0,5)--YEAR
      AND   BASE.STC_QUATR = ROLL.STC_QUATR
      AND   BASE.STC_MONTH >= ROLL.STC_MONTH
      AND   BASE.TEAM_CD = ROLL.TEAM_CD
      AND   BASE.DEPT_CD = ROLL.DEPT_CD     
       -- AND   BASE.DEPT_CD = 'EA190086'
      GROUP BY SUBSTRING(BASE.DHSTC_CD,0,5),BASE.TEAM_CD,BASE.DEPT_CD,SUBSTRING(BASE.DHSTC_CD,5,8),BASE.STC_MONTH,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT
       
     )T
     WHERE T.DH_YEAR = @YEAR AND T.DH_MONTH = @MONTH
            
            
            
                /*每個(gè)TEAM的統(tǒng)計(jì)*/
           
    DECLARE @CVS_TEAM_STAT AS TABLE
    ( 
       TEAM_CD VARCHAR(20)         
      ,GRP_CD VARCHAR(20)         
      ,CHECK_RADIO numeric(19,2) 
    )
     DECLARE @CVS_TEAM_BASE AS TABLE
         ( 
          DH_YEAR  VARCHAR(20)
            ,STC_QUATR VARCHAR(20)
            ,STC_MONTH VARCHAR(20)
            ,DH_MONTH  VARCHAR(20)
            ,TEAM_CD VARCHAR(20)         
            ,DEPT_CD VARCHAR(20)
            ,CVS_GRP_PAX_CNT  numeric(19,2)
            ,H09_CNT      numeric(19,2)    
          
         )
                   
                INSERT INTO @CVS_TEAM_BASE
                SELECT T1.DH_YEAR,T2.STC_QUATR,T1.DH_MONTH,T2.STC_MONTH,T2.TEAM_CD,'-' GRP_CD
                      ,SUM(CVS_DEPT_PAX_CNT) CVS_GRP_PAX_CNT
                      ,SUM(T2.H09_CNT) ACC_H09_CNT
                                    
                FROM  T_DM_DHSTC      T1
                     ,T_DM_DHSTC_DEPT T2
                WHERE
                --and   TEAM_CD = 'EA190001'
                      T1.DHSTC_CD = T2.DHSTC_CD
                GROUP BY  T1.DH_YEAR,T2.STC_QUATR,T1.DH_MONTH,T2.STC_MONTH
                         ,T2.TEAM_CD
                        
                INSERT INTO @CVS_TEAM_STAT
                SELECT T.TEAM_CD,'-' GRP_CD,T.CHECK_RADIO
                FROM(
                     SELECT BASE.DH_YEAR,MAX(BASE.STC_QUATR) STC_QUATR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT
                          ,SUM(ROLL.H09_CNT) ACC
                          ,BASE.CVS_GRP_PAX_CNT
                          ,SUM(ROLL.H09_CNT)/BASE.CVS_GRP_PAX_CNT CVS_RAT
                         ,CASE  WHEN BASE.STC_MONTH = '01' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2))
                                WHEN BASE.STC_MONTH = '02' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(66.0/100.0)) AS NUMERIC(19,2))
                                WHEN BASE.STC_MONTH = '03' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(100.0/100.0)) AS NUMERIC(19,2))
                            END CHECK_RADIO
                           
         FROM @CVS_TEAM_BASE BASE
             ,@CVS_TEAM_BASE ROLL
         WHERE BASE.DH_YEAR = ROLL.DH_YEAR
         --AND   BASE.DH_MONTH = ROLL.DH_MONTH
         AND   BASE.STC_QUATR = ROLL.STC_QUATR
      
         AND   BASE.DH_MONTH >= ROLL.DH_MONTH
      
         AND   BASE.TEAM_CD = ROLL.TEAM_CD    
      
         GROUP BY BASE.DH_YEAR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT,BASE.CVS_GRP_PAX_CNT
        
     )T
     WHERE T.DH_YEAR = @YEAR AND T.DH_MONTH = @MONTH

以上是“使用SQL的案例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！