--基础数据表：
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 + 日期。 
--举个例子