每天统计数据 [英] Count Data each days

问题描述

我正在尝试编写一个查询，该查询将为我提供持续时间计数(每位员工每天大于10)的结果.

Hi i'm trying to write a query that will give me the result of Count of duration greater than 10 each employee per day.

同一数据

EVENTID     USERID      DATE_TIME   READERID        READERNAME  LOGTYPE USERNAME    RESULT  DURATION

188110074       50078       2020-03-02 22:01:31 544381441       LOCKER      IN   SUBIA, MAY     00:12   12
188099752       50078       2020-03-02 21:48:52 544381436       LOCKER      OUT  SUBIA, MAY             
188098672       50078       2020-03-02 21:47:10 544381441       LOCKER      IN   SUBIA, MAY             
188098656       50078       2020-03-02 21:47:06 544381441       LOCKER      IN   SUBIA, MAY     15:45   945
187972279       50078       2020-03-02 06:01:54 544381436       LOCKER      OUT  SUBIA, MAY             
187949027       50078       2020-03-02 04:30:46 544381441       LOCKER      IN   SUBIA, MAY     00:16   16
187933475       50078       2020-03-01 22:44:47 544381431       LOCKER      IN   SUBIA, MAY     00:04   4
187933289       50078       2020-03-01 22:40:41 544381436       LOCKER      OUT  SUBIA, MAY             
187926256       50078       2020-03-01 22:01:31 544381431       LOCKER      IN   SUBIA, MAY     00:14   14
187914503       50078       2020-03-01 21:46:42 544381428       LOCKER      OUT  SUBIA, MAY 
180769437       1214201592  2020-01-20 08:13:06 544381436       LOCKER      OUT  ALLAN YAP          
180771730       1214201592  2020-01-20 08:36:38 544381431       LOCKER      IN   ALLAN YAP  00:23   23
180775099       1214201592  2020-01-20 09:07:04 544381436       LOCKER      OUT  ALLAN YAP          
180777340       1214201592  2020-01-20 09:30:53 544381431       LOCKER      IN   ALLAN YAP  00:23   23
180778948       1214201592  2020-01-20 09:48:58 544381436       LOCKER      OUT  ALLAN YAP          
180781714       1214201592  2020-01-20 10:04:52 544381431       LOCKER      IN   ALLAN YAP  00:15   15

预期产量

USERID      DATE_TIME   Count
50078       2020-03-02  3
50078       2020-03-01  1
1214201592  2020-01-20  3

这就是我尝试过的

Select count, USERNAME,  DATE_TIME
from (
Select  A.USERNAME,A.DATE_TIME , Count(A.Duration) count
from TBLACCESSLOGS A    
where Duration > 10 
Group by Date_Time,USERNAME)

注意:Date_time为varchar类型 但是得到了错误的结果，它看起来与我的预期相去甚远.

Note: Date_time is in varchar type but got wrong result , its looks like far from what i expected.

希望有人帮我解决这个问题

Hope someone help me out with this

推荐答案

您非常接近预期的输出.只需按以下方式使用TRUNC:

You are quite closer to your expected output. Just use TRUNC as following:

SELECT
    A.USERNAME,
    A.USERID, -- this
    TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS')) AS DATE_TIME,
    COUNT(A.DURATION) COUNT
FROM
    TBLACCESSLOGS A
WHERE
    DURATION > 10
GROUP BY
    A.USERNAME,
    A.USERID, -- this
    TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS'))

干杯！

这篇关于每天统计数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！