平衡趋势分析

Question

我希望每月按产品计算余额的运行百分比变化。month字段代表每个月的最后一个工作日。

我尝试使用了一个通用表表达式，如下所示：

declare @tab table 
( DATE DATETIME, 
BALANCE DECIMAL (38,8), 
PDCT_TYPE_C VARCHAR (5), 
EXCL_C VARCHAR (1) 
)

INSERT INTO @tab 
SELECT 
DATE, 
SUM(BALANCE),
PDCT_TYPE_C,
RC_EXCL_C 
from ERIC..RC_XPOS 
WHERE RC_EXCL_C IS NULL AND PDCT_TYPE_C = 'hln' 
GROUP BY XPOS_D,PDCT_TYPE_C,RC_EXCL_C 
; 
with cte as 
( 
select 
row_number() over (order by PDCT_TYPE_C,DATE) rn, 
DATE, 
BALANCE, 
PDCT_TYPE_C 
from @tab 
) 
select 
DATE, 
BALANCE, 
(((select BALANCE from cte where cte.rn=1))-(select BALANCE from cte where cte.rn=t1.rn+1)) as 'EAD MOM Difference', 
(((select BALANCE from cte where cte.rn=1))/(select BALANCE from cte where cte.rn=t1.rn+1)) as 'EAD MOM % Difference', 
PDCT_TYPE_C 
from cte as t1

如果只有一个产品，这是很好的，但问题是，如果有许多产品，它只计算前一行的百分比变化-当有多个产品类型时，可能是不同的产品。

有人知道怎么解决这个问题吗？

我希望得到下面这样的结果。我有余额，日期和产品类型字段可用，但我希望计算余额的差异和余额的变化，而不必做一个行分区和计算基于上面的行。

如果你有任何问题请告诉我。

期望的结果：

Date        | Balance      | Balance Difference | Balance % Change |  Product Type
---------------------------------------------------------------
30/06/2017  | 4,000,000.00 | --                 | --               | AP
30/06/2017  | 2,000,000.00 | --                 | --               | ZA
30/06/2017  | 1,000,000.00 | --                 | --               | LP
31/07/2017  | 5,000,000.00 |1,000,000.00        | 0.25             | AP            
31/07/2017  | 2,000,000.00 |1,000,000.00        | 0.5              | LP 
31/07/2017  | 1,000,000.00 |-1,000,000.00       | -0.5             | ZA

Answer 1

这就是你要找的吗？

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    [Date] DATE NOT NULL,
    Balance MONEY NOT NULL,
    ProductType CHAR(2) NOT NULL 
    );

INSERT #TestData (Date, Balance, ProductType) VALUES
    ('2017-06-30', 4000000, 'AP'),
    ('2017-06-30', 2000000, 'ZA'),
    ('2017-06-30', 1000000, 'LP'),

    ('2017-07-31', 5000000, 'AP'),
    ('2017-07-31', 1000000, 'ZA'),
    ('2017-07-31', 2000000, 'LP');

SELECT * FROM #TestData td;

--==================================================

SELECT 
    td.Date, 
    td.Balance, 
    [EAD MOM Difference] = td.Balance - LAG(td.Balance, 1) OVER (PARTITION BY td.ProductType ORDER BY td.Date),
    [EAD MOM % Difference] = td.Balance / LAG(td.Balance, 1) OVER (PARTITION BY td.ProductType ORDER BY td.Date) - 1,
    td.ProductType
FROM
    #TestData td
ORDER BY
    td.Date,
    td.ProductType;

--==================================================
--==================================================
-- SQL Server (pre 2012) version...
WITH 
    cte_TestDataRN AS (
        SELECT 
            td.Date, td.Balance, td.ProductType,
            RN = ROW_NUMBER() OVER (PARTITION BY td.ProductType ORDER BY td.Date)
        FROM
            #TestData td
        )
SELECT 
    tdr1.Date,
    tdr1.Balance,
    [EAD MOM Difference] = tdr1.Balance - tdr2.Balance,
    [EAD MOM % Difference] = tdr1.Balance / tdr2.Balance - 1,
    tdr1.ProductType
FROM
    cte_TestDataRN tdr1
    LEFT JOIN cte_TestDataRN tdr2
        ON tdr1.ProductType = tdr2.ProductType
        AND tdr1.RN = tdr2.RN + 1
ORDER BY 
    tdr1.Date,
    tdr2.ProductType;