19赞

分析查询

作者：惬听风吟jyy_802 | 2023-08-31 13:55

如何解决《分析查询》经验，为你挑选了1个好方法。

我正在寻找可以转换表中的以下信息的单个查询

name:time    :state
a   :10:00 AM:login
b   :10:05 AM:login
a   :10:06 AM:chatting
a   :10:08 AM:Idle
b   :10:11 AM:chatting
a   :10:10 AM:Logout
b   :10:12 AM:Logout

这样的事情(给定时间范围从上午10点到上午10:15作为查询期)

name: State    :Duration
a   : chatting :2 Minutes 
a   : Idle     :2 Minutes
b   : chatting :1 Minute

这可以仅使用SQL来完成吗？我使用Informix版本11.5

1> Jonathan Lef..：

它可以在单个SQL语句中完成.这是证明.

建立

CREATE TEMP TABLE eventtable
(
    name CHAR(3) NOT NULL,
    time DATETIME HOUR TO MINUTE NOT NULL,
    state CHAR(8) NOT NULL
);

INSERT INTO eventtable(name, time, state) VALUES('a', '10:00', 'login');
INSERT INTO eventtable(name, time, state) VALUES('b', '10:05', 'login');
INSERT INTO eventtable(name, time, state) VALUES('a', '10:06', 'chatting');
INSERT INTO eventtable(name, time, state) VALUES('a', '10:08', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('b', '10:11', 'chatting');
INSERT INTO eventtable(name, time, state) VALUES('a', '10:10', 'Logout');
INSERT INTO eventtable(name, time, state) VALUES('b', '10:12', 'Logout');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:01', 'login');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:02', 'chatting');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:03', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:04', 'Logout');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:05', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:06', 'Logout');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:07', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:08', 'Logout');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:09', 'login');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:11', 'chatting');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:12', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:13', 'chatting');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:14', 'Idle');
INSERT INTO eventtable(name, time, state) VALUES('c', '10:15', 'Logout');

正确的查询

注意条件.结果表必须排除"登录"和第一个其他事件之间的时间段; 此外,它必须排除"注销"和下一个事件之间的时间段(可能是"登录").name列上的表与时间列上的非对称连接之间的自连接(使用' <')可确保事件按时间顺序排列.NOT EXISTS子选择确保仅考虑相邻事件.在子查询中使用BETWEEN AND是一个错误,因为它包含了它的终点,r1.time并且r2.time从范围中排除它是至关重要的; 我花了几分钟发现了这个错误(查询运行但没有返回任何行,但为什么？)!

SELECT r1.name, r1.state, r2.TIME - r1.TIME AS duration
    FROM eventtable r1, eventtable r2
    WHERE r1.name = r2.name
      AND r1.time < r2.time
      AND r1.state != 'login'
      AND r1.state != 'Logout'
      AND r1.time BETWEEN DATETIME(10:00) HOUR TO MINUTE
                      AND DATETIME(10:15) HOUR TO MINUTE
      AND r2.time BETWEEN DATETIME(10:00) HOUR TO MINUTE
                      AND DATETIME(10:15) HOUR TO MINUTE
      AND NOT EXISTS (SELECT 1 FROM eventtable r3
                            WHERE r3.time > r1.time AND r3.time < r2.time
                      AND r3.name = r1.name
                      AND r3.name = r2.name);

这就产生了答案:

name state      duration
a    chatting   0:02
a    Idle       0:02
b    chatting   0:01

c    chatting   0:01
c    Idle       0:01
c    Idle       0:01
c    Idle       0:01
c    chatting   0:01
c    Idle       0:01
c    chatting   0:01
c    Idle       0:01

"持续时间"值是一个间隔小时的分钟; 如果你想在几分钟内得到一个值,你必须用一个强制转换它(使用4表示精度,允许间隔最多1440分钟,或1天;数据在较长的时间范围内是不明确的):

(r2.time - r1.time)::INTERVAL MINUTE(4) TO MINUTE

要么:

