SQLServer2005中的数据定时统计方法研究

王睿

摘要：在软件系统研发过程中，数据统计汇总的效率始终是难点，如何快速有效地将零碎的业务数据统计汇总成报表数据，成为数据库开发人员的研究重点。文章介绍了一种基于视图+存储过程+静态数据表+任务的方式，对报表数据进行定时统计汇总。

关键词：SQLServer2005；数据定时统计方法；业务数据；报表数据；数据库 文献标识码：A

中图分类号：TP311 文章编号：1009-2374（2016）17-0021-03 DOI：10.13535/j.cnki.11-4406/n.2016.17.009

1 概述

SQLServer数据库是Microsoft公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点，可跨越从运行Microsoft Windows 98的膝上型电脑到运行Microsoft Windows 2012的大型多处理器的服务器等多种平台使用。目前最新版本是SQLServer2012。本文主要针对日常工作中比较常用版本SQLServer2005进行分析，研究其数据定时统计的相关方法，并附实例加以说明。

2 软件项目数据统计中遇到的问题

在日常工作中，我们会经常遇到类似于这样的问题：“一张统计报表需要从十几张，甚至几十张业务基础数据表中汇总。”传统的处理方法是直接拼接SQL查询语句，然后将查询结果传递给报表控件，这样的处理方法看似没有问题，但随着业务数据的不断增加，用户打开报表的速度越来越慢，最后报表直接无法打开或者打开需要很长时间，从而导致整个项目的用户体验大打折扣。

3 数据定时统计方法的研究

面对这一问题，我们可以采用数据定时统计的方法将基础业务数据的统计结果保存在一张业务表中，然后数据报表直接从统计结果表中获取数据，如此一来，数据统计效率大大提升，同时也简化了报表编码的复杂度。

我们在SQLServer2005中编写基础业务数据统计视图，然后建立与视图结构一致的数据表，同时编写存储过程将视图统计的结果性数据同步到结果表中，最后采用SQLServer2005的定时任务，定期执行同步数据的存储过程即可，具体实现方法及步骤，如图1所示。

案例说明：

定时统计各单位累计消费情况：

第一步：创建视图

CREATE VIEW [V_ORG_ CONSUME]

AS

SELECT O.CODE_，O.NAME_， SUM（C.MONEY_） AS TOTALMONEY_

FROM T_ CONSUME AS C

LEFT JOIN T_ACTOR AS A ON A.ID=C.ACTORID_

LEFT JOIN T_ACTOR_ORG AS AO A.ID=AO.ACTORID_

LEFT JOIN T_ORG AS O ON AO.ORGID_=O.ID

GROUP BY O.ID，O.CODE_，O.NAME_

ORDER BY ID

第二步：创建统计结果表

CREATE TABLE [TV_ORG_ CONSUME]（

[ID] [bigint] NOT NULL，

[CODE_] [varchar]（100） NULL，

[NAME_] [varchar]（200） NULL，

[TOTALMONEY_] [float]NULL

PRIMARY KEY CLUSTERED

（[ID]ASC）WITH（PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF， IGNORE_DUP_KEY = OFF， ALLOW_ROW_LOCKS= ON， ALLOW_PAGE_LOCKS = ON） ON [PRIMARY]

） ON [PRIMARY]

第三步：创建数据同步存储过程

数据同步插入存储过程：

Create procedure [P_ORG_ CONSUME_INSERT]

@ID int，

@CODE_ varchar，

@NAME_ varchar，

@TOTALMONEY_ decimal（18， 10）

as

declare @sum int

begin tran

insert into TV_ORG_ CONSUME（ID，CODE_，NAME_， TOTALMONEY_）

values（@ID，@ CODE_，@ NAME_，@ TOTALMONEY_）

select @sum = count（*） from TV_ ORG_ CONSUME where （ID=@ID）

if（@sum>1）

begin

raiserror（‘记录已经存在'，16，8）

rollback tran——回滚事物

end

else

commit tran——提交事务

创建返回游标的存储过程：

Create procedure [P_ ORG_ CONSUME_GET]

@VChcekCursor Cursor Varying Output

As

Set @VChcekCursor = Cursor

For

SelectID， CODE_，NAME_， TOTALMONEY_

from V_ORG_ CONSUME

Open @VChcekCursor

执行数据同步插入的存储过程：

Create procedure[P_ORG_ CONSUME_INSERT_EXCUTE]

