如何在Oracle SQL查询中过滤掉当前小时的数据

问题分析 #

你的查询目前仅过滤了当天的数据，但未排除当前小时的记录。之前尝试的TRUNC(oh.creation_date AT TIME ZONE 'Europe/London') < TRUNC(TO_DATE(SYSDATE),'HH')无效，原因是左侧为天级截断的日期，右侧为小时级截断的日期时间，当天日期与右侧的日期部分相等，导致条件不成立，要么过滤掉所有数据，要么无实际过滤效果。

解决方案 #

核心是将订单时间转换为伦敦时区后，与伦敦时区当前小时的起始时间做比较，过滤掉大于等于该时间的记录。需使用带时区的SYSTIMESTAMP函数确保时区转换准确，避免数据库服务器时区与伦敦时区不一致的问题。

修改后的完整查询如下：

SELECT 
lt."Date",
lt."day" "Day",
concat(lt."Integrated In", ':00') "Integrated In",
lt."Qty Ordered",
rt."Qty Ordered" "Sum Ordered",
round((lt."Qty Ordered"/rt."Qty Ordered")*100,2) "Percentage",
sum (lt."Qty Ordered") over (order by lt."Integrated In" rows unbounded preceding 
       ) "Rolling Total"
FROM 
(select
TRUNC(oh.creation_date AT TIME ZONE 'Europe/London') as "Date",
extract(hour from  oh.creation_date AT TIME ZONE 'Europe/London') as "Integrated In",
to_char(oh.creation_date AT TIME ZONE 'Europe/London', 'DAY') "day",
COUNT (ol.qty_ordered) as "Qty Ordered"
from order_header oh
full join order_line ol
on oh.order_id = ol.order_id
WHERE oh.creation_date AT TIME ZONE 'Europe/London' < TRUNC(SYSTIMESTAMP AT TIME ZONE 'Europe/London', 'HH')
AND oh.order_type IN ('UKDCSTDRM', 'UKDCEXPRM', 'UKDCPOPRM', 'UKDCAPRM', 'UKDCEXPUP', 'UKDCSTDUP', 'UKDCAPUPS', 'UKDCPOPUP')
Group By TRUNC(oh.creation_date AT TIME ZONE 'Europe/London'), to_char(oh.creation_date AT TIME ZONE 'Europe/London', 'DAY'), extract(hour from  oh.creation_date AT TIME ZONE 'Europe/London')
Order By to_char(oh.creation_date AT TIME ZONE 'Europe/London', 'DAY'), extract(hour from  oh.creation_date AT TIME ZONE 'Europe/London')) lt

full join 

(select 
to_char(oh.creation_date AT TIME ZONE 'Europe/London') "day",
SUM (ol.qty_ordered) as "Qty Ordered"
from order_header oh
full join order_line ol
on oh.order_id = ol.order_id
WHERE oh.creation_date AT TIME ZONE 'Europe/London' < TRUNC(SYSTIMESTAMP AT TIME ZONE 'Europe/London', 'HH')
AND oh.order_type IN ('UKDCSTDRM', 'UKDCEXPRM', 'UKDCPOPRM', 'UKDCAPRM', 'UKDCEXPUP', 'UKDCSTDUP', 'UKDCAPUPS', 'UKDCPOPUP')
Group by to_char(oh.creation_date AT TIME ZONE 'Europe/London', 'DAY')
Order By to_char(oh.creation_date AT TIME ZONE 'Europe/London', 'DAY')) rt 

on lt."day" = rt."day"

关键修改点 #

• 替换原WHERE条件中的TRUNC(oh.creation_date AT TIME ZONE 'Europe/London') = TO_DATE(SYSDATE)为oh.creation_date AT TIME ZONE 'Europe/London' < TRUNC(SYSTIMESTAMP AT TIME ZONE 'Europe/London', 'HH')：
  • SYSTIMESTAMP AT TIME ZONE 'Europe/London'：获取当前伦敦时区的时间戳
  • TRUNC(..., 'HH')：将时间戳截断到当前小时的起始点（如当前为10:45，截断后为10:00:00）
  • 该条件确保仅包含伦敦时区中当前小时之前的所有记录
• 两个子查询lt和rt都需修改此条件，保证聚合数据均排除当前小时内容

内容的提问来源于stack exchange，提问作者Simon Harris