CAST (r2.time - r1.time AS INTERVAL MINUTE(4) TO MINUTE)

IBM Informix Dynamic Server(IDS)对时间常量有非常详细的表示法.在标准SQL中,您可以使用TIME作为类型,使用TIME '10:00:00'作为值,但在严格的标准SQL中需要秒.IDS确实提供了人们想要的确切类型 - 例如DATETIME HOUR TO MINUTE.您还可以在标准SQL中编写INTERVAL MINUTE(4); 'TO MINUTE'应该是可选的.

查询不正确

在我对Ray Hidayat的回答的评论中,我指出EXISTS子查询对于确保所考虑的事件是连续的是必要的 - 没有干预事件.这是相同的查询,其中包含添加到输出的开始和结束时间,并且缺少EXISTS子句(并且'duration'重命名为'lapse'):

SELECT r1.name, r1.state, r2.TIME - r1.TIME AS lapse,
       r1.time AS start, r2.time AS end
    FROM eventtable r1, eventtable r2
    WHERE r1.name = r2.name
      AND r1.time < r2.time
      AND r1.state != 'login'
      AND r1.state != 'Logout'
      AND r1.time BETWEEN DATETIME(10:00) HOUR TO MINUTE
                      AND DATETIME(10:15) HOUR TO MINUTE
      AND r2.time BETWEEN DATETIME(10:00) HOUR TO MINUTE
                      AND DATETIME(10:15) HOUR TO MINUTE;

这就产生了答案:

name state     lapse start end
a    chatting   0:04 10:06 10:10
a    chatting   0:02 10:06 10:08
a    Idle       0:02 10:08 10:10
b    chatting   0:01 10:11 10:12
c    chatting   0:13 10:02 10:15
c    chatting   0:12 10:02 10:14
c    chatting   0:11 10:02 10:13
c    chatting   0:10 10:02 10:12
c    chatting   0:09 10:02 10:11
c    chatting   0:07 10:02 10:09
c    chatting   0:06 10:02 10:08
c    chatting   0:05 10:02 10:07
c    chatting   0:04 10:02 10:06
c    chatting   0:03 10:02 10:05
c    chatting   0:02 10:02 10:04
c    chatting   0:01 10:02 10:03
c    Idle       0:12 10:03 10:15
c    Idle       0:11 10:03 10:14
c    Idle       0:10 10:03 10:13
c    Idle       0:09 10:03 10:12
c    Idle       0:08 10:03 10:11
c    Idle       0:06 10:03 10:09
c    Idle       0:05 10:03 10:08
c    Idle       0:04 10:03 10:07
c    Idle       0:03 10:03 10:06
c    Idle       0:02 10:03 10:05
c    Idle       0:01 10:03 10:04
c    Idle       0:10 10:05 10:15
c    Idle       0:09 10:05 10:14
c    Idle       0:08 10:05 10:13
c    Idle       0:07 10:05 10:12
c    Idle       0:06 10:05 10:11
c    Idle       0:04 10:05 10:09
c    Idle       0:03 10:05 10:08
c    Idle       0:02 10:05 10:07
c    Idle       0:01 10:05 10:06
c    Idle       0:08 10:07 10:15
c    Idle       0:07 10:07 10:14
c    Idle       0:06 10:07 10:13
c    Idle       0:05 10:07 10:12
c    Idle       0:04 10:07 10:11
c    Idle       0:02 10:07 10:09
c    Idle       0:01 10:07 10:08
c    chatting   0:04 10:11 10:15
c    chatting   0:03 10:11 10:14
c    chatting   0:02 10:11 10:13
c    chatting   0:01 10:11 10:12
c    Idle       0:03 10:12 10:15
c    Idle       0:02 10:12 10:14
c    Idle       0:01 10:12 10:13
c    chatting   0:02 10:13 10:15
c    chatting   0:01 10:13 10:14
c    Idle       0:01 10:14 10:15

