Oracle BI：选择上周的所有记录

发布于 2024-11-02 11:07:52 字数 302 浏览 3 评论 0原文

我需要获取日期在上周周日到周六（含）之间的所有记录，无论查询运行的日期如何。今天，即 2011 年 4 月 19 日，即从 4 月 10 日到 4 月 16 日。

当我手动输入日期并将过滤器转换为 SQL 时，我得到以下语法：

RESOLVED_DATE BETWEEN timestamp '2011-04-10 00:00:00' AND timestamp '2011-04-16 00:00:00'

我什至不确定这是否正确，因为它似乎排除了 16 日本身的日期（时间不应该是 23:59： 59？）

原文

I need to get all records with a date between Sunday and Saturday last week, inclusive, for whatever date the query is run. For today, April 19th 2011, that would be from April 10th to April 16th.

When I entered the dates manually and converted the filter to SQL, I got the following syntax:

RESOLVED_DATE BETWEEN timestamp '2011-04-10 00:00:00' AND timestamp '2011-04-16 00:00:00'

I'm not even sure this is correct, because it seems to exclude dates on the 16th itself (shouldn't the time be 23:59:59?)

分享到QQ

分享到微博

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

发布评论

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

颜 2024-11-09 11:07:52

可以使用 next_day 的组合来确定您想要的日期和常规日期算术。下面的代码应该非常接近，但它未经测试，并且可能在某些极端情况下失败，但至少您了解了总体思路:)

where resolved_date >= next_day( trunc(sysdate) - interval '14' day, 'SUN')
  and resolved_date <  next_day( trunc(sysdate) - interval '7'  day, 'SUN')

trunc(sysdate) 将日期截断为当天； 2011-04-19 23:32:34 变为 2011-04-19 00:00:00，即删除时间部分。
next_day(sysdate, 'SUN') 返回下周日。如果 sysdate 恰好是星期日，则返回下一个星期日。
重要提示：日期名称必须与您的会话使用相同的语言。
interval 只是从日期中添加/减去不同时间单位的标准方法。

总而言之，2011 年 4 月 19 日的逻辑是：

Truncate sysdate => 2011-04-19 00:00:00
减去14天=> 2011-04-05 00:00:00
查找下周日=> 2011-04-10 00:00:00

...并

截断 sysdate => 2011-04-19 00:00:00
减去7天=> 2011-04-12 00:00:00
查找下周日=> 2011-04-17 00:00:00

..导致以下查询：

 where resolved_date >= timestamp '2011-04-10 00:00:00'
   and resolved_date <  timestamp '2011-04-17 00:00:00'

将包含在 10:th 的第一秒或之后但在 17:th 的第一秒之前发生的所有resolved_dates。请注意，>= 和 < 并不等同于 Between。

关于性能的说明：我将确保 Oracle 正确估计日期范围为 7 天，并且使用正确的连接顺序/方法。如果您希望查询运行一段时间，您可以计算应用程序中的日期并将它们作为日期文字提供，而不是像我上面那样动态计算它们。

It is possible to determine the dates you want using combinations of next_day and regular date arithmetic. Below code should be pretty close, but it's untested and probably fails on some corner case, but at least you get the general idea :)

where resolved_date >= next_day( trunc(sysdate) - interval '14' day, 'SUN')
  and resolved_date <  next_day( trunc(sysdate) - interval '7'  day, 'SUN')

trunc(sysdate) truncate the date to day; 2011-04-19 23:32:34 becomes 2011-04-19 00:00:00, i.e. removing the time component.
next_day(sysdate, 'SUN') returns the next sunday. If sysdate happens to be a sunday, the next sunday is returned.
Important: The day names have to be in the same language as your session.
The interval thing is just a standard way of adding/subtracting different units of time from a date.

Putting it all together, the logic for the 19th of April 2011 would be:

Truncate sysdate => 2011-04-19 00:00:00
subtract 14 days => 2011-04-05 00:00:00
Find the next sunday => 2011-04-10 00:00:00

...and

Truncate sysdate => 2011-04-19 00:00:00
subtract 7 days => 2011-04-12 00:00:00
Find the next sunday => 2011-04-17 00:00:00

..resulting in the following query:

 where resolved_date >= timestamp '2011-04-10 00:00:00'
   and resolved_date <  timestamp '2011-04-17 00:00:00'

All resolved_dates that happened on or after the first second of the 10:th but before the first second of the 17:th would be included. Note that >= and < isn't equivalent to between.

A note on performance: I would make sure that Oracle correctly estimates the date range to be 7 days, and that the correct join order/method is used. If you expect the query to run for a while, you can afford to calculate the dates in the application and supply them as date litterals instead of computing them on the fly like I did above.

回复收藏 0 原文