Oracle SQL：4小时计数？ [英] Oracle SQL: Counting in 4h?

问题描述

我有许多记录表的Oracle 10g数据库。
表格为接受某种订单的员工提供数据。
最可用的列是员工，dt和orderId（我已附加示例）。
我想构建select，它会选择任何 4h中有6个或更多订单的雇员的记录。我使用了常规连接，各种功能，但我仍然没有做出可用的选择。
我无法做出选择，将过去（4小时）的每个记录搜索，看看是否有6个订单为同一名雇员。

任何帮助或解决方案是赞赏。

BR，

IJ

http://sqlfiddle.com/#!2/77418/1

输出的select应该是：

Joe 19.2.2013 13:28:09 36589589

Joe 19.2.2013 13:50:59 36589593

马特19.2.2013 13:57:02 36589594

Joe 19.2.2013 14:19:46 36589598

Benny 19.2.2013 14:50:28 36589601

Benny 19.2.2013 14:54:58 36589602

Benny 19.2.2013 14:56:35 36589603

Ray 19.2.2013 15:26:39 36589607

Ray 19.2.2013 15:26:41 36589608

Ray 19.2.2013 15:26:44 36589609

Ray 19.2.2013 15:48:11 36589611

本尼19.2.2013 16:48： 52 36589614

本尼19.2.2013 16:49:40 36589615

本尼19.2.2013 16:50： 38 36589616

约翰尼19.2.2013 17:37:33 36589620

约翰尼19.2.2013 17:51： 41 36589621

Joe 19.2.2013 18:16:55 36589625

Johnny 19.2.2013 18:34： 14 36589626

马特19.2.2013 21:13:50 36589632

解决方案

<你可以用 lag（）函数来做到这一点：

 选择t。*，
 from（选择t。*，lag（dt，6）over（由employeeId order by dt划分）as ord6dt 
 from t 
）t 
 where dt-ord6dt< 4.0 / 24 
  

这只是查看当前的订单6并比较日期。只保留时间差小于4小时的行。

I have oracle 10g database with table of many records. Table has data for employees receiving some kind of orders. Most usable columns are employee, dt and orderId (I've attached example). I want to construct select that will select records of an employee that had 6 or more orders in any 4h. I used regular join, various functions but I still didn't made usable select. I couldn't make select that will for each record search in past(4h) and see if there are 6 orders for same employee.

Any help or solution is appreciated.

BR,

IJ

http://sqlfiddle.com/#!2/77418/1

Output of select should be:

Joe 19.2.2013 13:28:09 36589589

Joe 19.2.2013 13:50:59 36589593

Matt 19.2.2013 13:57:02 36589594

Joe 19.2.2013 14:19:46 36589598

Benny 19.2.2013 14:50:28 36589601

Benny 19.2.2013 14:54:58 36589602

Benny 19.2.2013 14:56:35 36589603

Ray 19.2.2013 15:26:39 36589607

Ray 19.2.2013 15:26:41 36589608

Ray 19.2.2013 15:26:44 36589609

Ray 19.2.2013 15:48:11 36589611

Benny 19.2.2013 16:48:52 36589614

Benny 19.2.2013 16:49:40 36589615

Benny 19.2.2013 16:50:38 36589616

Johnny 19.2.2013 17:37:33 36589620

Johnny 19.2.2013 17:51:41 36589621

Joe 19.2.2013 18:16:55 36589625

Johnny 19.2.2013 18:34:14 36589626

Matt 19.2.2013 21:13:50 36589632

解决方案

You can do this with the lag() function:

select t.*, 
from (select t.*, lag(dt, 6) over (partition by employeeId order by dt) as ord6dt
      from t
     ) t
where dt - ord6dt < 4.0/24

This just looks at order 6 before the current and compares the dates. Only rows where the difference in time is less than 4 hours are kept.

这篇关于Oracle SQL：4小时计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！