在 Microsoft SQL Server Management Studio 中选择特定快照日期？

发布于 2025-01-13 23:44:45 字数 569 浏览 4 评论 0原文

我正在查看 Microsoft SQL Server Management Studio 中的快照表，其中每隔几天捕获一次不同的日期。该表的记录可追溯到 2014 年，并且在可预见的将来应该会继续使用新快照进行更新。例如，2022 年的日期如下：

2022-01-03 00:00:00
2022-01-10 00:00:00
2022-01-12 00:00:00
2022-01-18 00:00:00
2022-01-24 00:00:00
2022-02-03 00:00:00
2022-02-05 00:00:00
2022-02-09 00:00:00
2022-02-14 00:00:00
2022-02-21 00:00:00
2022-03-01 00:00:00
2022-03-07 00:00:00
2022-03-14 00:00:00

我想要做的是仅从最接近月初（或者可能是季度）的捕获日期中选择数据（回溯到 2019 年），并且让查询工作，以便它在四月/五月/六月等时运行。滚来滚去。在上面的示例中，本质上，我希望从 1/03、2/03 和 3/01 快照返回数据，而不考虑其他任何情况。

我该怎么做呢？谢谢你！

原文

I'm looking at a snapshot table in Microsoft SQL Server Management Studio with distinct dates captured every few days. The table has records stretching back to 2014, and should continue updating with new snapshots for the foreseeable future. As an example, the dates for 2022 are as follows:

2022-01-03 00:00:00
2022-01-10 00:00:00
2022-01-12 00:00:00
2022-01-18 00:00:00
2022-01-24 00:00:00
2022-02-03 00:00:00
2022-02-05 00:00:00
2022-02-09 00:00:00
2022-02-14 00:00:00
2022-02-21 00:00:00
2022-03-01 00:00:00
2022-03-07 00:00:00
2022-03-14 00:00:00

What I'm looking to do is select data (stretching back to 2019) from only the date captured dates closest to the beginning of the month (or, perhaps, quarter), and have the query work so that it will function when April/May/June/etc. roll around. In the above example, essentially, I'd be looking to return data from the 1/03, 2/03 and 3/01 snapshots with nothing else considered.

How do I go about doing this? Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卖梦商人 2025-01-20 23:44:45

尝试 APPLY ... TOP 1 查询，例如针对 AdventureWorksDW2017 的示例：

select d.CalendarYear, d.EnglishMonthName, firstSale.*
from DimDate d
outer apply 
(
  select top 1 * 
  from FactInternetSales s
  where s.OrderDate >= d.FullDateAlternateKey
    and s.OrderDate < dateadd(month,1,d.FullDateAlternateKey)
  order by s.OrderDate 
) firstSale
where d.DayNumberOfMonth = 1
and d.CalendarYear in (2012,2013)
order by d.FullDateAlternateKey

这不是定期快照事实，但查询是相同的。

Try an APPLY ... TOP 1 query, like this example against AdventureWorksDW2017:

select d.CalendarYear, d.EnglishMonthName, firstSale.*
from DimDate d
outer apply 
(
  select top 1 * 
  from FactInternetSales s
  where s.OrderDate >= d.FullDateAlternateKey
    and s.OrderDate < dateadd(month,1,d.FullDateAlternateKey)
  order by s.OrderDate 
) firstSale
where d.DayNumberOfMonth = 1
and d.CalendarYear in (2012,2013)
order by d.FullDateAlternateKey

That's not a periodic snapshot fact, but the query is the same.

回复收藏 0 原文

不必在意 2025-01-20 23:44:45

另一种不需要日历的方法是使用 ROW_NUMBER() 窗口函数为每个月内的每一行分配序数值，然后用于过滤除第一行之外的所有行（序数 = 1）。类似于：

SELECT S.Date
FROM (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM SnapshotDates
) S
WHERE S.Ordinal = 1
ORDER BY S.Date

使用 CTE（通用表表达式）的等效查询是：

WITH CTE_SnapshotDates AS (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM @SnapshotDates
)
SELECT S.Date
FROM CTE_SnapshotDates S
WHERE S.Ordinal = 1
ORDER BY S.Date

请参阅 this db<> ;fiddle 进行演示。

Another approach that doesn't require a calendar is to use the ROW_NUMBER() window function to assign ordinal values to each row within each month, which is then used to filter out all but the first (ordinal = 1). Something like:

SELECT S.Date
FROM (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM SnapshotDates
) S
WHERE S.Ordinal = 1
ORDER BY S.Date

The equivalent query using a CTE (Common Table Expression) is:

WITH CTE_SnapshotDates AS (
    SELECT *, Ordinal = ROW_NUMBER() OVER(PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
    FROM @SnapshotDates
)
SELECT S.Date
FROM CTE_SnapshotDates S
WHERE S.Ordinal = 1
ORDER BY S.Date

See this db<>fiddle for a demo.

回复收藏 0 原文

~没有更多了~