27,579
社区成员
发帖
与我相关
我的任务
分享
--基础数据表:
IF OBJECT_ID('[WSO_GN_PDP_CONTEXT_CREATE]','P')IS NOT
NULL
DROP TABLE [WSO_GN_PDP_CONTEXT_CREATE]
GO
CREATE TABLE [WSO_GN_PDP_CONTEXT_CREATE](
[SessionID] [bigint] NULL,
[ProbeID] [smallint] NULL,
[State] [smallint] NULL,
[Attempt] [int] NULL,
[IMSI] [bigint] NULL,
[MSISDN] [bigint] NULL,
[IMEI] [bigint] NULL,
[IMEI_TAC] [int] NULL,
[IMEI_SNR] [int] NULL,
[Current_LAC] [int] NULL,
[Current_CELL] [int] NULL,
[timeBegin] [bigint] NULL,
[timeACC] [int] NULL,
[APN] [varchar](32) NULL,
[UserIP] [int] NULL,
[NSAPI] [smallint] NULL,
[RATType] [smallint] NULL,
[ChargingId] [int] NULL,
[ChargeGateWay] [int] NULL,
[SGSN_CONTROL] [int] NULL,
[SGSN_DATA] [int] NULL,
[GGSN_CONTROL] [int] NULL,
[GGSN_DATA] [int] NULL,
[Cause] [smallint] NULL
)
GO
--保存整理数据表结构:
IF OBJECT_ID
('[WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1]','P')IS NOT
NULL
DROP TABLE [WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1]
GO
CREATE TABLE [WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1](
[timeBegin] [bigint] NULL,
[ProbeID] [smallint] NULL,
[RATType] [smallint] NULL,
[SGSN_CONTROL] [int] NULL,
[CURRENT_LAC] [int] NULL,
[CURRENT_CELL] [int] NULL,
[UserCount] [bigint] NULL,
[TOTAL_HITS] [bigint] NULL,
[PDP_ACT_SUCC] [bigint] NULL,
[PDP_ACT_FAIL] [bigint] NULL,
[PDP_ACT_TO] [bigint] NULL,
[PDP_ACT_DELAY_AVG] [int] NULL,
[PDP_ACT_DELAY_MIN] [int] NULL,
[PDP_ACT_DELAY_MAX] [int] NULL
)
declare @date1 datetime
set @date1=GETDATE()
SELECT
(timeBegin/1000/300)*300*1000 AS timeBegin,
ProbeID,
RATType,
SGSN_CONTROL,
CURRENT_LAC,
CURRENT_CELL,
COUNT_BIG(DISTINCT IMSI) AS UserCount,
COUNT_BIG(*) AS TOTAL_HITS,
SUM(cast(case when [State]=1 then 1 else 0 end as
bigint)) AS PDP_ACT_SUCC,
SUM(cast(case when [State]=2 or [State]=3 then 1
else 0 end AS bigint)) AS PDP_ACT_FAIL,
SUM(cast(case when [State]=3 then 1 else 0 end as
bigint)) AS PDP_ACT_TO,
AVG(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_AVG,
MIN(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_MIN,
MAX(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_MAX
--into WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1
FROM
WSO_GN_PDP_CONTEXT_CREATE
GROUP BY
(timeBegin/1000/300)*300*1000,
ProbeID,
RATType,
SGSN_CONTROL,
CURRENT_LAC,
CURRENT_CELL
declare @date2 datetime
set @date2=GETDATE()
print DATEDIFF(MS,@date1,@date2)
--创建索引:
CREATE CLUSTERED INDEX WSO_GN_PDP_CONTEXT_IDX
ON [WSO_GN_PDP_CONTEXT_CREATE]
(timeBegin,ProbeID,RATType,
SGSN_CONTROL,CURRENT_LAC,CURRENT_CELL)
DROP INDEX WSO_GN_PDP_CONTEXT_IDX ON
[WSO_GN_PDP_CONTEXT_CREATE]
--JOB每小时执行一次,对上一个小时的数据整理,把整理的结果
存入TYPE1表。
--每天目标数据的名称为:WSO20120602,就是WSO + 日期。
--举个例子,2点执行的时候把00:00 ~ 01:59:59秒发生的记录,
整理后存入当前数据库的TYPE1表。
--1点执行的那个比较特殊,这个时候执行的是昨天的23:00:00 ~
23:59:59时间范围的数据。
USE master
GO
IF OBJECT_ID('Pro_test')IS NOT NULL
DROP PROC Pro_test
GO
CREATE PROC Pro_test
AS
--定义变量@DBName,实现对数据库名字的动态
DECLARE @DBName VARCHAR(15)
SET @DBName='[WSO'+CONVERT(VARCHAR(8),GETDATE
(),112)+']'
--定义变量@TBName
DECLARE @TBName VARCHAR(50)
--给变量@TBName赋值,并且实现00:00:00的跨库功能
SET @TBName=
CASE WHEN DATEPART(HH,GETDATE())<>0 THEN
'WSO_GN_PDP_CONTEXT_CREATE'
ELSE '[WSO'+CONVERT(VARCHAR(8),DATEADD(DD,-
1,GETDATE()),112)+']..'+'WSO_GN_PDP_CONTEXT_CREATE' END
--PRINT @TBName
--定义变量,用来存储拼接的SQL语句:
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
USE '+@DBName
+'
GO
IF OBJECT_ID(''WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1'')
IS NULL
GO
CREATE TABLE WSO_GN_PDP_CONTEXT_CREATE_STATS_TYPE1(
[timeBegin] [bigint] NULL,
[ProbeID] [smallint] NULL,
[RATType] [smallint] NULL,
[SGSN_CONTROL] [int] NULL,
[CURRENT_LAC] [int] NULL,
[CURRENT_CELL] [int] NULL,
[UserCount] [bigint] NULL,
[TOTAL_HITS] [bigint] NULL,
[PDP_ACT_SUCC] [bigint] NULL,
[PDP_ACT_FAIL] [bigint] NULL,
[PDP_ACT_TO] [bigint] NULL,
[PDP_ACT_DELAY_AVG] [int] NULL,
[PDP_ACT_DELAY_MIN] [int] NULL,
[PDP_ACT_DELAY_MAX] [int] NULL
)
GO
SELECT
(timeBegin/1000/300)*300*1000 AS timeBegin,
ProbeID,
RATType,
SGSN_CONTROL,
CURRENT_LAC,
CURRENT_CELL,
COUNT_BIG(DISTINCT IMSI) AS UserCount,
COUNT_BIG(*) AS TOTAL_HITS,
SUM(cast(case when [State]=1 then 1 else 0 end as
bigint)) AS PDP_ACT_SUCC,
SUM(cast(case when [State]=2 or [State]=3 then 1
else 0 end AS bigint)) AS PDP_ACT_FAIL,
SUM(cast(case when [State]=3 then 1 else 0 end as
bigint)) AS PDP_ACT_TO,
AVG(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_AVG,
MIN(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_MIN,
MAX(case when [State]=1 then timeACC else NULL end)
AS PDP_ACT_DELAY_MAX
FROM
'
+
@TBName
+
'
GROUP BY
(timeBegin/1000/300)*300*1000,
ProbeID,
RATType,
SGSN_CONTROL,
CURRENT_LAC,
CURRENT_CELL
GO'
PRINT @SQL