将日期范围转换为每天记录的 SQL 查询 [英] SQL query to convert date ranges to per day records

问题描述

1. 我有在日期范围内保存数据的数据表.
2. 每条记录都可以与之前的记录重叠(记录有一个 CreatedOn datetime 列).
3. 如果需要，新记录可以定义自己的日期范围，因此可以与多个旧记录重叠.
4. 每个新的重叠记录都会覆盖它重叠的旧记录的设置.

结果集

我需要获取的是获取任何使用记录重叠的日期范围的每日数据.它应该每天返回一条记录，其中包含该特定日期的相应数据.

Result set

What I need to get is get per day data for any date range that uses record overlapping. It should return a record per day with corresponding data for that particular day.

要将范围转换为天数，我想到了数字/日期表 和用户定义的函数 (UDF) 来获取范围内每一天的数据，但我想知道是否还有其他的(如更好* 甚至更快) 这样做的方式，因为我使用的是最新的 SQL Server 2008 R2.

To convert ranges to days I was thinking of numbers/dates table and user defined function (UDF) to get data for each day in the range but I wonder whether there's any other (as in better* or even faster) way of doing this since I'm using the latest SQL Server 2008 R2.

想象一下我存储的数据是这样的

Imagine my stored data looks like this

ID | RangeFrom | RangeTo  | Starts | Ends  | CreatedOn (not providing data)
---|-----------|----------|--------|-------|-----------
1  | 20110101  | 20110331 | 07:00  | 15:00
2  | 20110401  | 20110531 | 08:00  | 16:00
3  | 20110301  | 20110430 | 06:00  | 14:00 <- overrides both partially

结果

如果我想获取 2011 年 1 月 1 日至 2001 年 5 月 31 日的数据，结果表应如下所示(省略明显的行):

Results

If I wanted to get data from 1st January 2011 to 31st May 2001 resulting table should look like the following (omitted obvious rows):

DayDate | Starts | Ends
--------|--------|------
20110101| 07:00  | 15:00  <- defined by record ID = 1
20110102| 07:00  | 15:00  <- defined by record ID = 1
...                          many rows omitted for obvious reasons
20110301| 06:00  | 14:00  <- defined by record ID = 3
20110302| 06:00  | 14:00  <- defined by record ID = 3
...                          many rows omitted for obvious reasons
20110501| 08:00  | 16:00  <- defined by record ID = 2
20110502| 08:00  | 16:00  <- defined by record ID = 2
...                          many rows omitted for obvious reasons
20110531| 08:00  | 16:00  <- defined by record ID = 2

推荐答案

如果可以在内存中做得更好，为什么还要在 DB 中完成

这是在数据传输、速度和资源方面似乎最合理的解决方案(我最终使用了).

Why do it all in DB when you can do it better in memory

This is the solution (I eventually used) that seemed most reasonable in terms of data transferred, speed and resources.

1. 获取从数据库到中间层的实际范围定义(数据量较小)
2. 在内存中生成特定日期范围的日历(比在 DB 中更快)
3. 将这些 DB 定义放入(比 DB 更容易和更快)

就是这样.我意识到，当您在内存中拥有可执行代码并且可以更快、更高效地执行相同操作时，将 DB 中的某些事情复杂化是不值得的.

And that's it. I realised that complicating certain things in DB is not not worth it when you have executable in memory code that can do the same manipulation faster and more efficient.

这篇关于将日期范围转换为每天记录的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！