如何在Oracle SQL中基于天进行计数查询 [英] How to do a count query based on a day in oracle sql

查看：69
本文介绍了如何在Oracle SQL中基于天进行计数查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我一直在寻找如何根据用户和日期编写计数查询的方法.例如，如果我有以下数据:

I was looking to find out how I can write a count query based on a user and day. For example if I have the following data:

Agent      | Date_1
Joe Bloggs | 06-jun-16 10:35:00
Joe Bloggs | 06-jun-16 10:36:00
Joe Bloggs | 06-jun-16 10:46:00
Joe Bloggs | 07-jun-16 09:36:00

我已经能够写出以下内容:

I have been able to write the following:

我当前的查询如下:

SELECT
    AGENT
   ,DATE_1 
   ,ROW_NUMBER() OVER (PARTITION BY AGENT ORDER BY DATE_1) AS COUNT_1    
FROM
    TABLE_1 
    ;

这将根据我的数据对每一行进行顺序计数.但是我想显示的是06年6月6日的计数只有1，因为用户和日期都是相同的，但是我不确定如何得到它.我想要的是:

This gives a sequential count for every row based on my data. But I would like to show a count of only 1 for 06-jun-16 as the user is the same as well as the date, but I'm not sure how I can get that. What I would like is:

Agent Name | Date_1     | Count_1
Joe Bloggs |  06-jun-16 | 1
Joe Bloggs |  07-jun-16 | 1

以下是我的表的创建表和插入语句:

The following is my create table and insert statement for my table:

CREATE TABLE TABLE_1   
   (      
"AGENT" VARCHAR2(12 BYTE),   
"DATE_1" DATE  
   );

插入声明

Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 09:47:23','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:07:47','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:16:48','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:32:31','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:33:35','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 10:49:20','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 11:14:17','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 14:08:13','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));  
Insert into TABLE_1 (AGENT,DATE_1) values ('Joe Bloggs',to_date('07-JUN-16 15:31:05','DD-MON-RR HH24:MI:SS'));

根据以上数据，我希望将大于30分钟的所有触摸都计为一次计数，如果介于30分钟之间的任何时间都不会增加计数.

From the above data I am looking to count all the touches that are greater than 30 mins as a single count, anything that falls in between the 30 minute period doesn't increment the count.

根据我的数据，第一次接触是09:47:23，因此开始时为1，第二次独特接触是10:07:47(基于下一个日期周期)，大约20分钟后，因此计数保持为1，现在来看第3个不同的时间，该时间是10:16:48，比前一个时间晚9分钟，因此计数仍保持为1.

From my data the first touch was 09:47:23 so that starts off as 1, second unique touch was 10:07:47 (based on next date period) which was roughly 20 mins later so count stays at 1, now looking at the 3rd distinct time, this was at 10:16:48 which was 9 mins later from the previous so the count would still stay as 1.

实际上，如果小于30分钟的计数与上一个时间戳相乘，则其滚动30分钟计数与上一个时间戳保持不变，如果其较大的计数增加1 ...

So in effect its a rolling 30 minute count based on the previous timestamp, if its below 30 mins count stays the same as previous if its greater count goes up by 1...

希望这更有意义.

推荐答案

这将为您提供您指定的结果，即每种代理商和日期组合的三十分钟窗口计数.

This will give you the result you specify, a count of thirty minute windows for each combination of agent and date.

select agent
       , trunc(date_1) as date_1
       , sum(intvl)+1 as count_1
from (
    SELECT 
        AGENT
       , DATE_1
       , case when date_1 >= lag(date_1) over (partition by agent order by date_1)
             + to_dsinterval('0 0:30:0') 
          then 1 else 0 end as intvl
    FROM
        TABLE_1
   )
group by agent
       , trunc(date_1)
order by 1, 2
/

通过LAG()分析函数，我们可以将DATE_1的当前值与前一个值进行比较，然后将其增加30分钟的INTERVAL()来测试所需的窗口.截断从DATE_1列中删除时间元素，每天给出一个结果.

The LAG() analytic function allows us to compare the current value of DATE_1 with the preceding one, which is augmented with an INTERVAL() of thirty minutes to test the required window. Truncation removes the time element from the DATE_1 column, giving one result per day.

这篇关于如何在Oracle SQL中基于天进行计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