存储过程-统计10年热点关键词

  1 USE [NPU_Group]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[Proc_HotAndFutrueKeywords]    Script Date: 02/05/2013 10:02:26 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 -- ================================================
  9 -- Proc Function : 研究热点和趋势统计存储过程
 10 -- Create Date   : 2013-02-01
 11 -- Update Date   : 2012-02-01
 12 -- Create User   : IsaacZhang
 13 -- Update User   : IsaacZhang
 14 -- Description   : 研究热点和趋势统计数据
 15 -- Parameter     :
 16 -- ================================================
 17 ALTER PROCEDURE [dbo].[Proc_HotAndFutrueKeywords]
 18     @year INT ,--年度
 19     @groupIntId INT,  --群组ID
 20     @TopNum INT --取多少条热点数据
 21 AS
 22 
 23 
 24 --创建结果临时表
 25 CREATE TABLE #tbResult(
 26         KeywordIdInt INT , 
 27         KeywordId UNIQUEIDENTIFIER ,
 28         KeyWordName VARCHAR(200) ,
 29         ForeignName VARCHAR(200) ,
 30         IsTopic BIT ,
 31         HotYear INT,
 32         TotalCount decimal(18, 2),
 33         GroupIntId INT,
 34         AmplificationNum decimal(18, 2),
 35         Amplification decimal(18, 2),
 36         AmpliFactor    decimal(18, 2)
 37 )
 38 --定义要输出的所有年份
 39 DECLARE @YearCount INT
 40 SET @YearCount = 10;
 41 --声明变量
 42 DECLARE @KeywordIdInt INT
 43 DECLARE @KeywordId UNIQUEIDENTIFIER
 44 DECLARE @KeyWordName VARCHAR(200),@ForeignName VARCHAR(200)
 45 DECLARE @IsTopic BIT
 46 DECLARE @TotalCount decimal(18, 2)
 47 DECLARE @Amplification decimal(18, 2),@lastYearCount decimal(18, 2),@AmplificationNum decimal(18, 2)
 48 ,@AmpliFactor    decimal(18, 2)
 49 
 50 DECLARE @CurrentYear INT
 51 --如果群组ID不为0，则取全部关键词文献信息
 52 IF @groupIntId!=0
 53 BEGIN
 54 
 55 BEGIN TRANSACTION Trans_HotAndFutrueKeywords --创建事务
 56 --删除表中原数据
 57 DELETE FROM dbo.Pt_FutureAmplification;
 58 
 59     --初始化当前年
 60     SET @CurrentYear = @year;
 61     WHILE(@CurrentYear>=@year-@YearCount)
 62     BEGIN
 63         --声明游标
 64         DECLARE hotKeywordsIndex CURSOR 
 65         FOR SELECT * FROM NPU_Core.dbo.Keyword
 66         OPEN hotKeywordsIndex
 67         FETCH NEXT FROM hotKeywordsIndex INTO @KeywordIdInt,@KeywordId,@KeyWordName
 68                 ,@ForeignName,@IsTopic
 69         WHILE @@FETCH_STATUS = 0
 70         BEGIN
 71             --每次重新赋值
 72             SET @TotalCount = 0;
 73             SET @lastYearCount = 0;
 74             PRINT '=============='+CAST(@CurrentYear AS VARCHAR(20))+'==============='
 75             SELECT @TotalCount = COUNT(0) FROM View_ShareBib2Keywords WHERE KeywordIdInt =@KeywordIdInt
 76             AND [Year] = @CurrentYear
 77             
 78             SELECT @lastYearCount = COUNT(0) FROM View_ShareBib2Keywords WHERE KeywordIdInt =@KeywordIdInt
 79             AND [Year] = (@CurrentYear-1)
 80             --PRINT CAST(@TotalCount AS VARCHAR(20))+'<-current-------last->'+CAST(@lastYearCount AS VARCHAR(20))
 81             --增长数量
 82             SET @AmplificationNum = @TotalCount-@lastYearCount;
 83             --增长幅度
 84             IF @lastYearCount!=0 AND @lastYearCount!=1
 85             BEGIN
 86                 SET @Amplification = (@TotalCount-@lastYearCount)/@lastYearCount
 87                 --增长因素
 88                 SET @AmpliFactor = @AmplificationNum*@Amplification;
 89                 --如果增幅为负增长
 90                 IF(@TotalCount<@lastYearCount)
 91                 BEGIN
 92                     SET @Amplification = -round((@lastYearCount-@TotalCount)/@lastYearCount,3);
 93                     SET @AmpliFactor = -@AmpliFactor;
 94                     --PRINT '|||'+CAST(@Amplification AS VARCHAR(20))
 95                 END
 96             END
 97             ELSE
 98             BEGIN
 99                 SET @Amplification = (@TotalCount-@lastYearCount)/1.00;
100                 SET @AmpliFactor = 0;
101             END
102             IF @TotalCount IS NULL
103                 SET @TotalCount = 0;
104         
105             INSERT INTO Pt_FutureAmplification VALUES(
106                 @KeywordIdInt,
107                 @KeywordId,
108                 @KeyWordName,
109                 @ForeignName,
110                 @IsTopic,
111                 @CurrentYear,
112                 @TotalCount,
113                 @groupIntId,
114                 @AmplificationNum,
115                 @Amplification,
116                 @AmpliFactor
117             );
118         --执行下一次游标操作
119         FETCH NEXT FROM  hotKeywordsIndex INTO @KeywordIdInt,@KeywordId,@KeyWordName
120             ,@ForeignName,@IsTopic
121         END
122         --关闭游标
123         CLOSE hotKeywordsIndex
124         --释放资源
125         DEALLOCATE hotKeywordsIndex
126         SET @CurrentYear = @CurrentYear-1;
127     END
128     
129         SELECT top ((@TopNum)) * FROM #tbResult
130         WHERE HotYear = ((@year-2))
131          ORDER BY TotalCount DESC,HotYear DESC
132     IF @@ERROR=0
133         COMMIT TRANSACTION Pt_FutureAmplification -- 提交事务处理
134     ELSE
135         ROLLBACK TRANSACTION Pt_FutureAmplification   --事务回滚
136 END