时间戳

首页 > 解决方案 > 使用 CASE 语句和 GROUP BY 子句的 LAST_VALUE 聚合函数

sql-server - 使用 CASE 语句和 GROUP BY 子句的 LAST_VALUE 聚合函数

问题描述

我正在使用一个MERGE语句，在SELECT查询中我试图在具有不同汇总类型（例如 SUM、AVG 等）的语句中使用具有聚合函数的函数LAST_VALUE。CASE我想从子句中排除[Value],[Target]和[Budget]字段。GROUP BY我正在使用OVER ORDER BY语法，但不断收到以下错误：

消息 8120，级别 16，状态 1，第 12
行列“@TempFact_KPI.Value”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

代码：

CREATE TYPE [dbo].[TempFact_KPI] AS TABLE(
[PeriodKey] [uniqueidentifier] NULL,
[KPIKey] [uniqueidentifier] NULL,
[SummaryType] [nvarchar](50) NULL,
[PeriodDateTime] [datetime] NULL,
[Value] [real] NULL,
[Target] [real] NULL,
[Budget] [real] NULL
)
GO

存储过程：

SELECT DISTINCT
    [PeriodKey],
    [KPIKey],
    CASE [SummaryType]
       WHEN 'Sum' THEN SUM([Value])
       WHEN 'Count' THEN COUNT([Value])
       WHEN 'Mean' THEN AVG([Value])
       WHEN 'Maximum' THEN MAX([Value])
       WHEN 'Minimum' THEN MIN([Value])
       WHEN 'Last' THEN LAST_VALUE([Value]) OVER (ORDER BY [PeriodDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       ELSE [Value]
    END as [Value],
    CASE [SummaryType]
       WHEN 'Sum' THEN SUM([Target])
       WHEN 'Count' THEN COUNT([Target])
       WHEN 'Mean' THEN AVG([Target])
       WHEN 'Maximum' THEN MAX([Target])
       WHEN 'Minimum' THEN MIN([Target])
       WHEN 'Last' THEN LAST_VALUE([Target]) OVER (ORDER BY [PeriodDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       ELSE [Target]
    END AS [Target],
    CASE [SummaryType]
       WHEN 'Sum' THEN SUM([Budget])
       WHEN 'Count' THEN COUNT([Budget])
       WHEN 'Mean' THEN AVG([Budget])
       WHEN 'Maximum' THEN MAX([Budget])
       WHEN 'Minimum' THEN MIN([Budget])
       WHEN 'Last' THEN LAST_VALUE([Budget]) OVER (ORDER BY [PeriodDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       ELSE [Budget]
    END AS [Budget] 
FROM 
    @TempFact_KPI
GROUP BY 
    [PeriodKey], [KPIKey], [SummaryType]

我已经查看了发布的建议Query for Min, Max, Avg, and Last Value in TSQL and SQL Exclude Field from GROUP BY in results but use in WHERE，但它仍然没有提供想要的结果

标签： sql-servertsqlmergesql-server-2012

解决方案

您可以使用窗口函数：

SELECT DISTINCT
[PeriodKey]
,[KPIKey]
,CASE [SummaryType]
    WHEN 'Sum'     THEN SUM([Value])   OVER(PARTITION BY [PeriodKey], [KPIKey], [SummaryType])
    WHEN 'Count'   THEN COUNT([Value]) OVER(PARTITION BY [PeriodKey], [KPIKey], [SummaryType])
    WHEN 'Mean'    THEN AVG([Value])   OVER(PARTITION BY [PeriodKey], [KPIKey], [SummaryType])
    WHEN 'Maximum' THEN MAX([Value])   OVER(PARTITION BY [PeriodKey], [KPIKey], [SummaryType])
    WHEN 'Minimum' THEN MIN([Value])   OVER(PARTITION BY [PeriodKey], [KPIKey], [SummaryType])
    WHEN 'Last'    THEN LAST_VALUE([Value]) OVER (PARTITION BY [PeriodKey], [KPIKey], [SummaryType] ORDER BY [PeriodDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ELSE [Value]
END as [Value]
 -- ...
FROM @TempFact_KPI;

推荐阅读