常用的计算物业

SQLSERVER 常用KPI的计算.

SQL code

--基本上没有用SQL写过KPI,在里有人问起,觉得用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=\"\"

WHILE @DTE_BEGDATE= \"\" 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=\"\"

WHILE @DTE_BEGDATE= \"\" 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 _Year ,_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 _Year ,_Q , (_MONEY _MONEY )/_MONEY AS 季同比

FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON _Year=_Year-1 AND _Q=_Q

-------------------------------------

-- 计算环比(年,季,月)

-------------------------------------

SELECT _Year ,_Q , (_MONEY _MONEY )/_MONEY AS 季环比

FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON _Year=_Year AND _Q=_Q-1

-------------------------------------

-- 计算YTD/MTD/QTD

-- YTD 是年的第一天开始到年的最后一天的累计值.

-- MTD 是某年的某月的第一天开始到某年该月的最后一天的累计值.

-- QTD 是某年的某季的第一天开始到某年该季的最后一天的累计值.

-- 这几个都是累计值 . 知道了概念后,在原表就可以求出.不举例子了.

-------------------------------------

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