深圳地铁AFC决策支持系统——游标是怎么干掉的？

主题：设备压数统计 

（一）分析视角

时间：年/季/月/日

站点：线路/站点

终端：设备

（二）分析指标

压数数量（是否正常上传交易数据，平时3000笔，今天只有1000笔，判定为2000笔没有上传，同一台设备和其前七天比较，在某个范围内浮动的比例，超出范围的要显示出，人工定参数来进行查询）

时间、车站、设备号

（三）数据源分析：

n         压数数量（交易异常）：拿今天的交易量与前七天的交易量进行比较，根据差值计算出一个比例，

提供一个标准比例（参数），拿计算出来的比例与标准比例进行对比，如果大于这个比例，则把超出范围的显示出来，显示包括：设备号，交易量，前七天交易量，误差值。

时间：td_common08. tradedate

站点：td_common08 .tradeaddress

设备类型：td_common08..terminalid

n         设备收入异常：用交易异常数据与RPT表关联，取出收入异常关键字不为0的记录，从而得到设备收入异常。

交易异常与收入异常的关系： 交易异常是通过每天的交易量与前7天平均交易量比较，得到当天交易异常（压数数量），而RPT是汇总TD_COMMON08表收入情况的异常数据，然后通过交易异常数据与RPT表关联，就得到既产生交易异常又产生收入异常的设备的数据，即设备交易异常数据。该指标确保了数据能够真正反映设备的交易异常情况，从而排除了某天客流量的确比前7天少但收入正常的设备交易异常的假数据。

（四）指标计算公式

交易量：某一设备当天的发生交易记录的记录总数。

参考值：前7天交易量/7

压数数量：参考值－当天交易量

误差值：（参考值－当天交易量）/参考值

应收票款：应收票款

实收票款：实收票款

差异 = 应收票款－实收票款

（五）实例

时间：2007-9-11

站点：罗湖站

设备：268000500

交易量：70000

参考值：80000

压数数量：80000－70000 = 10000

误差值：（80000－70000）/80000 = 0.125

源系统表及主题描述：
       数据来源只有一个叫TD_COMMON表的，是地铁前端业务表，地铁的每一个交易事务都会有一条记录写入到表中。每天大概会产生100万条以上的记录，每个月就有3000多万条记录。该主题是统计铁铁公司摆放到各个站点的交易设备每天产生的交易数量，从而知道哪台设备哪天发生交易异常。

业务情况描述：
        待续……

实现方法：
        一、使用游标，代码如下：

--**************************************************************************
-- 设备压数统计
--**************************************************************************
--存储过程
IF EXISTS (SELECT name
    FROM     sysobjects
    WHERE     name = N'P_EquipmentPress'
    AND type = 'P')
DROP PROCEDURE P_EquipmentPress
GO

