使用SQL的案例分析

小编给大家分享一下使用SQL的案例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

需求

所有部门汇总的结果的打分。大部分指标是根据部门汇总(SUM或AVG)结果打分。

但面谈率和前10%加班平均小时数俩指标，不是根据所有部门(TEAM)汇总(SUM或AVG)结果打分。而是需要根据每个部门的

数据做排序或累加后的结果打分

面谈率计算方式：TEAM1中季度1月份，面谈人数比例目标是33% -》 目标面谈人数 = 部门人数*33%

               实际面谈人数 = 10

TEAM1中季度2月份，面谈人数比例目标是60% -》 目标面谈人数 = 部门人数*60%

               实际面谈人数 = 2 + 10（1月份实际面谈人数）

    TEAM1中季度3月份，面谈人数比例目标是100% -》目标面谈人数 = 部门人数*100%

实际面谈人数 = 1 + 12（2月份实际面谈人数）

这里部门人数是按季度统计每个季度部门的总人数(且不算领导，在之前存储过程中部门人数已确定，CVS_DEPT_PAX_CNT表示），因为每季度部门人数或部门关系都可能变更

计算每月实际面谈率 = 实际面谈人数 / CAST(目标面谈人数 AS NUMERIC(19,2)) * 100

注意 这里是把整数转换成小数保证不用整除计算。要求是小数

具体SQL如下

/*每个group的统计*/

    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 /*如下内层查询是在SQL SERVER 2008上做每行的累积运算*/

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)) 就是实际面谈人数/目标面谈人数

--,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

                /*每个TEAM的统计*/

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的案例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！