sql - T-SQL 中的累积乘法

Question

GTS 表

CCP months   QUART   YEARS  GTS
----  ------  -----    ----- ---
CCP1    1       1   2015    5
CCP1    2       1   2015    6
CCP1    3       1   2015    7
CCP1    4       2   2015    4
CCP1    5       2   2015    2
CCP1    6       2   2015    2
CCP1    7       3   2015    3
CCP1    8       3   2015    2
CCP1    9       3   2015    1
CCP1    10      4   2015    2
CCP1    11      4   2015    3
CCP1    12      4   2015    4

基线表

CCP BASELINE   YEARS    QUART
----  --------   -----  -----
CCP1    5       2015    1

预期结果

CCP months  QUART    YEARS  GTS   result
----  ------  -----    ----- ---   ------

CCP1    1       1   2015    5     25   -- 5 * 5 (here 5 is the baseline)
CCP1    2       1   2015    6     30   -- 6 * 5 (here 5 is the baseline)
CCP1    3       1   2015    7     35   -- 7 * 5 (here 5 is the baseline)
CCP1    4       2   2015    4     360  -- 90 * 4(25+30+35 = 90 is the basline)
CCP1    5       2   2015    2     180  -- 90 * 2(25+30+35 = 90 is the basline)
CCP1    6       2   2015    2     180  -- 90 * 2(25+30+35 = 90 is the basline)
...
next quarter baseline will be (720)

SQLFIDDLE

解释

基线表具有每个 CCP 的单一基线值。

基准值应应用于每个 CCP 的第一季度，对于下一个季度，上一季度的总和值将作为基准值。

是的，我可以使用 While 循环或游标来做到这一点，但我正在寻找更好的方法到目前为止，我已经尝试过类似的方法。但不知道我在这里错过了什么。

查询

;WITH RECURSIVE_CTE
     AS (SELECT B.CCP,
                G.months,
                g.years,
                g.quart,
                Cast(( B.BASELINE + GTS ) AS NUMERIC(38, 15))AS BASE,
                Sum(Cast(( B.BASELINE + GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY b.CCP) AS Q_SUM
         FROM   BASELINE B
                INNER JOIN GTS G
                        ON B.CCP = G.CCP
                           AND b.QUART = g.QUART
                           AND b.YEARS = g.YEARS
         UNION ALL
         SELECT G.CCP,
                G.months,
                g.years,
                g.quart,
                Cast(( Q_SUM + GTS ) AS NUMERIC(38, 15)),
                Sum(Cast(( Q_SUM + GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY g.CCP, g.years, g.quart) AS Q_SUM
         FROM   RECURSIVE_CTE C
                INNER JOIN GTS G
                        ON C.CCP = G.CCP
                           AND G.YEARS = C.YEARS and g.QUART = c.quart + 1)
SELECT *
FROM   RECURSIVE_CTE

注意:我在 Sql Server 2008

中寻找建议

Answer 1

我认为如果您向递归 anchor 添加一个行号，然后将 cte 中的连接限制为每季度仅 3 行中的一个，您可以获得所需的结果:

WITH CTE AS
(   SELECT  b.CCP,
            Baseline = CAST(b.Baseline AS DECIMAL(15,2)),
            b.Years,
            b.Quart,
            g.Months,
            g.GTS,
            Result = CAST(b.Baseline * g.GTS AS DECIMAL(15,2)),
            NextBaseline = SUM(CAST(b.Baseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
            RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
    FROM    #GTS AS g
            INNER JOIN #Base AS b
                ON B.CCP = g.CCP
               AND b.QUART = g.QUART
               AND b.YEARS = g.YEARS
    UNION ALL
    SELECT  b.CCP,
            CAST(b.NextBaseline AS DECIMAL(15, 2)),
            b.Years,
            b.Quart + 1,
            g.Months,
            g.GTS,
            Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,2)),
            NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
            RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
    FROM    #GTS AS g
            INNER JOIN CTE AS b
                ON B.CCP = g.CCP
               AND b.Quart + 1 = g.QUART
               AND b.YEARS = g.YEARS
               AND b.RowNumber = 1
)
SELECT  CCP, Months, Quart, Years, GTS, Result, Baseline
FROM    CTE;

这会产生:

CCP     Months  Quart   Years   GTS     Result      Baseline
-------------------------------------------------------------
CCP1    1       1       2015    5       25.00       5.00
CCP1    2       1       2015    6       30.00       5.00
CCP1    3       1       2015    7       35.00       5.00
CCP1    4       2       2015    4       360.00      90.00
CCP1    5       2       2015    2       180.00      90.00
CCP1    6       2       2015    2       180.00      90.00
CCP1    7       3       2015    3       2160.00     720.00
CCP1    8       3       2015    2       1440.00     720.00
CCP1    9       3       2015    1       720.00      720.00
CCP1    10      4       2015    2       8640.00     4320.00
CCP1    11      4       2015    3       12960.00    4320.00
CCP1    12      4       2015    4       17280.00    4320.00

示例数据

CREATE TABLE #GTS (CCP VARCHAR(4), months INT, QUART INT, YEARS INT, GTS INT);
INSERT INTO #GTS (CCP, months, QUART, YEARS, GTS)
VALUES
    ('CCP1', 1, 1, 2015, 5),
    ('CCP1', 2, 1, 2015, 6),
    ('CCP1', 3, 1, 2015, 7),
    ('CCP1', 4, 2, 2015, 4),
    ('CCP1', 5, 2, 2015, 2),
    ('CCP1', 6, 2, 2015, 2),
    ('CCP1', 7, 3, 2015, 3),
    ('CCP1', 8, 3, 2015, 2),
    ('CCP1', 9, 3, 2015, 1),
    ('CCP1', 10, 4, 2015, 2),
    ('CCP1', 11, 4, 2015, 3),
    ('CCP1', 12, 4, 2015, 4);

CREATE TABLE #Base (CCP VARCHAR(4), BASELINE INT, YEARS INT, QUART INT);
INSERT #Base (CCP, BASELINE, YEARS, QUART) VALUES ('CCP1', 5, 2015, 1);