Oracle BI：选择上周的所有记录 [英] Oracle BI: Select all records from last week
问题描述
无论运行查询的日期如何，我都需要获取上周日和周六之间（包括首尾两天）的所有记录。对于今天，2011年4月19日，从4月10日到4月16日。
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.
当我手动输入日期并将过滤器转换为SQL时，我得到了以下语法：
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'
我什至不确定这是正确的，因为它似乎排除了日期16号本身（时间不应该是23:59:59？）
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?)
推荐答案
可以确定您的日期想要结合使用 next_day 和常规日期算术的组合。下面的代码应该很接近，但是它未经测试并且可能在某些极端情况下失败，但是至少您有一个大致的了解：）
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）截断日期； 2011-04-19 23:32:34变为2011-04-19 00:00:00，即删除时间部分。
next_day（sysdate， SUN）返回下一个星期日。如果sysdate恰好是星期日，则返回下一个星期天。
重要：日期名称必须与您的会话使用相同的语言。
interval 只是从日期添加/减去不同时间单位的一种标准方法。
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.
总而言之，2011年4月19日的逻辑将是：
Putting it all together, the logic for the 19th of April 2011 would be:
- 截断日期=> 2011-04-19 00:00:00
- 减去14天=> 2011-04-05 00:00:00
- 查找下一个星期日=> 2011-04-10 00:00:00
...和
- 截断日期=> 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的第一秒之前。请注意，> = 和< 不等同于 。
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.
性能说明：我将确保Oracle正确估计日期范围为7天，并使用正确的加入顺序/方法。如果您希望查询运行一段时间，则可以负担得起在应用程序中计算日期并将其作为日期基准提供的功能，而不必像上面我那样即时进行计算。
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.
这篇关于Oracle BI：选择上周的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
