[原]SQLSERVER 常用KPI的计算.

Question

--基本上没有用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 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)
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 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_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 是某年的某季的第一天开始到某年该季的最后一天的累计值.
-- 这几个都是累计值 . 知道了概念后,在原表就可以求出.不举例子了.
---------------------------------------其它的,如 总体贡献度,父子贡献度,受欢迎程度 等等,可以用类拟的方法求出.

Answer 1

up.不过，单从语句上来讲，LZ尚需磨练。

Answer 2

YTD,MTD,QTD,WTD,相关MDX都是支持的，没必要搞这么复杂。
如：
 SUM(YTD(),[Measures].[度量值]) : 本年1日到现在累加。 ( 
    ParallelPeriod(
             [Dim_Time].[年 - 月].[年],
              1,
            [Dim_Time].[年 - 月].CurrentMember
    ), [Measures].[度量值]
)
: 去年同期数据。你只关心把相关模型建好。层级的数据，可以用简单的MDX支持。比如：parent,child 都可以操作层级数据。 

Answer 3

这种数据没必要用SQL实现，数据量大，性能就跟不上了。

Answer 4

其实现在不管是什么数据据库，我接触的微软的和甲骨文，他们的存储原理很相近，说白了还是关系性数据库，数据量以上千万，出一张复杂的报表很慢，而这种as数据库，不仅速度奇快，你在用多维数据浏览器在前台拖动的时候非常快。抛砖引玉：同期的mdx表达式：([Measures].[工业企业增加值累计值],ParallelPeriod([D Date Month].[datetime].[year],1))

Answer 5

，请注意：父子和层级是两个概念，本人做立方的时候比较喜欢父子，做维度dill 比较方便，层级就有点不方便了，详细见我的blog：http://blog.csdn.net/muzhenxing013/archive/2009/06/28/4304965.aspx

Answer 6

弱弱的问下,KPI是啥?我见过工厂的KPI这个术语,可是不是用SQL算的

Answer 7

 确实是，不管是sql server的analysis services（as数据库） 还是ibm的congnos 的transformer和framework（数据包）有一个重要的功能就是提高了访问的速度

Answer 8

KPI
Key Performance Indicators就是关键绩效指标
KPI可以按业务分,如 生产KPI,销售KPI,仓库KPI,财会KPI,客户KPI 等等.
KPI相关资料网上也不少.

Answer 9

终于有人讨论业务啦，我们公司的 KPI ，PKI ，IKI也挺烦的，那SQL啊，都是上千行的（历史遗留问题），看得人都傻了，表没设计好，维护起来简直是噩梦。

Answer 10

KPI 绩效分析，PKI 毛利指数分析，IKI 仓储物品呆滞料消耗率分析，翻译不精确，大概就是这个意思

Answer 11

我们公司正在用KPI考核员工，哎，正烦这个咋个在CSDN也有KPI,躲都躲不掉...

Answer 12

Key Performance Indicators 关键业绩指标
企业的生产过程是劳动者运用劳动工具改变劳动对象的过程。在企业生产的三个基本要素（劳动力、劳动资料、劳动对象）中，劳动力是最重要的因素，正确的统计、分析、预测劳动生产力指标，对于企业有序地组织生产、充分开发、合理利用人力资源有着重要意义

Answer 13

KPI 
Key Performance Indicators就是关键绩效指标 

Answer 14

觉得用SQL写KPI关键是中间表的设计.而且,中间表对速度要求也比较高.可以参考另一贴子:
http://topic.csdn.net/u/20090313/10/e90163f0-e36c-43a7-81d1-4095c33a5a3e.html
从某种癔义来讲,如果原来从事过数据仓库的人,对KPI的理解可能更透一些,设计出相关KPI的中间表更容易一些.有人在QQ里说起:你说的父子贡献度现在我们单位也有,只不过,代码很长,差不多有一千行,能不能简化?
如果要简化,关键是你的中间表如何设计.如果你原来数据库的商品基础表是平衡树的话,这很容易办.如果是不平衡的话,那就有点麻烦了.  :)
一般来说,很多工厂目前用到的系统,他的商品编号可以是任意的,没有规则.而原先做系统时也没有想过要使用规则.为后来各种数据的统计,或上数据仓库等都造成了很大的麻烦.我觉得一个数据库系统的编码是有规则的.是应该按层次来编号的.例如说商品编号.
001  =>上衣 上衣下的某个品类/品牌 001001 某品牌下的商品  001001000001 
这样的话,你处理起来非常的容易,也很容易理解.
不扯太远了,说一下关于平衡的父子的项献度如何设计相关的中间表.1.按层横向.以三层为例:商品名称 第一层编号 第一层名称 第二层编号 第二层名称 第三层编号 第三层的名称 ....2.商品名称/SUM(商品所在的第三层) = 具体商品在该层的项献度.
  SUM(第三层)/SUM(第三层所在的第二层) = 第三层在第二层的贡献度.
  SUM(第二层)/SUM(第二层所在的第一层) = 第二层在第一层的项献度 .
  SUM(第一层)/总和 = 第一层对全部的贡献度.3.SQL语句就不完了.这东西,说着简单,不过,弄起来还有点长.反正,有了相关的思路,程序毕竟不是难事.4.如果是不平衡的,呵呵,那只能自己写函数处理了.要求到下面第一个叶的和.程度可能很受影响.