CREATE PROCEDURE P_EquipmentPress
AS
BEGIN
    DECLARE @CUR_DATE    INTEGER        --当天日期
        ,@PRE_DATE    INTEGER
        ,@CUR_QTY    INTEGER            --当天交易数量
        ,@SUM_QTY    INTEGER            --前7天交易数量
        ,@TRADEADDRESS    VARCHAR(20)    --站点
        ,@CUR_RATE    NUMERIC
        ,@COM_QTY    NUMERIC            --参考值
        ,@PRESS_QTY    NUMERIC            --压数数量

        ,@STEPNAME    VARCHAR(2000)
        ,@SQLSTRING    VARCHAR(4000)
        ,@STEPDESC    VARCHAR(2000)

        ,@V_TERMINALID    VARCHAR(12)        --虚拟设备号

    TRUNCATE TABLE F_EquipmentPress

    --SET @CUR_DATE = 20070810
    SET @CUR_DATE = YEAR(GETDATE())*10000 + MONTH(GETDATE())*100
                                        + DAY(GETDATE() - 1)
    SET @PRE_DATE = @CUR_DATE - 7

    DECLARE C_TERMINALID CURSOR FOR
        SELECT DISTINCT TERMINALID,TRADEADDRESS
        FROM TD_COMMON08
        WHERE YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100
                        + DAY(TRADEDATE) = @CUR_DATE        --如果设备每天都没改变，则可取消该过滤条件

    OPEN C_TERMINALID

    FETCH NEXT FROM C_TERMINALID INTO @V_TERMINALID,@TRADEADDRESS
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        --当天交易量
        SELECT @CUR_QTY = COUNT(*)
        FROM TD_COMMON08
        WHERE YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100
                    + DAY(TRADEDATE) = @CUR_DATE
        AND TERMINALID = @V_TERMINALID

        --前7天交易量
        SELECT @SUM_QTY = COUNT(*)
        FROM TD_COMMON08
        WHERE YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)
            >= @PRE_DATE
        AND YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)
            <= @CUR_DATE - 1
        AND TERMINALID = @V_TERMINALID

        --参考值
        SET @COM_QTY = @SUM_QTY / 7
        --压数数量
        SET @PRESS_QTY = ABS(@CUR_QTY - @COM_QTY)
        --误差值
        SET @CUR_RATE = @PRESS_QTY / @COM_QTY

        INSERT INTO F_EquipmentPress
            (TRADEDATE,TRADEADDRESS,TERMINALID,CUR_QTY,COM_QTY,PRESS_QTY
                ,CUR_RATE)
        VALUES(@CUR_DATE,@TRADEADDRESS,@V_TERMINALID,@CUR_QTY
                        ,@COM_QTY,@PRESS_QTY,@CUR_RATE)

        FETCH NEXT FROM C_TERMINALID INTO @V_TERMINALID,@TRADEADDRESS
    END

    CLOSE C_TERMINALID
    DEALLOCATE C_TERMINALID
END

/**//*
时间花销统计：
    所有设备总量有750，即共有750个TERMINALID号
    每天一台设备的交易量需要运行SELECT COUNT(*) FROM TD_COMMON08 WHERE TERMINALID = '' AND TRADEDATE = ''语句，750台设备就需要运行
该语句750次。
    前7天一台设备的交易量需要运行SELECT COUNT(*) FROM TD_COMMON08 WHERE TERMINALID = '' AND TRADEDATE <= '' AND TRADEDATE >''语句，
750台设备需要运行750次。

*/

        以上注释提到，该法相当于在源表里执行多次COUNT运算，750台设备，每台设备要算一次是当天的交易数，一次七天的交易数，这样每台设备就两次COUNT运算，750台设备就有1500次COUNT运算。按照源系统，一天有100万条记录，一个月3000万条记录，一年下来（深圳地铁从2005年12月营运）有3亿条以上的记录。在这么庞大的数据里做这样的运算，即使一次COUNT运算花消1分钟，一天的数据就需要25个小时来进行运算，要产生历史数据，得到一年的数据，实际上根本无法实现。而且产生历史数据后，每天都要进行增量处理。源系统增量一次（每天进行）就有100万条数据，即每天都要用25个小时来运算，根本无法进行，因为实际上每次增量只能在凌晨的四五个小时完成上一天的统计。因此，上述使用游标的方法不可行。

二、使用查询语句进行，代码如下：

--
IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = N'P_EquipmentPress'
    AND type = 'P')
DROP PROCEDURE P_EquipmentPress
GO

