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

I have a simple table with just a DATETIME filed.

我有一个简单的表，只有DATETIME提交。

My question is, how can I get the value related to the end-of-year of previous year, with a window-function query?

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

I've tried with this query but the result is the end-of-year of the current year:

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

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

I am using SQL Server 2012.

我正在使用SQL Server 2012。

Many thanks to all.

非常感谢大家。

2 个解决方案

#1

If you want to filter records then you need to use Where clause. You need something like this not window function.

如果要过滤记录，则需要使用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))

#2

I don't think you need to use a windowed function. A simple filter combined with the max function will return the end of the previous year.

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

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

Although simple, this approach has a small problem. Using the year function, on datefield in the where clause, makes the query non-sargable. If performance is an issue; you could fix by using DateFromParts as demonstrated in @Prdp's fine answer.

虽然简单，但这种方法有一个小问题。在where子句中的datefield上使用year函数，使查询不可搜索。如果表现是一个问题;您可以通过使用DateFromParts来修复，如@ Prdp的精确答案所示。

EDIT

编辑

This version of the query uses a windowed function, as requested by the OP.

此版本的查询使用窗口函数，如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
;

The above only returns one record. If you want see the last day of the previous year, next to every record in your table:

以上只返回一条记录。如果要查看上一年的最后一天，请在表格中的每条记录旁边：

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

#1