复杂滚动场景(交叉应用和外部应用示例)

Question

我目前有如下数据(但更大！)

/*--:::::::::::
DROP TABLE #target
DROP TABLE #Fact
*/--:::::::::::
CREATE TABLE #target 
    (
    PlayerKey INT,
    Name            VARCHAR(8),
    LiveKey     INT
    );
INSERT INTO #target 
    values
    (1,'michael',20130103),
    (2,'jackson',20130107);

CREATE TABLE #Fact 
    (
    DateKey     INT,
    PlayerKey INT,
    Amount      INT
    );
INSERT INTO #Fact 
    values
    (20130101,1,10),
    (20130102,1,90),
    (20130103,1,18),
    (20130103,2,79),
    (20130103,3,99),
    (20130104,2,15),
    (20130105,1,12),
    (20130105,2,15),
    (20130106,1,60),
    (20130107,1,96),
    (20130107,2,88),
    (20130107,4,28),
    (20130108,1,13),
    (20130108,2,15),
    (20130109,1,33),
    (20130109,2,67),
    (20130110,1,19),
    (20130110,2,17)
    ;

查询的开始如下所示。

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
        SELECT  rn = ROW_NUMBER() OVER(PARTITION BY d.Name ORDER BY f.DateKey),
                f.DateKey,
                d.Name,
                f.Amount
        FROM    #Fact f
                INNER JOIN #target d ON
                  f.PlayerKey = d.PlayerKey AND
                  f.DateKey >= d.LiveKey AND
                  f.DateKey < CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),d.LiveKey,112))+@NumDays),112)
        )
SELECT  x.*,
        "RollingAmount" = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte x;

这说明了以下几点：

​

​

假设我们有一个DimDate DimDate生产视图可用，那么如何确保michael有一个金额为0的20130104行？

另外，在同一脚本中是否可以添加新列"AmountAll“和"AmountAllRolling”，这将给出包括PlayerKeys 3和4在内的所有播放器的编号？我猜这将涉及将INNER JOIN更改为LEFT OUTER JOIN

因此，鉴于上述情况，最终结果如下：

​

​

编辑

通过博格丹的出色帮助，我得到了以下信息。

我添加了一个额外的总AmountGroup，即指定玩家之间的总数--这只是“很好地拥有”，而不是原始规范的一部分。

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
        SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                x.DateKey,
                d.Name,
                Amount      = ISNULL(f.Amount,0),
                AmountGroup = ISNULL(f.AmountGroup,0),
                AmountAll   = ISNULL(f.AmountAll,0)
        FROM    (
                SELECT  t.*, 
                EndLiveKey = CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112))
                FROM    #target t
                ) d 
                CROSS APPLY
                    (
                SELECT  dm.DateKey
                FROM    WHData.dbo.vw_DimDate dm
                WHERE   dm.DateKey >= d.LiveKey AND
                        dm.DateKey < d.EndLiveKey           
                ) x
                OUTER APPLY
                (
                SELECT  Amount  = SUM(CASE WHEN PlayerKey1 = PlayerKey2 THEN fbase.Amount END),
                        AmountGroup = SUM(CASE WHEN inGroup = 1 THEN fbase.Amount ELSE 0 END),
                        AmountAll   = SUM(fbase.Amount)
                FROM
                    (
                    SELECT  fct.Amount, 
                            fct.PlayerKey AS PlayerKey1, 
                        d.PlayerKey AS PlayerKey2,
                        CASE WHEN tt.PlayerKey IS NULL THEN 0 ELSE 1 END AS inGroup
                    FROM    #Fact fct 
                        LEFT OUTER JOIN #target tt ON
                        fct.PlayerKey = tt.PlayerKey 
                    WHERE   fct.DateKey = x.DateKey
                ) fbase
            ) f
        )
SELECT   y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountGroup"  = SUM(AmountGroup) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

Answer 1

我假设您有一个具有以下结构的DimDate表：

CREATE TABLE DimDate
(
DateKey INT PRIMARY KEY
);

而DateKey列没有空白。

解决方案：

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  x.DateKey,
                    d.Name,
                    Amount = ISNULL(f.Amount,0)
            FROM    
            (
                SELECT  t.*, CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112)) AS EndLiveKey
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            LEFT OUTER JOIN #Fact f 
            ON f.PlayerKey = d.PlayerKey 
            AND f.DateKey = x.DateKey
        )
SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DateKey),
        y.*,
        "RollingAmount" = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

编辑#1:

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                    x.DateKey,
                    d.Name,
                    Amount      = ISNULL(f.Amount,0),
                    AmountAll   = ISNULL(fall.AmountAll,0)
            FROM    
            (
                SELECT  t.*, CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112)) AS EndLiveKey
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            OUTER APPLY
            (
                SELECT  SUM(fct.Amount) AS Amount
                FROM    #Fact fct 
                WHERE   fct.DateKey = x.DateKey
                AND     fct.PlayerKey = d.PlayerKey
            ) f
            OUTER APPLY
            (
                SELECT  SUM(fct.Amount) AS AmountAll 
                FROM    #Fact fct 
                WHERE   fct.DateKey = x.DateKey
            ) fall
        )
SELECT  
        y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;

编辑#2:

DECLARE @NumDays INT = 3;

WITH    basic_cte AS
        (
            SELECT  rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY x.DateKey),
                    x.DateKey,
                    d.Name,
                    Amount      = ISNULL(f.Amount,0),
                    AmountAll   = ISNULL(f.AmountAll,0)
            FROM    
            (
                SELECT  t.*, EndLiveKey = CONVERT(INT,CONVERT(CHAR(8),CONVERT(DATETIME,CONVERT(DATETIME,CONVERT(CHAR(8),t.LiveKey,112))+@NumDays),112))
                FROM    #target t
            ) d 
            CROSS APPLY
            (
                SELECT  dm.DateKey
                FROM    DimDate dm
                WHERE   dm.DateKey >= d.LiveKey 
                AND     dm.DateKey < d.EndLiveKey           
            ) x
            OUTER APPLY
            (
                SELECT  AmountAll   = SUM(fbase.Amount),
                        Amount      = SUM(CASE WHEN PlayerKey1 = PlayerKey2 THEN fbase.Amount END)
                FROM
                (
                    SELECT  fct.Amount, fct.PlayerKey AS PlayerKey1, d.PlayerKey AS PlayerKey2
                    FROM    #Fact fct 
                    WHERE   fct.DateKey = x.DateKey
                ) fbase
            ) f
        )
SELECT  
        y.*,
        "RollingAmount"     = SUM(Amount) OVER(PARTITION BY Name ORDER BY DateKey),
        "RollingAmountAll"  = SUM(AmountAll) OVER(PARTITION BY Name ORDER BY DateKey)
FROM    basic_cte y;