可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
GTS Table
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
Baseline table
CCP BASELINE YEARS QUART
---- -------- ----- -----
CCP1 5 2015 1
Expected result
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
Baseline table has single baseline value for each CCP.
The baseline value should be applied to first quarter of each CCP and for the next quarters previous quarter sum value will be the basleine.
Here is a working query in 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;
UPDATE :
To work with more than one year
;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;
Now am looking for a solution in Sql Server 2012+ which will utilize SUM OVER(ORDER BY) functionality or any better way
Tried something like this
EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))
But didnt workout
回答1:
Following solution assumes there are always 3 rows per quarter (only the last quarter might be partial), single SELECT, no recursion :-)
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
See Fiddle
EDIT:
Added logic to handle values <= 0 Fiddle
回答2:
Another method that uses the EXP(SUM(LOG())) trick and only window functions for the running total (no recursive CTEs or cursors).
Tested at dbfiddle.uk:
WITH
ct AS
( SELECT
ccp, years, quart,
q2 = round(exp(coalesce(sum(log(sum(gts)))
OVER (PARTITION BY ccp
ORDER BY years, quart
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING)
, 0))
, 2) -- round appropriately to your requirements
FROM gts
GROUP BY ccp, years, quart
)
SELECT
g.*,
result = g.gts * b.baseline * ct.q2,
baseline = b.baseline * ct.q2
FROM ct
JOIN gts AS g
ON ct.ccp = g.ccp
AND ct.years = g.years
AND ct.quart = g.quart
CROSS APPLY
( SELECT TOP (1) b.baseline
FROM baseline AS b
WHERE b.ccp = ct.ccp
ORDER BY b.years, b.quart
) AS b
;
How it works:
(CREATE tables and INSERT skipped)
1, lets group by ccp, year and quart and calculate the sums:
select
ccp, years, quart,
q1 = sum(gts)
from gts
group by ccp, years, quart ;
GO
ccp | years | quart | q1
:--- | ----: | ----: | :--------
CCP1 | 2015 | 1 | 18.000000
CCP1 | 2015 | 2 | 8.000000
CCP1 | 2015 | 3 | 6.000000
CCP1 | 2015 | 4 | 9.000000
CCP1 | 2016 | 1 | 12.000000
- 2, we use the
EXP(LOG(SUM()) trick to calculate the running multiplications of these sums. We use BETWEEEN .. AND -1 PRECEDING in the window to skip the current values, as these values are only used for the baselines of the next quart.
The rounding is to avoid inaccuracies that come from using LOG() and EXP(). You can experiment with using either ROUND() or casting to NUMERIC:
with
ct as
( select
ccp, years, quart,
q1 = sum(gts)
from gts
group by ccp, years, quart
)
select
ccp, years, quart, -- months, gts, q1,
q2 = round(exp(coalesce(sum(log(q1))
OVER (PARTITION BY ccp
ORDER BY Years, Quart
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING),0)),2)
from ct ;
GO
ccp | years | quart | q2
:--- | ----: | ----: | ---:
CCP1 | 2015 | 1 | 1
CCP1 | 2015 | 2 | 18
CCP1 | 2015 | 3 | 144
CCP1 | 2015 | 4 | 864
CCP1 | 2016 | 1 | 7776
- 3, we combine the two queries in one (no need for that, it just makes the query more compact, you could have 2 CTEs instead) and then join to
gts so we can multiply each value with the calculated q2 (which gives us the baseline).
The CROSS APPLY is merely to get the base baseline for each ccp.
Note that I change this one slightly, to numeric(22,6) instead of rounding to 2 decimal places. The results are the same with the sample but they may differ if the numbers are bigger or not integer:
with
ct as
( select
ccp, years, quart,
q2 = cast(exp(coalesce(sum(log(sum(gts)))
OVER (PARTITION BY ccp
ORDER BY years, quart
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING)
, 0.0))
as numeric(22,6)) -- round appropriately to your requirements
from gts
group by ccp, years, quart
)
select
g.*,
result = g.gts * b.baseline * ct.q2,
baseline = b.baseline * ct.q2
from ct
join gts as g
on ct.ccp = g.ccp
and ct.years = g.years
and ct.quart = g.quart
cross apply
( select top (1) baseline
from baseline as b
where b.ccp = ct.ccp
order by years, quart
) as b
;
GO
CCP | months | QUART | YEARS | GTS | result | baseline
:--- | -----: | ----: | ----: | :------- | :------------ | :-----------
CCP1 | 1 | 1 | 2015 | 5.000000 | 25.000000 | 5.000000
CCP1 | 2 | 1 | 2015 | 6.000000 | 30.000000 | 5.000000
CCP1 | 3 | 1 | 2015 | 7.000000 | 35.000000 | 5.000000
CCP1 | 4 | 2 | 2015 | 4.000000 | 360.000000 | 90.000000
CCP1 | 5 | 2 | 2015 | 2.000000 | 180.000000 | 90.000000
CCP1 | 6 | 2 | 2015 | 2.000000 | 180.000000 | 90.000000
CCP1 | 7 | 3 | 2015 | 3.000000 | 2160.000000 | 720.000000
CCP1 | 8 | 3 | 2015 | 2.000000 | 1440.000000 | 720.000000
CCP1 | 9 | 3 | 2015 | 1.000000 | 720.000000 | 720.000000
CCP1 | 10 | 4 | 2015 | 2.000000 | 8640.000000 | 4320.000000
CCP1 | 11 | 4 | 2015 | 3.000000 | 12960.000000 | 4320.000000
CCP1 | 12 | 4 | 2015 | 4.000000 | 17280.000000 | 4320.000000
CCP1 | 1 | 1 | 2016 | 8.000000 | 311040.000000 | 38880.000000
CCP1 | 2 | 1 | 2016 | 1.000000 | 38880.000000 | 38880.000000
CCP1 | 3 | 1 | 2016 | 3.000000 | 116640.000000 | 38880.000000
回答3:
Im not sure if the question is how do the over() logic or perform a recursive cte in 2012 or just calculate in 2012 without recursive cte.
Looks like you were trying to create the PRODUCT() agreggation equivalent. But as I put in my comment
The function 'Exp' is not a valid windowing function, and cannot be used with the OVER clause.
So I did my version without recursive cte for my answer. I include one aditional year on the sample data with different baseline
- Calculate the total
gts for each Quart
- Get the baseline from first
Quart
- Calculate the base line for the other
Quart doing the dummy PRODUCT() aggregated
SQL Fiddle Demo
WITH gtsTotal as (
SELECT [CCP], [Year], [QUART], SUM([GTS]) as sumGts
FROM gts
GROUP BY [CCP], [Year], [QUART]
),
newBase as (
SELECT g.[CCP], g.[YEAR], b.[BASELINE], 1 as sQuart, b.[BASELINE] as [TotalBase]
FROM gtsTotal g
INNER JOIN baseline b
on g.[Year] = b.[YEARS]
and g.[CCP] = b.[CCP]
WHERE g.[QUART] <= 1
UNION ALL
SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase]
FROM gtsTotal g
INNER JOIN baseline b
on g.[Year] = b.[YEARS]
and g.[CCP] = b.[CCP]
WHERE g.[QUART] <= 1
GROUP BY g.[CCP], g.[YEAR], b.[BASELINE]
UNION ALL
SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase]
FROM gtsTotal g
INNER JOIN baseline b
on g.[Year] = b.[YEARS]
and g.[CCP] = b.[CCP]
WHERE g.[QUART] <= 2
GROUP BY g.[CCP], g.[YEAR], b.[BASELINE]
UNION ALL
SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase]
FROM gtsTotal g
INNER JOIN baseline b
on g.[Year] = b.[YEARS]
and g.[CCP] = b.[CCP]
WHERE g.[QUART] <= 3
GROUP BY g.[CCP], g.[YEAR], b.[BASELINE]
)
SELECT g.CCP, g.months, g.QUART, g.Year, CEILING(g.GTS * n.TotalBase)
FROM newBase n
INNER JOIN gts g
ON n.CCP = g.CCP
AND n.[Year] = g.[Year]
AND n.[sQuart] = g.[QUART]
order by g.[Year], n.sQuart
Output
| CCP | months | QUART | Year | Result|
|------|--------|-------|------|-------|
| CCP1 | 1 | 1 | 2015 | 25 |
| CCP1 | 2 | 1 | 2015 | 30 |
| CCP1 | 3 | 1 | 2015 | 35 |
| CCP1 | 4 | 2 | 2015 | 360 |
| CCP1 | 5 | 2 | 2015 | 180 |
| CCP1 | 6 | 2 | 2015 | 180 |
| CCP1 | 7 | 3 | 2015 | 2160 |
| CCP1 | 8 | 3 | 2015 | 1440 |
| CCP1 | 9 | 3 | 2015 | 720 |
| CCP1 | 10 | 4 | 2015 | 8640 |
| CCP1 | 11 | 4 | 2015 | 12960 |
| CCP1 | 12 | 4 | 2015 | 17280 |
回答4:
Another alternative is using stacked/cascaded CTEs:
;WITH CteFirstQtr AS(
SELECT g.*,
result = b.BASELINE * g.GTS
FROM gts g
INNER JOIN BASELINE b
ON b.CCP = g.CCP
AND b.YEARS = g.YEARS
AND b.QUART = g.QUART
),
CteSecondQtr AS(
SELECT g.*,
result = t.result * g.gts
FROM gts g
CROSS APPLY(
SELECT SUM(result)
FROM CteFirstQtr
WHERE
CCP = g.CCP
AND Years = g.Years
AND QUART = 1
)t(result)
WHERE g.QUART = 2
),
CteThirdQtr AS(
SELECT g.*,
result = t.result * g.gts
FROM gts g
CROSS APPLY(
SELECT SUM(result)
FROM CteSecondQtr
WHERE
CCP = g.CCP
AND Years = g.Years
AND QUART = 2
)t(result)
WHERE g.QUART = 3
),
CteFourthQtr AS(
SELECT g.*,
result = t.result * g.gts
FROM gts g
CROSS APPLY(
SELECT SUM(result)
FROM CteThirdQtr
WHERE
CCP = g.CCP
AND Years = g.Years
AND QUART = 3
)t(result)
WHERE g.QUART = 4
)
SELECT * FROM CteFirstQtr UNION ALL
SELECT * FROM CteSecondQtr UNION ALL
SELECT * FROM CteThirdQtr UNION ALL
SELECT * FROM CteFourthQtr
回答5:
No recursion, no loops.
DECLARE @BASELINE TABLE(CCP char(4), BASELINE numeric(22,6), YEARS int ,QUART int)
DECLARE @gts TABLE (CCP char(4),months int,QUART int,YEARS int,GTS numeric(22,6))
insert into @baseline
SELECT 'CCP1' AS CCP,5 AS BASELINE, 2015 AS YEARS,1 AS QUART
insert into @gts (CCP,months,QUART,YEARS,GTS)
SELECT 'CCP1' AS CCP,1 AS months,1 AS QUART ,2015 AS YEARS, 5 AS GTS UNION
SELECT 'CCP1' AS CCP,2 AS months,1 AS QUART ,2015 AS YEARS, 6 AS GTS UNION
SELECT 'CCP1' AS CCP,3 AS months,1 AS QUART ,2015 AS YEARS, 7 AS GTS UNION
SELECT 'CCP1' AS CCP,4 AS months,2 AS QUART ,2015 AS YEARS, 4 AS GTS UNION
SELECT 'CCP1' AS CCP,5 AS months,2 AS QUART ,2015 AS YEARS, 2 AS GTS UNION
SELECT 'CCP1' AS CCP,6 AS months,2 AS QUART ,2015 AS YEARS, 2 AS GTS UNION
SELECT 'CCP1' AS CCP,7 AS months,3 AS QUART ,2015 AS YEARS, 3 AS GTS UNION
SELECT 'CCP1' AS CCP,8 AS months,3 AS QUART ,2015 AS YEARS, 2 AS GTS UNION
SELECT 'CCP1' AS CCP,9 AS months,3 AS QUART ,2015 AS YEARS, 1 AS GTS UNION
SELECT 'CCP1' AS CCP,10 AS months,4 AS QUART ,2015 AS YEARS, 2 AS GTS UNION
SELECT 'CCP1' AS CCP,11 AS months,4 AS QUART ,2015 AS YEARS, 3 AS GTS UNION
SELECT 'CCP1' AS CCP,12 AS months,4 AS QUART ,2015 AS YEARS, 4 AS GTS
DECLARE @Summary TABLE (CCP char(4),[DATE] DATE,SumGTS numeric(22,6),AllPriorGTS numeric(22,6),[Multiplier] numeric(22,6))
INSERT INTO @Summary(CCP,[DATE],SumGTS)
SELECT
CCP,DATEADD(MONTH,(QUART-1)*3,DATEADD(YEAR,YEARS-2000,'01/01/2000')) [DATE]
, SUM(GTS) [SumGTS]
FROM @gts
GROUP BY CCP,DATEADD(MONTH,(QUART-1)*3,DATEADD(YEAR,YEARS-2000,'01/01/2000'))
UPDATE s
SET AllPriorGTS=
(
SELECT EXP(SUM(LOG(s1.SumGTS)))
FROM @Summary s1
WHERE s1.[Date]<s.[DATE]
)
from @Summary s
UPDATE s
SET [Multiplier]=SumGTS*ISNULL(AllPriorGTS,1) * bl.BASELINE
from @Summary s
INNER JOIN @baseline bl ON bl.CCP = s.CCP
SELECT
g.*,g.GTS*ISNULL(s.[Multiplier],bl.BASELINE) [RESULTS]
FROM @gts g
LEFT JOIN @Summary s ON DATEADD(MONTH,3,s.DATE)=DATEADD(MONTH,(g.QUART-1)*3,DATEADD(YEAR,g.YEARS-2000,'01/01/2000'))
LEFT JOIN @baseline bl ON bl.CCP = g.CCP
回答6:
Without a CTE. I'd suggest possible working with the months and years in an actual date format. I might become a bit easier to manage the transition of the Years 2015 vs 2016 becomes a simple DATEADD(MM,-1,dateCol) as opposed to figuring out when to subtract or add a year. I haven't implemented in this solution, but could help if you needed it. It could lead to a pretty effective indexing strategy to speed the query up if you have a large data set. I believer it would also be possible to implement this with LAG as well.
create table #tmp_BASELINE (
CCP char(4),
BASELINE numeric(22,6),
YEARS int ,
QUART int)
create table #tmp_gts (
CCP char(4),
months int,
QUART int,
YEARS int,
GTS numeric(22,6)
)
insert into #tmp_BASELINE
SELECT 'CCP1' AS CCP,10 AS BASELINE, 2015 AS YEARS,1 AS QUART
insert into #tmp_gts
SELECT 'CCP1' AS CCP,1 AS months,1 AS QUART ,2015 AS YEARS, 50 AS GTS UNION
SELECT 'CCP1' AS CCP,2 AS months,1 AS QUART ,2015 AS YEARS, 52 AS GTS UNION
SELECT 'CCP1' AS CCP,3 AS months,1 AS QUART ,2015 AS YEARS, 57 AS GTS UNION
SELECT 'CCP1' AS CCP,4 AS months,2 AS QUART ,2015 AS YEARS, 59 AS GTS UNION
SELECT 'CCP1' AS CCP,5 AS months,2 AS QUART ,2015 AS YEARS, 61 AS GTS UNION
SELECT 'CCP1' AS CCP,6 AS months,2 AS QUART ,2015 AS YEARS, 65 AS GTS UNION
SELECT 'CCP1' AS CCP,7 AS months,3 AS QUART ,2015 AS YEARS, 69 AS GTS UNION
SELECT 'CCP1' AS CCP,8 AS months,3 AS QUART ,2015 AS YEARS, 73 AS GTS UNION
SELECT 'CCP1' AS CCP,9 AS months,3 AS QUART ,2015 AS YEARS, 78 AS GTS UNION
SELECT 'CCP1' AS CCP,10 AS months,4 AS QUART ,2015 AS YEARS, 84 AS GTS UNION
SELECT 'CCP1' AS CCP,11 AS months,4 AS QUART ,2015 AS YEARS, 90 AS GTS UNION
SELECT 'CCP1' AS CCP,12 AS months,4 AS QUART ,2015 AS YEARS, 95 AS GTS
SELECT * FROM #tmp_BASELINE
SELECT CCP,
Months,
QUART,
YEARS,
GTS,
SUM(GTS) OVER (PARTITION BY QUART) as QTRGTS,
COALESCE((SELECT DISTINCT SUM(PGT.GTS) OVER (PARTITION BY QUART) FROM #tmp_gts as PGT WHERE GTS.YEARS = PGT.YEARS AND PGT.QUART = GTS.QUART-1),(SELECT TOP 1 BaseLine FROM #tmp_BASELINE)) as Modifier,
GTS * COALESCE((SELECT DISTINCT SUM(PGT.GTS) OVER (PARTITION BY QUART) FROM #tmp_gts as PGT WHERE GTS.YEARS = PGT.YEARS AND PGT.QUART = GTS.QUART-1),(SELECT TOP 1 BaseLine FROM #tmp_BASELINE)) as GTSxModifier
FROM #tmp_gts as GTS
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 别忘想泡老子 的文章《Running Multiplication in T-SQL》','https://www.manongdao.com/article-552488.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}