在T-SQL中运行乘法

小编典典

在T-SQL中运行乘法

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

外植

基准表对于每个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,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;

更新：

工作一年以上

;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, 2)),
                g.Years,
                g.Quart,
                g.Months,
                g.GTS,
                d_rn,
                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   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, 2)),
                g.Years,
                g.Quart,
                g.Months,
                g.GTS,
                g.d_rn,
                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   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,
       Months,
       Quart,
       Years,
       GTS,
       Result,
       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))

但是没有锻炼

阅读 267

2021-04-15

共1个答案

小编典典

以下解决方案假定每个季度始终有3行（只有最后一个季度可能是部分行），单个SELECT，无递归:-)

WITH sumQuart AS
 (
   SELECT *,
      CASE
        WHEN ROW_NUMBER() -- for the 1st month in a quarter
             OVER (PARTITION BY CCP, Years, Quart
                   ORDER BY months) = 1
                  -- return the sum of all GTS of this quarter
        THEN SUM(GTS) OVER (PARTITION BY CCP, Years, 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.Quart, 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.Quart, 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, Quart, Years, GTS,
   round(newBaseline * GTS,2),
   round(newBaseline,2)
FROM cte

2021-04-15