SQL Server 2012：如何仅使用每月第一天的值来计算季度平均值(SQL Server 2012: How to calculate Quarterly Average with only v

SQL Server 2012：如何仅使用每月第一天的值来计算季度平均值(SQL Server 2012: How to calculate Quarterly Average with only values from the first of each month)

假设我有下表：

CREATE TABLE Portfolio.DailyNAV ( Date date NOT NULL, NAV int NOT NULL, ) GO

日期列具有从“2015-02-02”开始的每日工作日，并且NAV列具有该日的总资产价值。 我想获得四分之一的平均资产净值。 对于这个例子，我们假设我想在2016年第二季度得到它。我的代码现在是：

Select AVG(NAV) As AvgNAV FROM Portfolio.DailyNAV WHERE year(Date) = '2016' AND DATEPART(QUARTER,Date) = '2' GO

我面临的问题是，此代码计算该季度的每日平均值，但平均净资产值应仅使用该季度每个月的第一个营业日期计算。 因此，对于2016年第二季度，平均资产净值应该是2016-04-01,2016-05-02（第一个不是工作日）和2016-06-01的平均值。 我不想简单地更改我的WHERE子句并使用这些日期，因为我想创建一个存储过程，用户可以通过放入年份和季度来获得平均资产净值。

谢谢。

Let's say I have the following table:

CREATE TABLE Portfolio.DailyNAV ( Date date NOT NULL, NAV int NOT NULL, ) GO

The date column has the daily business day starting from '2015-02-02' and the NAV column has that day's total asset value. I want to get the average NAV for a quarter. For this example, let's assume I want to get it for the 2nd Quarter of 2016. My code is now:

Select AVG(NAV) As AvgNAV FROM Portfolio.DailyNAV WHERE year(Date) = '2016' AND DATEPART(QUARTER,Date) = '2' GO

The problem I am facing is that this code calculates the daily average for the quarter but Average NAV should be calculated only using the first business date of each month for that quarter. So for 2016 Q2, the Average NAV should be the average from 2016-04-01, 2016-05-02 (the 1st was not a work day) and 2016-06-01. I don't want to simply change my WHERE clause and use those dates because at I want to make a stored procedure where the user can get the average NAV by putting in the Year and Quarter.

Thanks.

最满意答案

这应该工作：

WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER( PARTITION BY CONVERT(VARCHAR(6),[Date],112) ORDER BY [Date]) FROM Portfolio.DailyNAV WHERE YEAR([Date]) = 2016 AND DATEPART(QUARTER,[Date]) = 2 AND NAV IS NOT NULL ) SELECT AVG(NAV) AvgNAV FROM CTE WHERE RN = 1;

This should work:

WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER( PARTITION BY CONVERT(VARCHAR(6),[Date],112) ORDER BY [Date]) FROM Portfolio.DailyNAV WHERE YEAR([Date]) = 2016 AND DATEPART(QUARTER,[Date]) = 2 AND NAV IS NOT NULL ) SELECT AVG(NAV) AvgNAV FROM CTE WHERE RN = 1;