根据前一行同一列的值计算值 [英] Calculate value based on value from same column of the previous row

问题描述

我有一个问题，我必须使用公式计算一列，该公式使用上一行中完成的计算的值.

I have an issue where I have to calculate a column using a formula that uses the value from the calculation done in the previous row.

我尝试了滞后功能，但无法通过第二行.之后我的所有值都为空，因为该列最初以空值开头.我觉得我错过了一些东西.

I have tried the lag function but cannot get past the 2nd row. After that all my values are null, since that column originally starts as null. I feel like I am missing something.

我需要计算一个新列，使用公式:

I need to calculate a new column, using the formula:

MovingRate = MonthlyRate + (0.7 * MovingRatePrevious)

... 其中 MovingRatePrevious 是前一行的 MovingRate.对于第 1 个月，我有该值，因此我不需要重新计算该值，但我需要该值才能计算后续行.我需要按类型分区.

... where the MovingRatePrevious is the MovingRate of the prior row. For month 1, I have the value so I do not need to re-calculate that but I need that value to be able to calculate the subsequent rows. I need to partition by Type.

这是我的原始数据集:

Month   Type    MonthyRate  MovingRate
--------------------------------------
1       Blue    0.400       0.330
2       Blue    0.300   
3       Blue    0.700   
4       Blue    0.900   

MovingRate 列中的期望结果:

Month   Type    MonthyRate  MovingRate
---------------------------------------
1       Blue    0.400       0.330
2       Blue    0.300       0.531
3       Blue    0.700       1.072
4       Blue    0.900       1.650

推荐答案

您可以使用递归 CTE 来计算它.以下是您的数据的通用版本:

You can calculate it using recursive CTE. Below is a generalized version for your data:

DECLARE @t TABLE (Month INT, Type VARCHAR(100), MonthlyRate DECIMAL(18, 3));
INSERT INTO @t VALUES
(1, 'Blue', 0.400),
(2, 'Blue', 0.300),
(3, 'Blue', 0.700),
(4, 'Blue', 0.900);

WITH cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Month) AS rn
    FROM @t
), rcte AS (
    SELECT *, CAST(0.330 AS DECIMAL(18, 3)) AS MovingRate
    FROM cte1 AS base
    WHERE rn = 1
    UNION ALL
    SELECT curr.*, CAST(curr.MonthlyRate + 0.7 * prev.MovingRate AS DECIMAL(18, 3))
    FROM cte1 AS curr
    JOIN rcte AS prev ON curr.Type = prev.type AND curr.rn = prev.rn + 1
)
SELECT *
FROM rcte

这篇关于根据前一行同一列的值计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！