临时谓词 - Amazon Kinesis Data Analytics
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

临时谓词

下表显示了标准 SQL 支持的临时谓词和 Amazon Kinesis Data Analytics 支持的 SQL 标准的扩展的图形表示形式。它显示了每个谓词涵盖的关系。每个关系均表示为时间间隔上限和下限,并具有组合意义 upperInterval predicate lowerInterval evaluates to TRUE。前 7 个谓词是标准 SQL。后 10 个谓词 (以粗体字显示) 是 SQL 标准的 Amazon Kinesis Data Analytics 扩展。

谓词 涵盖的关系

CONTAINS

OVERLAPS

EQUALS

PRECEDES

SUCCEEDS

IMMEDIATELY PRECEDES

IMMEDIATELY SUCCEEDS

LEADS

LAGS

STRICTLY CONTAINS

STRICTLY OVERLAPS

STRICTLY PRECEDES

STRICTLY SUCCEEDS

STRICTLY LEADS

STRICTLY LAGS

IMMEDIATELY LEADS

IMMEDIATELY LAGS

为启用简明表达式,Amazon Kinesis Data Analytics 还支持以下扩展:

  • 可选 PERIOD 关键字 – 可忽略 PERIOD 关键字。

  • 紧凑链接 – 如果这些谓词中有两个背靠背出现并由 AND 分隔,则可以忽略 AND,前提是第一个谓词的右间隔与第二个谓词的左间隔相同。

  • TSDIFF – 此函数将选取两个 TIMESTAMP 参数并返回两者之差(以毫秒为单位)。

例如,您可以编写以下表达式:

 PERIOD (s1,e1) PRECEDES PERIOD(s2,e2)  AND PERIOD(s2, e2) PRECEDES PERIOD(s3,e3)

更简明的形式如下:

(s1,e1) PRECEDES (s2,e2) PRECEDES PERIOD(s3,e3)

以下简明表达式:

TSDIFF(s,e)

表示以下内容:

CAST((e - s) SECOND(10, 3) * 1000 AS BIGINT)

最后,标准 SQL 允许 CONTAINS 谓词选取一个 TIMESTAMP 作为其右侧参数。例如,以下表达式:

PERIOD(s, e) CONTAINS t

等效于以下内容:

s <= t AND t < e

语法

临时谓词将集成到新 BOOLEAN 值表达式中:

<period-expression> :=  <left-period> <half-period-predicate> <right-period> <half-period-predicate> :=  <period-predicate> [ <left-period> <half-period-predicate> ] <period-predicate> :=   EQUALS | [ STRICTLY ] CONTAINS | [ STRICTLY ] OVERLAPS | [ STRICTLY | IMMEDIATELY ] PRECEDES | [ STRICTLY | IMMEDIATELY ] SUCCEEDS | [ STRICTLY | IMMEDIATELY ] LEADS | [ STRICTLY | IMMEDIATELY ] LAGS <left-period> := <bounded-period> <right-period> := <bounded-period> | <timestamp-expression> <bounded-period> := [ PERIOD ] ( <start-time>, <end-time> ) <start-time> := <timestamp-expression> <end-time> := <timestamp-expression> <timestamp-expression> :=  an expression which evaluates to a TIMESTAMP value where <right-period> may evaluate to a <timestamp-expression> only if the immediately preceding <period-predicate> is [ STRICTLY ] CONTAINS

此 Boolean 表达式受以下内置函数支持:

BIGINT tsdiff( startTime TIMESTAMP, endTime TIMESTAMP )

返回 (endTime - startTime) 的值 (以毫秒为单位)。

示例

以下示例代码记录了在空调开启时窗户打开的警报:

create or replace pump alarmPump stopped as  insert into alarmStream( houseID, roomID, alarmTime, alarmMessage ) select stream w.houseID, w.roomID, current_timestamp,                   'Window open while air conditioner is on.' from    windowIsOpenEvents over (range interval '1' minute preceding) w join    acIsOnEvents over (range interval '1' minute preceding) h on w.houseID = h.houseID where (h.startTime, h.endTime) overlaps (w.startTime, w.endTime);

示例使用案例

以下查询使用临时谓词以在两个人试图在两个不同的位置同时使用相同的信用卡时发出欺诈警报:

create pump creditCardFraudPump stopped as insert into alarmStream  select stream    current_timestamp, creditCardNumber, registerID1, registerID2  from transactionsPerCreditCard  where registerID1 <> registerID2  and (startTime1, endTime1) overlaps (startTime2, endTime2) ;

前面的代码示例使用具有以下数据集的输入流:

(current_timestamp TIMESTAMP, creditCardNumber VARCHAR(16), registerID1 VARCHAR(16), registerID2 VARCHAR(16), startTime1 TIMESTAMP, endTime1 TIMESTAMP, startTime2 TIMESTAMP, endTime2 TIMESTAMP)