如何在具有SQL Server中的窗口函数的数据集中获取上一年的最大日期

Question

我有一个简单的表格，只有一个DATETIME文件。

我的问题是，如何使用窗口函数查询获取与上一年年末相关的值？

我尝试过这个查询，但结果是当年的年底：

SELECT datefield, max(datefield) OVER (PARTITION BY YEAR(datefiled)) FROM foo

我正在使用 SQL Server 2012。

非常感谢大家。

Answer 1

如果要过滤记录，则需要使用Where子句。你需要这样的东西，而不是窗口函数。

SELECT TOP 1 WITH ties *
FROM   foo
WHERE  datefield <= Datefromparts(Year(Getdate()) - 1, 12, 31)
ORDER  BY datefield DESC 

或

SELECT *
FROM   foo
WHERE  datefield = (SELECT Max(datefield) AS last_date_prev_year
                    FROM   foo
                    WHERE  datefield <= Datefromparts(Year(Getdate()) - 1, 12, 31)) 

Answer 2

我认为你不需要使用窗口函数。 一个简单的过滤器与 max 函数相结合，将返回上一年的年底。

-- Where clause removes records from current and future years.
SELECT 
    MAX(datefield)
FROM
    foo
WHERE
    YEAR(datefield) < YEAR(GETDATE())
;

这种方法虽然简单，但有一个小问题。 在 where 子句中datefield使用 year 函数会使查询不可优化优化。 如果性能是一个问题;您可以使用DateFromParts进行修复，如@Prdp的精美答案所示。

编辑

此版本的查询根据 OP 的请求使用窗口函数。

-- Max of previous year, using a windowed function.
SELECT
    MAX(datefield) AS LastYearEnd
FROM
    (
        -- Rank records based on year.
        -- Current year is 1, last year is 2, etc.
        SELECT
            datefield,
            DENSE_RANK() OVER (ORDER BY YEAR(datefield) DESC) AS rn
        FROM
            foo
    ) AS dr
WHERE
    rn = 2
;

以上仅返回一条记录。 如果您想在表格中的每条记录旁边查看上一年的最后一天：

-- Returns last day of previous year, relative to dateField.
SELECT
    datefield,
    DATEADD(YEAR, -1, MAX(datefield) OVER (PARTITION BY YEAR(datefield)))
FROM
    foo
;