使用Transact SQL选择运行总图的数据

Question

我有一个表与以下结构:AuthorId, FollowersNumber, PublishDate，…我需要的是绘制的运行总数的FollowersNumber的周期图。棘手的是每个作者只被计算一次。例如下面的表:

AuthorId, FollowersNumber, PublishDate
1         100              '2013-01-01'
2         200              '2013-01-01'
3         200              '2013-01-02'
2         100              '2013-01-02'
4          60              '2013-01-03'
1          30              '2013-01-03'

结果必须为:

2013-01-01 - 300 (100+200)
2013-01-02 - 500 (300+200)
2013-01-03 - 560 (500+60)

现在我的SQL看起来像(简化):

SELECT 0, SUM (q.FollowersNumber) AS Y FROM 
(SELECT FollowersNumber FROM dbo.Aggregate p WITH (NOLOCK) WHERE p.PublishDate BETWEEN @CurrentPeriod_0_Start AND @CurrentPeriod_0_End AND p.AuthorId not IN 
(SELECT AuthorId FROM dbo.Aggregate WITH (NOLOCK) WHERE PublishDate BETWEEN @PreviousPeriod_0_Start AND @PreviousPeriod_0_End)) AS q 
UNION
SELECT 1, SUM (q.FollowersNumber) AS Y FROM 
(SELECT FollowersNumber FROM dbo.Aggregate p WITH (NOLOCK) WHERE p.PublishDate BETWEEN @CurrentPeriod_1_Start AND @CurrentPeriod_1_End AND p.AuthorId not IN 
(SELECT AuthorId FROM dbo.Aggregate WITH (NOLOCK) WHERE PublishDate BETWEEN @PreviousPeriod_1_Start AND @PreviousPeriod_1_End)) AS q 
etc.

获得该数据后，我计数运行总数的FollowersNumber在c#代码。这个查询非常庞大，运行缓慢。有什么办法能让它快一点吗?

Answer 1

http://sqlfiddle.com/#!3/ff2cf/4

按日期获取总历史记录。我看不懂你的查询参数

Answer 2

这可能对你有帮助-

DECLARE @temp TABLE
(
      AuthorId INT
    , FollowersNumber INT
    , PublishDate DATETIME
)
INSERT INTO @temp (AuthorId, FollowersNumber, PublishDate)
VALUES 
    (1, 100, '20130101'),
    (2, 200, '20130101'),
    (3, 200, '20130102'),
    (2, 100, '20130102'),
    (4,  60, '20130103'),
    (1,  30, '20130103')
DECLARE 
      @CurrentPeriod_0_Start DATETIME
    , @PreviousPeriod_0_Start DATETIME
    , @CurrentPeriod_0_End DATETIME
    , @PreviousPeriod_0_End DATETIME
    , @CurrentPeriod_1_Start DATETIME
    , @PreviousPeriod_1_Start DATETIME
    , @CurrentPeriod_1_End DATETIME
    , @PreviousPeriod_1_End DATETIME

SELECT 0, Y = SUM(p.FollowersNumber) 
FROM @temp p 
WHERE p.PublishDate BETWEEN @CurrentPeriod_0_Start AND @CurrentPeriod_0_End 
    AND NOT EXISTS(
        SELECT 1
        FROM @temp p2
        WHERE p2.PublishDate BETWEEN @PreviousPeriod_0_Start AND @PreviousPeriod_0_End
            AND p2.AuthorId = p.AuthorId
    )
UNION ALL
SELECT 1, Y = SUM(p.FollowersNumber)  
FROM @temp p 
WHERE p.PublishDate BETWEEN @CurrentPeriod_1_Start AND @CurrentPeriod_1_End 
    AND NOT EXISTS(
        SELECT 1
        FROM @temp p2
        WHERE p2.PublishDate BETWEEN @PreviousPeriod_1_Start AND @PreviousPeriod_1_End
            AND p2.AuthorId = p.AuthorId
    )

Answer 3

您可以使用这个查询来代替您的查询。我在SQL Server中使用CTE编写了这个查询。我在你的数据样本上测试了一下，效果很好。

WITH 
RANKINGTABLE AS(
          SELECT [AUTHORID]
              ,[FOLLOWERSNUMBER]
              ,[PUBLISHDATE]
              ,DENSE_RANK() OVER(PARTITION BY AUTHORID ORDER BY PUBLISHDATE) IRANK 
          FROM [TESTQUERY].[DBO].[AGGREGATE]
          ),
DAYSUM AS(
SELECT PUBLISHDATE, SUM([FOLLOWERSNUMBER]) DATESUM 
FROM RANKINGTABLE 
WHERE IRANK = 1
GROUP BY PUBLISHDATE
)
SELECT DS1.PUBLISHDATE, 
        (SELECT SUM(DS2.DATESUM) FROM DAYSUM DS2 WHERE DS2.PUBLISHDATE <= DS1.PUBLISHDATE) PTOTAL  
FROM DAYSUM DS1 

Answer 4

你可以试试这个

SELECT 1, SUM(CASE WHEN PublishDate BETWEEN @PreviousPeriod_0_Start AND @PreviousPeriod_0_End
                   THEN NULL ELSE CASE WHEN PublishDate BETWEEN @CurrentPeriod_0_Start AND @CurrentPeriod_0_End
                                       THEN FollowersNumber
                                  END
                   END
              ) AS Y
FROM dbo.Aggregate WITH(NOLOCK)
UNION ALL
SELECT 0, SUM(CASE WHEN PublishDate BETWEEN @PreviousPeriod_1_Start AND @PreviousPeriod_1_End
                   THEN NULL ELSE CASE WHEN PublishDate BETWEEN @CurrentPeriod_1_Start AND @CurrentPeriod_1_End
                                       THEN FollowersNumber
                                  END
                   END
              ) AS Y
FROM dbo.Aggregate WITH(NOLOCK)
etc.