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

问题描述

1. 我有保存日期范围内数据的数据表.
2. 允许每条记录与之前的记录重叠(记录有一个 CreatedOn 日期时间 列).
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.

要将范围转换为天数，我在考虑 numbers/dates 表 和用户定义函数 (UDF) 来获取范围内每一天的数据，但我想知道是否还有其他方法(如 better* 甚至 faster) 这样做的方式，因为我使用的是最新的 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. 在内存中生成特定日期范围的日历(比在数据库中更快)
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屋！