使用SQL的案例分析

148次閱讀

共計 3691 個字符，預計需要花費 10 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下使用 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