As

Declare @ID int

Declare @ CODE_varchar

Declare @ NAME_varchar

Declare @ TOTALMONEY_decimal（18， 10）

Exec P_ORG_CONSUME_GET @VChcekCursor out

Fetch Next From @VChcekCursor

InTo @ID，@ CODE_，@ NAME_，@ TOTALMONEY_

While（@@Fetch_Status = 0）

Begin

exec[P_ORG_CONSUME_INSERT]

@ID，@ CODE_，@ NAME_，@ TOTALMONEY_

Fetch Next From @VChcekCursor

InTo @ID，@ CODE_，@ NAME_，@ TOTALMONEY_

End

Close @VChcekCursor

Deallocate@VChcekCursor

Go

第四步：创建任务定时执行存储过程

DECLARE @jobId BINARY（16）

EXEC @ReturnCode=

msdb.dbo.sp_add_job @job_name=NJob_Consume_Excute_Procedure，

@enabled=1，

@notify_level_eventlog=2，

@notify_level_email=0，

@notify_level_netsend=0，

@notify_level_page=0，

@delete_level=0，

@description=N无描述。，

@category_name=N[Uncategorized （Local）]，

@owner_login_name=Nadmin， @job_id = @jobId OUTPUT--数据库登录帐号

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep @job_id=@jobId， @step_name=NDELETE_DATA，

@step_id=1，

@cmdexec_success_code=0，

@on_success_action=3，

@on_success_step_id=0，

@on_fail_action=2，

@on_fail_step_id=0，

@retry_attempts=0，

@retry_interval=0，

@os_run_priority=0， @subsystem=NTSQL，

@command=N DELETE FROM TV_ORG_CONSUME ‘，——清除临时表中结果数据

@database_name=NTESTDB， ——数据库名称

@output_file_name=NC：＼TEMP＼T_SQL＼DELETE_ LOG.txt，——删除数据日志

@flags=22

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep@job_id=@jobId， @step_name=NSYNCHRONIZATION_DATA，

@step_id=2，

@cmdexec_success_code=0，

@on_success_action=1，

@on_success_step_id=0，

@on_fail_action=2，

@on_fail_step_id=0，

@retry_attempts=0，

@retry_interval=0，

@os_run_priority=0， @subsystem=NTSQL，

@command=N

DECLARE @RC int

EXECUTE @RC=[P_ ORG_CONSUME_INSERT_EXCUTE] ——执行数据同步存储过程

GO，

@database_name=NTESTDB， ——数据库名称

@output_file_name=NC：＼TEMP＼T_SQL＼SYNCHRONIZATION_lOG.txt，——日志存放目录

@flags=22

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode=msdb.dbo.sp_update_job @job_id=@jobId， @start_step_id=1

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode=msdb.dbo.sp_add_jobschedule @job_id=@jobId， @name=NEXCUTE，

@enabled=1，

@freq_type=4，

@freq_interval=1，

@freq_subday_type=8，

@freq_subday_interval=6，

@freq_relative_interval=0，

@freq_recurrence_factor=0，

@active_start_date=20160320，

@active_end_date=99991231，

@active_start_time=203000，——每天晚上八点半定时任务开始执行

@active_end_time=210000——每天晚上九点定时任务执行结束

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId， @server_name = N（local）

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback：

IF （@@TRANCOUNT > 0） ROLLBACK TRANSACTION

EndSave：

4 结语

采用存储过程定期的将视图中的数据同步至静态表的数据汇总方法适合与数据量较大的业务处理上，例如银行财务扎账、各类成本汇总等方面均有明显的优势，其充分利用了数据库的数据处理能力，减少了应用程序对数据的二次处理，同时大大提高了报表的查询效率。对于小规模的数据统计，不建议采用此方法进行数据统计汇总。

同时采用此方法也存在一些问题，例如：定时任务执行过程中有业务数据产生，业务数据不会被统计到结果中；用户不能看到实时的统计结果等。

参考文献

[1] 明日科技.SQL Server从入门到精通[M].北京：清华大学出版社，2012.

[2] [美]Ben Forta，钟鸣，刘晓霞.SQL必知必会[M].北京：人民邮电出版社，2013.

[3] [美]西尔伯沙茨，杨冬青.数据库系统概念[M].北京：机械工业出版社，2012.

（责任编辑：黄银芳）