SQL查询 - 简化重写

Question

使用SQL Server 2016

我有以下T-SQL查询，它能够完成我需要的功能，但是它包含了很多重复内容。有没有更优雅的方式来重写它？

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, 0, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -1, @date)
) z
WHERE z.rowNum = 1
UNION
SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, -1, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -2, @date)
) z
WHERE z.rowNum = 1
UNION
SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, -2, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -3, @date)
) z
WHERE z.rowNum = 1;

etc....for 12 times

如您所见，我正在使用一个月的日期偏移量将相同的语句 UNION 12 次。 请给予建议。 谢谢。

- user5326167

---

没有示例数据和预期输出很难看出你在做什么，但似乎可以使用OVER和它的ROWS子句来限制你想要应用聚合或函数的行数来解决这个问题。 - S3S

好的，让我添加一些虚拟数据。谢谢您的关注。 - user5326167

是否同意评论？看起来是每个月的第一条记录吗？ - Juan Carlos Oropeza

每个月的最后一条记录 - user5326167

抱歉，各位，我修改了它：所有增量都为负数。 - user5326167

Answer 1

测试脚本很困难，但是你可以尝试以下方法：

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id], DATEADD(MONTH,-1 * t.num, @date) ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, t.num, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)  )  t(num)
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -1 * t.num, @date)
) z
WHERE z.rowNum = 1

- Dmitrij Kultasev

---

根据 OP 的编辑，使用负数来表示 t(num)。 - Juan Carlos Oropeza

哦，他修改了问题。 - Dmitrij Kultasev

Answer 2

使用案例：

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id], MONTH([action_timestamp] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           CASE WHEN [action_timestamp_formatted] < DATEADD(MONTH, 1, @date) THEN DATEADD(MONTH, 0, @date)
                WHEN [action_timestamp_formatted] < DATEADD(MONTH, 2, @date) THEN DATEADD(MONTH, 1, @date)
                WHEN [action_timestamp_formatted] < DATEADD(MONTH, 3, @date) THEN DATEADD(MONTH, 2, @date) END AS [date]
    FROM [dbo].[CR_hist_Data]
) z
WHERE z.rowNum = 1

- kjmerf

---

它将不会返回与您具有相同分区子句的相同结果集，因此它将返回与第一个 union 相同的数据。 - Dmitrij Kultasev

Answer 3

我认为使用公共表达式可能会有所帮助。

with dates(r, currmonth, lastmonth) as
(select 1 r, eomonth(@date) CurrMonth, eomonth(@date,-1) LastMonth
 union all
 select r+1, eomonth(@date,-1*r), eomonth(@date,-1*(r+1)) from dates
 where r < 12)

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id],dates.r ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           dates.currmonth AS [date]
    FROM [dbo].[CR_hist_Data]
    join dates on 1=1
    WHERE [action_timestamp_formatted] < dates.lastmonth
) z
WHERE z.rowNum = 1

- Patrick H