在T-SQL中运行乘法

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
CCP1     1      1   2016    8
CCP1     2      1   2016    1
CCP1     3      1   2016    3

基线表

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)
CCP1    7       3   2015    3     2160.00  -- 720.00 * 3(360+180+180 = 720)
CCP1    8       3   2015    2     1440.00  --   720.00 * 2(360+180+180 = 720)
CCP1    9       3   2015    1     720.00   --   720.00 * 1(360+180+180 = 720)
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
CCP1     1      1   2016    8     311040.00 --  38880.00
CCP1     2      1   2016    1     77760.00  --  38880.00
CCP1     3      1   2016    3     116640.00 --  38880.00

SQLFIDDLE

Explantion

基线表具有每个CCP的单个基线值.

基线值应适用于每个CCP的第一季度,并且对于下一季度,上一季度的总和值将是basleine.

这是Sql Server 2008中的一个工作查询

;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,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.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,b.Quart + 1,Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15,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;

更新：

工作超过一年

;WITH order_cte
     AS (SELECT Dense_rank() OVER(partition BY ccp ORDER BY years,quart) d_rn,*
         FROM   #gts),CTE
     AS (SELECT b.CCP,Baseline = Cast(b.Baseline AS DECIMAL(15,g.Years,g.Quart,d_rn,Result = Cast(b.Baseline * g.GTS AS DECIMAL(15,NextBaseline = Sum(Cast(b.Baseline * g.GTS AS DECIMAL(15,2)))
                                 OVER(
                                   PARTITION BY g.CCP,RowNumber = Row_number()
                              OVER(
                                PARTITION BY g.CCP,g.quart
                                ORDER BY g.Months)
         FROM   order_cte AS g
                INNER JOIN #Baseline 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,g.d_rn,Result = Cast(b.NextBaseline * g.GTS AS DECIMAL(15,NextBaseline = Sum(Cast(b.NextBaseline * g.GTS AS DECIMAL(15,g.quart
                                ORDER BY g.Months)
         FROM   order_cte AS g
                INNER JOIN CTE AS b
                        ON B.CCP = g.CCP
                           AND b.d_rn + 1 = g.d_rn
                           AND b.RowNumber = 1)
SELECT CCP,Baseline
FROM   CTE;

现在我正在寻找Sql Server 2012中的解决方案,它将利用SUM OVER(ORDER BY)功能或任何更好的方式

试过这样的事情

EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))

但没锻炼

解决方法

WITH sumQuart AS
 (
   SELECT *,CASE
        WHEN ROW_NUMBER() -- for the 1st month in a quarter
             OVER (PARTITION BY CCP,Quart
                   ORDER BY months) = 1
                  -- return the sum of all GTS of this quarter
        THEN SUM(GTS) OVER (PARTITION BY CCP,Quart)
        ELSE NULL -- other months
      END AS sumGTS
   FROM gts
 ),cte AS
 (
   SELECT 
      sq.*,COALESCE(b.Baseline,-- 1st quarter
               -- product of all previous quarters
               CASE
                 WHEN MIN(ABS(sumGTS)) -- any zeros?
                      OVER (PARTITION BY sq.CCP ORDER BY sq.Years,sq.Quart,sq.Months 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) = 0 
                   THEN 0  
                 ELSE -- product
                      EXP(SUM(LOG(NULLIF(ABS(COALESCE(b.Baseline,1) * sumGTS),0)))
                          OVER (PARTITION BY sq.CCP ORDER BY sq.Years,sq.Months 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING)) -- product
                      -- odd number of negative values -> negative result
                    * CASE WHEN COUNT(CASE WHEN sumGTS < 0 THEN 1 END) 
                                OVER (PARTITION BY sq.CCP ORDER BY sq.Years,sq.Months 
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) % 2 = 0 THEN 1 ELSE -1 END
               END) AS newBaseline
   FROM sumQuart AS sq
   LEFT JOIN BASELINE AS b
          ON B.CCP = sq.CCP
          AND b.Quart = sq.Quart
          AND b.Years = sq.Years
 )
SELECT 
   CCP,months,round(newBaseline * GTS,2),round(newBaseline,2)
FROM cte

见Fiddle

编辑：
添加了处理值的逻辑< = 0 Fiddle

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!