SQLSERVER 常用KPI的计算.

SQLSERVER 常用KPI的计算.

SQL code
--基本上没有用SQL写过KPI,在QQ里有人问起,觉得用SQL试试,呵呵.
--本文只是抛砖引玉.欢迎参观.

create table Timer (
    DTE_Date DateTime Not null,
    Int_Year int not null,
    Int_Q Int Not null,
    Int_M Int Not null,
    Int_D Int Not null   
 CONSTRAINT [PK_Timer] PRIMARY KEY  
(
    DTE_Date ASC
))

go

DECLARE @DTE_BEGDATE DATETIME
SET @DTE_BEGDATE='2000-1-1'
WHILE @DTE_BEGDATE<= '2099-12-31' BEGIN
        INSERT INTO Timer VALUES(@DTE_BEGDATE,YEAR(@DTE_BEGDATE),
        CASE WHEN MONTH (@DTE_BEGDATE)>=1 AND MONTH (@DTE_BEGDATE)<=3 THEN 1
             WHEN MONTH (@DTE_BEGDATE)>=4 AND MONTH (@DTE_BEGDATE)<=6 THEN 2
             WHEN MONTH (@DTE_BEGDATE)>=7 AND MONTH (@DTE_BEGDATE)<=9 THEN 3
             WHEN MONTH (@DTE_BEGDATE)>=10 AND MONTH (@DTE_BEGDATE)<=12 THEN 4
        END
        ,MONTH (@DTE_BEGDATE),DAY(@DTE_BEGDATE) )
       
        SET @DTE_BEGDATE =DATEADD (DAY,1,@DTE_BEGDATE)
END

GO

-----------------------------------------
--建立测试业务表
-----------------------------------------
CREATE TABLE BILL_SELL(
    DTE_DATETIME    DATETIME,
    VAR_BILLID        VARCHAR(32),
    VAR_PRODUCT        VARCHAR(64),
    INT_AMOUNT        INT,
    INT_MONEY        INT
 CONSTRAINT [PK_BILL_SELL] PRIMARY KEY  
(
    VAR_BILLID ASC
))

CREATE INDEX IX_SELL_DATETIME ON BILL_SELL (DTE_DATETIME)

GO

-----------------------------------------
--填充业务表.
-----------------------------------------
DECLARE @DTE_BEGDATE DATETIME
DECLARE @Int_BillID        Int
SET @Int_BillID=1
SET @DTE_BEGDATE='2007-1-1'
WHILE @DTE_BEGDATE<= '2009-12-31' BEGIN
        INSERT INTO BILL_SELL VALUES(@DTE_BEGDATE, @Int_BillID,LEFT(NEWID(),1),RAND() * 1000,RAND()*1000 )
        SET @Int_BillID=@Int_BillID+1
        SET @DTE_BEGDATE =DATEADD (DAY,1,@DTE_BEGDATE)
END
 
GO

/**************************************/
--1.建立中间表.
CREATE TABLE JX_TAB (
    Int_Year int null,
    Int_Q Int null,
    Int_M Int null,
    Int_D Int null,   
    INT_AMOUNT FLOAT NULL,
    INT_MONEY FLOAT NULL,
)

CREATE INDEX IX_T_YEAR  ON JX_TAB(INT_YEAR)
CREATE INDEX IX_T_Q  ON JX_TAB(Int_Q)
CREATE INDEX IX_T_M  ON JX_TAB(Int_M)
CREATE INDEX IX_T_D  ON JX_TAB(Int_D)

GO

-------------------------------------
-- 计算同比(年,季,月).
-------------------------------------
--2.将数据 按统计时间 抽到中间表.
INSERT INTO  JX_TAB
SELECT  A.Int_Year ,A.Int_Q ,NULL,NULL,SUM(INT_AMOUNT),SUM(INT_MONEY) --因为是统计季度,所以 月 和 天 是 NULL
FROM
        ( SELECT YEAR(DTE_DATETIME) AS Int_Year
                ,CASE WHEN MONTH (DTE_DATETIME)>=1 AND MONTH (DTE_DATETIME)<=3 THEN 1
                     WHEN MONTH (DTE_DATETIME)>=4 AND MONTH (DTE_DATETIME)<=6 THEN 2
                     WHEN MONTH (DTE_DATETIME)>=7 AND MONTH (DTE_DATETIME)<=9 THEN 3
                     WHEN MONTH (DTE_DATETIME)>=10 AND MONTH (DTE_DATETIME)<=12 THEN 4
                END AS Int_Q
                ,MONTH (DTE_DATETIME) AS Int_M
                ,DAY(DTE_DATETIME) AS Int_D
                ,INT_AMOUNT
                ,INT_MONEY
        FROM BILL_SELL ) AS A
GROUP BY Int_Year,Int_Q

GO

--3.计算同比,计算 月,日,周 的,依次类推.
SELECT A.Int_Year ,A.Int_Q , (A.INT_MONEY -B.INT_MONEY )/B.INT_MONEY AS 季同比
FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON A.Int_Year=B.Int_Year-1 AND A.Int_Q=B.Int_Q

-------------------------------------
-- 计算环比(年,季,月)
-------------------------------------
SELECT A.Int_Year ,A.Int_Q , (A.INT_MONEY -B.INT_MONEY )/B.INT_MONEY AS 季环比
FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON A.Int_Year=B.Int_Year AND A.Int_Q=B.Int_Q-1

-------------------------------------
-- 计算YTD/MTD/QTD
-- YTD 是年的第一天开始到年的最后一天的累计值.
-- MTD 是某年的某月的第一天开始到某年该月的最后一天的累计值.
-- QTD 是某年的某季的第一天开始到某年该季的最后一天的累计值.
-- 这几个都是累计值 . 知道了概念后,在原表就可以求出.不举例子了.
-------------------------------------

--其它的,如 总体贡献度,父子贡献度,受欢迎程度 等等,可以用类拟的方法求出.