--
CREATE PROCEDURE P_EquipmentPress
AS
BEGIN
    DECLARE @CUR_DATE    INTEGER
        ,@PRE_DATE    INTEGER

    TRUNCATE TABLE F_EquipmentPress
    
    --SET @CUR_DATE = YEAR(GETDATE())*10000 + MONTH(GETDATE())*100
                                        + DAY(GETDATE() - 1)
    SET @CUR_DATE = 20070808
    SET @PRE_DATE = @CUR_DATE - 7
    
    --所有设备的当天交易量，产生750条数据。参考：执行时间1分38秒，产生710条数据。
    SELECT TERMINALID
        ,TRADEADDRESS
        ,YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 +
            DAY(TRADEDATE) TRADEDATE
        ,COUNT(*) CUR_QTY
    INTO #TEMP_1
    FROM TD_COMMON08
    WHERE YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)
                    = @CUR_DATE
    AND TRADEADDRESS <> '0000000000'
    GROUP BY TERMINALID
        ,TRADEADDRESS
        ,YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)

    TRUNCATE TABLE T_1
    INSERT INTO T_1 (TERMINALID,TRADEADDRESS,TRADEDATE,CUR_QTY)
    SELECT TERMINALID,TRADEADDRESS,TRADEDATE,CUR_QTY
    FROM #TEMP_1
    
    --所有设备前7天交易量，产生750条数据。参考：执行时间1分34秒，产生729条数据。
    SELECT TERMINALID
        ,TRADEADDRESS
        ,COUNT(*) SUM_QTY
    INTO #TEMP_2
    FROM TD_COMMON08
    WHERE YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)
                    >= @PRE_DATE
    AND YEAR(TRADEDATE)*10000 + MONTH(TRADEDATE)*100 + DAY(TRADEDATE)
                    <= @CUR_DATE - 1
    AND TRADEADDRESS <> '0000000000'
    GROUP BY TERMINALID
        ,TRADEADDRESS

    TRUNCATE TABLE T_2
    INSERT INTO T_2 (TERMINALID,TRADEADDRESS,SUM_QTY)
    SELECT TERMINALID,TRADEADDRESS,SUM_QTY
    FROM #TEMP_2
    
    --联结两表，得到当天所有指标
    INSERT INTO F_EquipmentPress
        (TERMINALID,TRADEADDRESS,TRADEDATE,CUR_QTY,COM_QTY,PRESS_QTY
            ,CUR_RATE)
    SELECT ISNULL(A.TERMINALID,B.TERMINALID)
        ,ISNULL(A.TRADEADDRESS,B.TRADEADDRESS)
        ,ISNULL(A.TRADEDATE,@CUR_DATE)
        ,ISNULL(A.CUR_QTY,0)
        --,B.SUM_QTY
        ,B.SUM_QTY / 7
        ,ISNULL(A.CUR_QTY,0) - B.SUM_QTY/7
        ,(ISNULL(A.CUR_QTY,0) - B.SUM_QTY/7) / (B.SUM_QTY/7)
    FROM T_1 A
    FULL OUTER JOIN T_2 B
    ON A.TERMINALID = B.TERMINALID
    
    DROP TABLE #TEMP_1
    DROP TABLE #TEMP_2
END

/**//*
    事实表中的CUR_RAGE是0到1之间的数。
    该表可以反映三种情况：
    1、CUR_RATE = 0:说明该设备当天的交易量与前7天每天平均交易量相等或非常接近。
    2、CUR_RATE = -1：说明该设备当天交易量为0，或与前7天每天平均交易量相差非常大。
    3、CUR_RATE > 0：说明该设备当天交易量比前7天每天平均交易量大。
    4、CUR_RATE  < 0：说明该设备当天交易量比前7天每天平均交易量小。
    5、COM_QTY为空（NULL）：说明该设备前7天没有或几乎没有交易量。
*/

        使用查询语句分别对当天和前七天按照设备号进行GROUP BY汇总，这样得到两条SELECT语句，每条语句执行时间大概是1分30秒，两条语句执行完毕花消大概3分半钟。两条SELECT语句得到的结果记录最多也只有750条记录（750台设备），且并不是每台设备每天都有交易。把这两个记录集进行全连接，得到既有当天交易量，又有前七天交易量，还能得到哪台设备前七天没有产生交易而当天产生交易、前七天有产生交易而当天没有产生交易的设备情况。数据库的连接对于10万以内的数据来说，根本用不了多少秒时间，况且这两个表最多也就750条记录，根本用不到一秒就可以完成。故上面的存储过程绝大部分的时间就花消在那两个SELECT语句上，其余的连接和运算三两秒就可以搞定了，即每天的增量用不到五分钟就可以完成，而处理历史数据时，一个月最多就需要3分钟*30天=90分钟就可以得到（实际执行时间是1小时02分钟38秒），可以每天处理几个月的数据，分几天就可以把所有的历史数据处理完。