sql - 按小时分组的开始时间和结束时间的差异-6ren

sql - 按小时分组的开始时间和结束时间的差异

转载作者：行者123 更新时间：2023-12-01 12:48:04

我的表有类似下面的信息

Emp  Date       START_TIME            END_TIME             Code     Minutes
---  --------   -------------------   -------------------  ----     -------
 E1  11/1/2012  11/1/2012 6:55:00 AM  11/1/2012 7:01:00 AM  C1       6
 E1  11/1/2012  11/1/2012 6:57:00 AM  11/1/2012 8:01:00 AM  C2       64
 E2  11/1/2012  11/1/2012 6:57:00 AM  11/1/2012 8:00:00 AM  C2       63
 E1  11/2/2012  11/2/2012 7:35:00 AM  11/2/2012 8:01:00 AM  C1       26

预期输出是

Date       Code  Range                        Minutes
---------  ----  -----------------------      ------- 
11/1/2012   C1   6:30:00 AM-7:00:00 AM           5
11/1/2012   C1   7:00:00 AM-7:30:00 AM           1
11/1/2012   C2   6:30:00 AM-7:00:00 AM           6   
11/1/2012   C2   7:00:00 AM-7:30:00 AM           60
11/1/2012   C2   7:30:00 AM-8:00:00 AM           60
11/1/2012   C2   8:00:00 AM-8:30:00 AM           1
11/2/2012   C1   7:30:00 AM-8:00:00 AM           25
11/2/2012   C1   8:00:00 AM-8:30:00 AM           1

不考虑 Emp 字段，我想按日期分组，并根据每次 30 分钟内花费的总时间进行编码。 我的限制是使用 select 语句实现此目的，即只能通过 SQL 查询，因为 PL/SQL 是不允许的。提前致谢!

最佳答案

涉及模型条款的解决方案。

首先让我们计算每个条目所需的 30 分钟 block 的数量。

