数据集技术问题：如何从按年度分组的列中取值计算？

你提供的SQL语句如下：

SELECT
dbo.TOWARY.TWR_NUMER AS INDEKS,
dbo.TOWARY.TWR_NAZWA AS NAZWA,
dbo.SL_JM.SLJM_KOD AS JM,
SUM (dbo.WAZENIA.WZN_ILOSC * dbo.WZN_CNY.WZNCNY_NETTO) / SUM (dbo.WAZENIA.WZN_ILOSC) AS CENA,
DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU) AS ROK,
dbo.MAGAZYNY.MG_KOD AS MG_KOD,
dbo.MAGAZYNY.MG_NAZWA AS MG_NAZWA,

ROW_NUMBER () OVER (
        PARTITION BY
        dbo.TOWARY.TWR_NUMER
       ORDER BY
       dbo.TOWARY.TWR_NUMER,
       DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU) DESC
) AS NUMER

针对你需要计算连续年度CENA比值（如2021/2020、2022/2021）以及当前年度与基准年度（最早年度）比值的需求，提供两种解决方案：

方案1：在SQL层面预计算比值 #

利用窗口函数直接在数据源中计算所需比值，报表端可直接展示结果，效率更高。修改后的SQL如下：

WITH BaseData AS (
    SELECT
        dbo.TOWARY.TWR_NUMER AS INDEKS,
        dbo.TOWARY.TWR_NAZWA AS NAZWA,
        dbo.SL_JM.SLJM_KOD AS JM,
        SUM(dbo.WAZENIA.WZN_ILOSC * dbo.WZN_CNY.WZNCNY_NETTO) / SUM(dbo.WAZENIA.WZN_ILOSC) AS CENA,
        DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU) AS ROK,
        dbo.MAGAZYNY.MG_KOD AS MG_KOD,
        dbo.MAGAZYNY.MG_NAZWA AS MG_NAZWA
    FROM dbo.TOWARY
    -- 补充你原SQL中缺失的表关联条件
    JOIN dbo.WAZENIA ON [你的关联条件]
    JOIN dbo.WZN_CNY ON [你的关联条件]
    JOIN dbo.ZAKUPY ON [你的关联条件]
    JOIN dbo.SL_JM ON [你的关联条件]
    JOIN dbo.MAGAZYNY ON [你的关联条件]
    GROUP BY 
        dbo.TOWARY.TWR_NUMER,
        dbo.TOWARY.TWR_NAZWA,
        dbo.SL_JM.SLJM_KOD,
        DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU),
        dbo.MAGAZYNY.MG_KOD,
        dbo.MAGAZYNY.MG_NAZWA
)
SELECT
    *,
    -- 连续年度同比比值：当前年度CENA / 上一年度CENA
    CENA / LAG(CENA) OVER (PARTITION BY INDEKS ORDER BY ROK) AS CENA_RATIO_YOY,
    -- 与基准年度（最早年度）的比值：当前年度CENA / 该INDEKS最早年度的CENA
    CENA / FIRST_VALUE(CENA) OVER (PARTITION BY INDEKS ORDER BY ROK) AS CENA_RATIO_BASE
FROM BaseData
ORDER BY INDEKS, ROK DESC;

关键说明 #

• LAG(CENA) OVER (PARTITION BY INDEKS ORDER BY ROK)：按INDEKS分组、年度升序排列，获取当前行的上一行（上一年度）的CENA值。
• FIRST_VALUE(CENA) OVER (PARTITION BY INDEKS ORDER BY ROK)：获取该INDEKS分组中最早年度的CENA值，作为基准值。
• 需补充原SQL中缺失的表JOIN条件，否则无法正常执行。

方案2：在SSRS报表中通过表达式计算 #

如果无法修改SQL，可在SSRS中通过表达式直接计算比值：

计算连续年度同比比值 #

=IIF(
    Lookup(Fields!INDEKS.Value & (Fields!ROK.Value - 1), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称") Is Nothing 
    OR Lookup(Fields!INDEKS.Value & (Fields!ROK.Value - 1), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称") = 0,
    Nothing,
    Fields!CENA.Value / Lookup(Fields!INDEKS.Value & (Fields!ROK.Value - 1), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称")
)

计算与基准年度的比值 #

=IIF(
    Lookup(Fields!INDEKS.Value & Min(Fields!ROK.Value, "INDEKS"), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称") Is Nothing 
    OR Lookup(Fields!INDEKS.Value & Min(Fields!ROK.Value, "INDEKS"), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称") = 0,
    Nothing,
    Fields!CENA.Value / Lookup(Fields!INDEKS.Value & Min(Fields!ROK.Value, "INDEKS"), Fields!INDEKS.Value & Fields!ROK.Value, Fields!CENA.Value, "你的数据集名称")
)

关键说明 #

• 用INDEKS+ROK拼接成唯一键，通过Lookup函数定位对应年度的CENA值。
• 加入IIF判断避免空值或除以0的报错，无有效数据时返回空值。
• 替换你的数据集名称为实际数据集名称，INDEKS为你的分组组名。

内容的提问来源于stack exchange，提问作者Artur