这显示了用户'c'的每个符合条件的起始行如何与每个符合条件的结束行匹配,从而产生许多虚假的数据行.在处理基于时间的查询时,NOT EXISTS子查询是一个常见主题.您可以在Snodgrass的" 在SQL中开发面向时间的应用程序 "(PDF在线提供的PDF)以及Date,Darwen和Lorentzos的"时态数据和关系模型 "中找到有关这些操作的信息.

推荐阅读

程序员
Raspberry Pi上无法运行Qt5视频播放器示例

如何解决《RaspberryPi上无法运行Qt5视频播放器示例》经验，为你挑选了1个好方法。 ... [详细]
程序员
在iPad Pro中,启动屏幕故事板显示在模拟器中但不显示设备

如何解决《在iPadPro中,启动屏幕故事板显示在模拟器中但不显示设备》经验，为你挑选了0个好方法。 ... [详细]
程序员
无法使用EC2上的机器IP地址打开spinnaker deck UI

如何解决《无法使用EC2上的机器IP地址打开spinnakerdeckUI》经验，为你挑选了1个好方法。 ... [详细]
程序员
带有退货声明的短路

如何解决《带有退货声明的短路》经验，为你挑选了1个好方法。 ... [详细]
程序员
是否可以检测用户是否安装并使用了Google健身应用？

如何解决《是否可以检测用户是否安装并使用了Google健身应用？》经验，为你挑选了0个好方法。 ... [详细]
程序员
Laravel 5注销或会话破坏

如何解决《Laravel5注销或会话破坏》经验，为你挑选了1个好方法。 ... [详细]
程序员
多处理队列子类问题

如何解决《多处理队列子类问题》经验，为你挑选了1个好方法。 ... [详细]
程序员
TLS握手错误

如何解决《TLS握手错误》经验，为你挑选了0个好方法。 ... [详细]
程序员
Android自定义属性未显示

如何解决《Android自定义属性未显示》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何在CakePHP 3中的model-> save()上获取SQL查询？

如何解决《如何在CakePHP3中的model->save()上获取SQL查询？》经验，为你挑选了0个好方法。 ... [详细]
程序员
允许管理员用户查看其他用户类型可以查看/执行的操作？

如何解决《允许管理员用户查看其他用户类型可以查看/执行的操作？》经验，为你挑选了1个好方法。 ... [详细]
程序员
将对象列表保存到文本文件

如何解决《将对象列表保存到文本文件》经验，为你挑选了1个好方法。 ... [详细]
程序员
GNU malloc_info():获得真正分配的内存？

如何解决《GNUmalloc_info():获得真正分配的内存？》经验，为你挑选了0个好方法。 ... [详细]
程序员
在正则表达式拆分期间发生javascript未定义元素

如何解决《在正则表达式拆分期间发生javascript未定义元素》经验，为你挑选了1个好方法。 ... [详细]
程序员
从数组中选择奇数或偶数元素

如何解决《从数组中选择奇数或偶数元素》经验，为你挑选了1个好方法。 ... [详细]
程序员
缺少Type.GetMember和MemberInfo.GetCustomAttributes（C＃PCL .NET 4.6）

如何解决《缺少Type.GetMember和MemberInfo.GetCustomAttributes（C＃PCL.NET4.6）》经验，为你挑选了1个好方法。 ... [详细]
程序员
转储Postgres DB，时间和.sql文件的权重

如何解决《转储PostgresDB，时间和.sql文件的权重》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何直接写入模仿scalar_summary的摘要？

如何解决《如何直接写入模仿scalar_summary的摘要？》经验，为你挑选了2个好方法。 ... [详细]
程序员
更改左侧的ToolBar默认图标

如何解决《更改左侧的ToolBar默认图标》经验，为你挑选了1个好方法。 ... [详细]
程序员
BufferedWriter类的writeLine方法

如何解决《BufferedWriter类的writeLine方法》经验，为你挑选了1个好方法。 ... [详细]

惬听风吟jyy_802

这个屌丝很懒，什么也没留下！

关注作者

Tags | 热门标签

RankList | 热门文章