SQL> select emp, start_time, end_time, code,
  2                      trunc(start_time, 'mi')
  3                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
  4                      ceil(2*24*(end_time-(trunc(start_time, 'mi')
  5                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
  6                 from tab f
  7  /

EM START_TIME             END_TIME               CO START_BLOCK                BLOCKS
-- ---------------------- ---------------------- -- ---------------------- ----------
E1 11/01/2012 06:55:00 am 11/01/2012 07:01:00 am C1 11/01/2012 06:30:00 am          2
E1 11/01/2012 06:57:00 am 11/01/2012 08:01:00 am C2 11/01/2012 06:30:00 am          4
E2 11/01/2012 06:57:00 am 11/01/2012 08:00:00 am C2 11/01/2012 06:30:00 am          3
E1 11/02/2012 07:35:00 am 11/02/2012 08:01:00 am C1 11/02/2012 07:30:00 am          2

现在，我们使用模型子句生成行以将其分成 30 分钟的时间段。

SQL> with foo as (select rownum id, emp, start_time, end_time, code,
  2                      trunc(start_time, 'mi')
  3                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
  4                      ceil(2*24*(end_time-(trunc(start_time, 'mi')
  5                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
  6                 from tab f)
  7  select trunc(start_time) thedate, code, emp, range, minutes
  8    from foo
  9   model partition by(id)
 10         dimension by(0 as f)
 11         measures(code, emp, start_time, end_time, start_block, blocks,
 12                  sysdate as start_range,
 13                  sysdate as end_range,
 14                  cast(0 as number) minutes,
 15                  cast('' as varchar2(50)) range)
 16         rules (start_range [for f from 0 to blocks[0]-1  increment 1]  = start_block[0] + (30*cv(f)/1440),
 17                end_range[any]  =  start_range[cv()] + (30/1440),
 18                code[any]  =  code[0],
 19                emp[any]   =  emp[0],
 20                start_time[any]  =  start_time[0],
 21                end_time[any]  =  end_time[0],
 22                range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),
 23                minutes [any]   = case
 24                                    when start_time[0] between start_range[cv()] and end_range[cv()]
 25                                    then 1440 *(end_range[cv()] - start_time[0])
 26                                    when end_time[0] between start_range[cv()] and end_range[cv()]
 27                                    then 1440 *(end_time[0] - start_range[cv()])
 28                                    else 1440 * (end_range[cv()] - start_range[cv()])
 29                                  end );

CO EM RANGE                                                 MINUTES
-- -- -------------------------------------------------- ----------
C2 E2 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am             3
C2 E2 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am            30
C2 E2 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am            30
C1 E1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am             5
C1 E1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am             1
C1 E1 11/02/2012 07:30:00 am - 11/02/2012 08:00:00 am            25
C1 E1 11/02/2012 08:00:00 am - 11/02/2012 08:30:00 am             1
C2 E1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am             3
C2 E1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am            30
C2 E1 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am            30
C2 E1 11/01/2012 08:00:00 am - 11/01/2012 08:30:00 am             1

11 rows selected.

所以我们按以下方式划分:

partition by(id)

即通过唯一引用。然后我们将生成具有我们维度的行

dimension by(0 as f)

结合部分规则:

for f from 0 to blocks[0]-1  increment 1

所以 start_range 列是用start_range [for f from 0 to blocks[0]-1 increment 1] = start_block[0] + (30*cv(f)/1440),

start_block[0] 在第一个查询中，例如:

EM START_TIME             END_TIME               CO START_BLOCK                BLOCKS
-- ---------------------- ---------------------- -- ---------------------- ----------
E1 11/01/2012 06:55:00 am 11/01/2012 07:01:00 am C1 11/01/2012 06:30:00 am          2

所以对于这一行，它的计算结果为

start_range[0 to 1] = 11/01/2012 06:30:00 am + (30minutes * the value of f)

即

start_range[0] = 11/01/2012 06:30:00 am + (30min*0) = 11/01/2012 06:30:00 am
start_range[1] = 11/01/2012 06:30:00 am + (30min*1) = 11/01/2012 07:00:00 am

剩下的就很简单了:

end_range[any]  =  start_range[cv()] + (30/1440),

表示对于当前行的 end-range，我们采用 start_range 并添加 30 分钟。

range 列是 start_range 和 end_range 的串联:

range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),

最后为了计算该范围内的分钟数:

minutes [any]   = case
                    when start_time[0] between start_range[cv()] and end_range[cv()]
                    then 1440 *(end_range[cv()] - start_time[0])
                    when end_time[0] between start_range[cv()] and end_range[cv()]
                    then 1440 *(end_time[0] - start_range[cv()])
                    else 1440 * (end_range[cv()] - start_range[cv()])
                  end );

如果 start_time 位于范围内，则取范围的末尾 - 开始时间
如果 end_time 位于范围内，则取 end_time - 范围的开始
否则为 end_range - start_range。

1440只是以分钟的形式得到答案。

现在我们可以将它们全部分组:

SQL> with foo as (select rownum id, emp, start_time, end_time, code,
  2                      trunc(start_time, 'mi')
  3                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440) start_block,
  4                      ceil(2*24*(end_time-(trunc(start_time, 'mi')
  5                      - (mod(to_char(trunc(start_time, 'mi'), 'mi'), 30) / 1440)))) blocks
  6                 from tab f)
  7  select thedate, code, range, sum(minutes) minutes
  8    from (select trunc(start_time) thedate, code, emp, range, minutes
  9            from foo
 10           model partition by(id)
 11                 dimension by(0 as f)
 12                 measures(code, emp, start_time, end_time, start_block, blocks,
 13                          sysdate as start_range,
 14                          sysdate as end_range,
 15                          cast(0 as number) minutes,
 16                          cast('' as varchar2(50)) range)
 17                 rules (start_range [for f from 0 to blocks[0]-1  increment 1]  = start_block[0] + (30*cv(f)/1440),
 18                        code[any]  =  code[0],
 19                        emp[any]  =  emp[0],
 20                        end_range[any]  =  start_range[cv()] + (30/1440),
 21                        start_time[any]  =  start_time[0],
 22                        end_time[any]  =  end_time[0],
 23                        range [any] = to_char(start_range[cv()], 'dd/mm/yyyy hh:mi:ss am') || ' - ' || to_char(end_range[cv()], 'dd/mm/yyyy hh24:mi:ss am'),
 24                        minutes [any]   = case
 25                                            when start_time[0] between start_range[cv()] and end_range[cv()]
 26                                            then 1440 *(end_range[cv()] - start_time[0])
 27                                            when end_time[0] between start_range[cv()] and end_range[cv()]
 28                                            then 1440 *(end_time[0] - start_range[cv()])
 29                                            else 1440 * (end_range[cv()] - start_range[cv()])
 30                                          end ))
 31   group by thedate, code, range
 32   order by thedate, code, range;

THEDATE    CO RANGE                                                 MINUTES
---------- -- -------------------------------------------------- ----------
11/01/2012 C1 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am             5
11/01/2012 C1 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am             1
11/01/2012 C2 11/01/2012 06:30:00 am - 11/01/2012 07:00:00 am             6
11/01/2012 C2 11/01/2012 07:00:00 am - 11/01/2012 07:30:00 am            60
11/01/2012 C2 11/01/2012 07:30:00 am - 11/01/2012 08:00:00 am            60
11/01/2012 C2 11/01/2012 08:00:00 am - 11/01/2012 08:30:00 am             1
11/02/2012 C1 11/02/2012 07:30:00 am - 11/02/2012 08:00:00 am            25
11/02/2012 C1 11/02/2012 08:00:00 am - 11/02/2012 08:30:00 am             1

关于sql - 按小时分组的开始时间和结束时间的差异，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14196688/

文章推荐： pcre - 在 AIX 6.1 64 位上编译 pcre 8.32

文章推荐： java - 关于Cucumber框架

文章推荐： java - 根据真/假条件从 URL 绘制位图

文章推荐： vb.net - 在线程运行时使用定时器

CUDA替代__syncthreads而不是__threadfence()差异
我从NVIDIA手册Eg中复制了以下代码:__threadfence()。他们为什么有在以下代码中使用了__threadfence()。我认为使用__syncthreads()而不是__thread
带有修订范围和更改列表的 SVN 差异
我在使用 SVN 更改列表和 svn diff 时遇到了一些麻烦.特别是我想获取特定修订范围的特定文件列表的更改历史记录。 SVN 变更列表似乎是完美的解决方案，所以我的方法是: svn change
shell - 差异/合并两个文件
我有两个 IP 地址列表。我需要将它们合并到三个文件中，交集，仅来自 list1 的文件和仅来自 list2 的文件。我可以用 awk/diff 或任何其他简单的 unix 命令来做到这一点吗？如何
上一个和新工作副本之间的 svn 差异
假设自上次更新(恢复)到我的 a.b 文件以来我做了一些更改。此 a.b 文件也在存储库中更改。现在我想将我所做的更改与 repos 更改进行比较。如果我 svn revert 文件，我可以看到
JavaBeans 比较器/差异
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。我们不允许提问寻求书籍、工具、软件库等的推荐。您可以编辑问题，以便用事实和引用来回答。关闭 7 年前。
openssl sha256 差异
我使用的是 openssl 1.0.1c , linux x86_64 我正在创建包含“hello”的文件(没有换行符) openssl dgst -sha256 hello_file i get :
naming - 共同与核心 - 差异
假设我们有几个库。有什么区别核心和普通图书馆？他们应该如何被认可，我们是否组织了两者的职责？ +Common -Class1 +Core -Class2 +Lib1 has : Comm
以毫秒为单位的日期之间的 SQLite 差异
如何在 SQLite 中计算以毫秒为单位的最小时间间隔？好的，提供一些背景信息，这是我的 table 的样子: link_budget table 所以有这个时间列，我想发出一个请求，以毫秒为单位
concurrency - 乐观与多版本并发控制 - 差异？
我想知道，乐观并发控制 (OCC) 和多版本并发控制 (MVCC) 之间的区别是什么？到目前为止，我知道两者都是基于更新的版本检查。在 OCC 中，我读到了没有获取读取访问锁的事务，仅适用于以后的
c# - SignalR 差异
说到 SignalR，我有点菜鸟。刚刚开始四处探索和谷歌搜索它，我想知道是否有人可以向我解释完成的事情之间的一些差异。在我见过的一些示例中，人们需要创建一个 Startup 类并定义 app.Map
math - 两个四元数之间的“差异”
我在 Ogre 工作，但这是一个一般的四元数问题。我有一个对象，我最初对其应用旋转四元数 Q1。后来，我想让它看起来好像我最初通过不同的四元数 Q2 旋转了对象。我如何计算四元数，该四元数将采用已
Javascript 模块模式 - 差异
我了解 javascript 模块模式，但我使用两种类型的模块模式，并且想从架构 Angular 了解它们之间的区别。 // PATTERN ONE var module = (function()
Scala JSON 差异
我有两个具有完全相同键的 JSON。 val json1 = """{ 'name': 'Henry', 'age' : 26, 'activities' : {
vba - 文件复制与名称函数？差异？
我发现使用 VBA 在 Excel 中复制单个文件有两种不同的方法。一是文件复制: FileCopy (originalPath), (pathToCopyTo) 另一个是名称: Name (orig
java - float[] 差异
我想知道查找两个 float 组之间差异的绝对值的最有效方法是什么？是否是以下内容: private float absDifference(float[] vector1, float[] vec
Wicket:getApplication 差异
我有一个关于 wicket getApplication 的问题。 getApplication() 和 getSession().getApplication 有什么区别？部署 wicket 应用
使用和不使用追溯模式的持久订阅之间的 activemq 差异
我刚刚开始使用activemq，我有一个关于追溯消费者的问题，为了启用这个功能，你需要有一个持久的订阅。但是在主题上启用和不启用追溯的持久订阅有什么区别？ activemq 文档说。 http://a
Scala JSON 差异
我有两个具有完全相同键的 JSON。 val json1 = """{ 'name': 'Henry', 'age' : 26, 'activities' : {
types - 浮点和整数的Erlang二进制表示，差异？
得到另一个 Erlang 二进制表示查询('因为这就是我最近正在阅读的内容，并且需要二进制协议(protocol)实现)。如果我正确理解了类型说明符，那么对于“浮点”类型值，8 字节表示似乎很好(这
java - 重载和隐藏 - 差异
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 4 年前。 Improve this ques

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 按小时分组的开始时间和结束时间的差异