判斷一個表項的時間范圍是否與另一個KDB /Q重疊

我有一個如下表：

table:([] RIC:`A.N`A.N`A.N`GOOG.O`GOOG.O; 
startRange:2022.01.03D09:31:54.000000000 2022.01.03D09:32:04.000000000 2022.01.03D09:31:54.100000000 2022.01.03D09:31:54.000000000 2022.01.03D09:31:54.100000000;
endRange:2022.01.03D09:31:59.000000000 2022.01.03D09:32:09.000000000 2022.01.03D09:31:59.100000000 2022.01.03D09:31:59.000000000 2022.01.03D09:31:59.100000000)

我想添加一個“重疊”列，它是一個布爾標志，只要一個條目有另一個與其時間范圍重疊的條目（具有相同的相應 RIC），它就等于 1。因此，對于我上面的表格，應該標記第一個和第三個條目，因為它們都用于 `AN 并且具有重疊的日期時間范圍。應該標記第四個和第五個條目，因為它們也具有相同的 RIC 和重疊的日期時間范圍。

老實說，我根本不知道如何處理這個問題。任何建議將不勝感激！

uj5u.com熱心網友回復：

這里有點冗長的解決方案，但我認為這涵蓋了您的用例：

q)raze{update overlap:{any(x within'z)|y within'z}'[startRange;endRange]{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
RIC    startRange                    endRange                      overlap
--------------------------------------------------------------------------
A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000 1
A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000 0
A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000 1
GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000 1
GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000 1

為了打破這個答案，首先我們需要時間范圍來檢查重疊。我們從給定 RIC 的所有時間范圍開始：

q)`overlap xcols raze{update overlap:count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap                                                                                                                                                                          ..
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000)                                                        ..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000)                                                        ..

我們要排除我們正在處理的條目的時間范圍：

q)`overlap xcols raze{update overlap:{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap                                                                                                                   RIC    startRange                    endRange          ..
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------..
(2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000) A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:5..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000) A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:0..
(2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000;2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000) A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:5..
,2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000                                                              GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:5..
,2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000                                                              GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:5..

最后看看是否startRange或endRange在這些時間范圍內：

q)`overlap xcols raze{update overlap:{any(x within'z)|y within'z}'[startRange;endRange]{x where y<>til count x}'[;i]count[i]#enlist flip(startRange;endRange)from x}each{select from table where RIC=x}each`A.N`GOOG.O
overlap RIC    startRange                    endRange
--------------------------------------------------------------------------
1       A.N    2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000
0       A.N    2022.01.03D09:32:04.000000000 2022.01.03D09:32:09.000000000
1       A.N    2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000
1       GOOG.O 2022.01.03D09:31:54.000000000 2022.01.03D09:31:59.000000000
1       GOOG.O 2022.01.03D09:31:54.100000000 2022.01.03D09:31:59.100000000

標籤：约会时间 时间 数据库

上一篇：缺少日期的熊貓的每日最大值

下一篇：使用KQL（Kusto查詢語言），如何將日期時間分組為周（或7天的塊）？