[原]SQLSERVER 常用KPI的计算.解决思路

--基本上没有用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))goDECLARE @DTE_BEGDATE DATETIMESET @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)ENDGO-------------------------------------------建立测试业务表-----------------------------------------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 DATETIMEDECLARE @Int_BillID        IntSET @Int_BillID=1SET @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 @[email protected]_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_TABSELECT  A.Int_Year ,A.Int_Q ,NULL,NULL,SUM(INT_AMOUNT),SUM(INT_MONEY) --因为是统计季度,所以 月 和 天 是 NULLFROM         ( 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 AGROUP BY Int_Year,Int_QGO--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 是某年的某季的第一天开始到某年该季的最后一天的累计值.-- 这几个都是累计值 . 知道了概念后,在原表就可以求出.不举例子了.---------------------------------------其它的,如 总体贡献度,父子贡献度,受欢迎程度 等等,可以用类拟的方法求出.