查找多个类别的时间间隔的总和oracle SQL

mftmpeh8 于 2023-08-03 发布在 Oracle

关注(0)|答案(2)|浏览(105)

我有一个Oracle数据库，每次用户进行操作时都会保存，我想知道他们每个人工作了多少小时。我找到这个数字的逻辑如下：对于在10分钟时间跨度内完成的每个操作，该时间被认为是“工作时间”。
所以，如果有人在10：05做了一个手术，在10：25做了另一个手术，我会认为他从10：00-10：10和10：20-10：30工作，总共20分钟。
我希望有一个查询，计算总分钟数，遵循这个逻辑，划分为每个用户。
我试着跟随this blog post，并且能够实现预期的结果，但是只有一个用户。一旦我尝试包含多个用户，查询将无限运行。下面是只对一个用户有效的查询。
我如何能够在多个用户中复制这种逻辑？

WITH database AS(
SELECT
    operation_date,
    user
FROM
    database
where 
    user = 'user1'
)
    ,start_date as (
  select 
  min (operation_date ) AS start_date,
         10 / 1440 AS time_interval
  from   database
)
, intervals as (
  select 
  
  start_date + ( ( level - 1 ) * time_interval ) start_date,
         start_date + ( ( level ) * time_interval ) end_date
  from   start_date
  connect by level <= 144
)

,timesum as(
  select
     user,
     to_char(start_date,'dd/mm/yyyy hh24:mi'),
     count ( operation_date ) as oper_num
  from   intervals
  left  join database
  on     start_date <= operation_date
  and    operation_date < end_date
  group  by user, start_date
  order  by start_date
  )
  
  select
  user,
  (count(oper_num)*10)/60 as hours
  from timesum
  where
  oper_num > 0
  group by user

字符串

oracle

来源：https://stackoverflow.com/questions/76757856/find-the-sum-of-intervals-of-time-for-multiple-categories-oracle-sql

2条答案

按热度按时间

vybvopom1#

因为我的评论似乎有帮助，我把它写下来作为一个答案。
由于您只需要总数，并且周期似乎是相对于午夜的10分钟间隔（而不是相对于用户的第一个操作），因此您需要做的就是将时间舍入到10分钟的下限，然后计算不同的值。你可以沿着以下方式做一些事情：

SELECT 
  "USER", 
  COUNT(DISTINCT operation_date - MOD(TO_CHAR(operation_date, 'MI'), 10)/(24*60) - TO_CHAR(operation_date, 'SS')/(24*60*60)) * 10/60 t
FROM "DATABASE"
GROUP BY "USER"
ORDER BY "USER"

字符串
你可以看到working in this Fiddle。
- MOD(TO_CHAR(operation_date, 'MI'), 10)/(24*60)将获得10分钟发言权（例如10：23：01 -> 10：20：01），- TO_CHAR(operation_date, 'SS')/(24*60*60)将删除秒（例如10：20：01 -> 10：20：00）。然后，通过计算每个用户的不同值，您可以知道他们工作了多少个10分钟的时间段，并可以计算总时间。
(In评论，我的原始版本没有考虑到秒，但它在这里清理。我还在我的一天<->分钟算术四舍五入分钟的错误。）
这可能需要更多的清理，使用interval而不是24*60和24*60*60算法，并显式使用to_number而不是依赖于自动转换。但其中一些将是首选项，其中一些将取决于您使用它的实际上下文。
我保留了问题中的列/表名称。正如其他人在评论和其他答案中所指出的那样，实际使用这些名称并不是一个好主意，因为它只会引起混乱和不必要的复杂性。

赞(0）回复(0）举报 2023-08-03

jqjz2hbq2#

下面我提出一个稍微不同的方法。在这里，我动态计算一组由小时和开始分钟/结束分钟定义的“时间范围”。它被连接到操作数据，然后连接的行数确定每个用户和日期的工作周期数。这种方法不仅是多用户的，它还允许从操作数据中自由选择任何日期范围。

WITH RANGES AS (
  -- creates a row for each 10 minute interval within a day
  SELECT
        START_HOUR
      , START_MINUTE
      , CASE WHEN END_MINUTE = 0 THEN 60 ELSE END_MINUTE END END_MINUTE
  FROM (
    SELECT
        EXTRACT(HOUR FROM CAST((TRUNC(CURRENT_TIMESTAMP) + (LEVEL - 1) / 144) AS TIMESTAMP)) start_hour
      , EXTRACT(MINUTE FROM CAST((TRUNC(CURRENT_TIMESTAMP) + (LEVEL - 1) / 144) AS TIMESTAMP)) start_minute
      , EXTRACT(MINUTE FROM CAST((TRUNC(CURRENT_TIMESTAMP) + (LEVEL) / 144) AS TIMESTAMP)) end_minute
    FROM DUAL
    CONNECT BY LEVEL <= 144
    )
  )
SELECT 
    USER_ID
  , TRUNC(operation_date)
  , COUNT(*) AS ACTIVE_PERIODS
FROM OPS_DATA
INNER JOIN RANGES 
    ON  extract(HOUR from cast(operation_date as timestamp) ) = RANGES.start_hour
    AND extract(MINUTE from cast(operation_date as timestamp) ) >= start_minute
    AND extract(MINUTE from cast(operation_date as timestamp) ) < end_minute
-- WHERE operation_date >= ?? and operation_date < ?? -- optional
GROUP BY
    USER_ID
  , TRUNC(operation_date)

字符串
| TRUNC（OPERATION_DATE）|活动周期| ACTIVE_PERIODS |
| --|--| ------------ |
| 2023年7月1日|二个| 2 |
| 2023年7月1日|二个| 2 |
| 2023年7月1日|一个| 1 |
| 2023年7月2日|二个| 2 |
| 2023年7月2日|二个| 2 |
| 2023年7月2日|一个| 1 |
| 2023年7月3日|二个| 2 |
| 2023年7月3日|二个| 2 |
| 2023年7月3日|一个| 1 |
fiddle

nb我假设操作信息表称为“ops_data”，并且列operation_date是“date数据类型”。我还使用了“user_id”作为列名，因为“user”是一个保留字。请查看链接的fiddle，以获取所使用的示例数据和中间查询结果，这可能有助于解释逻辑。

赞(0）回复(0）举报 2023-08-03

我来回答

查找多个类别的时间间隔的总和oracle SQL

2条答案

相关问题

热门标签

最新问答