本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。
# 时间谓词
下表以图形方式显示了标准 SQL 支持的时间谓词以及对 Amazon Kinesis Data Analytics 支持的 SQL 标准的扩展。它显示了每个谓词所涵盖的关系。每个关系均表示为时间间隔上限和下限，并具有组合意义 *upperInterval predicate lowerInterval evaluates to TRUE*。前 7 个谓词是标准 SQL。最后 10 个谓词（以粗体显示）是 Amazon Kinesis Data Analytics 对 SQL 标准的扩展。
| 谓词 | 涵盖的关系 |
| --- | --- |
| CONTAINS | ![\[Blue rectangular shapes arranged in horizontal rows, resembling a simplified layout or structure.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_contains.png) |
| OVERLAPS | ![\[Blue rectangular boxes arranged in rows, representing a structured layout or diagram.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_overlaps.png) |
| EQUALS | ![\[Two horizontal blue rectangles with orange borders, stacked vertically.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_equals.png) |
| PRECEDES | ![\[Four blue rectangular shapes representing placeholder text or content blocks.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_precedes.png) |
| SUCCEEDS | ![\[Four blue rectangular shapes arranged horizontally with gaps between them.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_succeeds.png) |
| IMMEDIATELY PRECEDES | ![\[Blue rectangular shapes representing text or content placeholders.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_precedes.png) |
| IMMEDIATELY SUCCEEDS | ![\[Two horizontal blue rectangular shapes against a white background.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_succeeds.png) |
| **LEADS** | ![\[Four blue rectangular buttons with orange outlines, arranged horizontally.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_leads.png) |
| **LAGS** | ![\[Four blue rectangular bars of varying lengths arranged horizontally.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_lags.png) |
| **STRICTLY CONTAINS** | ![\[Two blue rectangular shapes with orange outlines, one larger above a smaller one.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_contains.png) |
| **STRICTLY OVERLAPS** | ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_overlaps.png) |
| **STRICTLY PRECEDES** | ![\[Two horizontal blue bars representing placeholder elements in a user interface.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_precedes.png) |
| **STRICTLY SUCCEEDS** | ![\[Two blue rectangular shapes representing UI elements or buttons.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_succeeds.png) |
| **STRICTLY LEADS** | ![\[Two blue rectangular shapes representing text or content blocks.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_leads.png) |
| **STRICTLY LAGS** | ![\[Two horizontal blue rectangular shapes, one above the other, against a white background.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_strictly_lags.png) |
| **IMMEDIATELY LEADS** | ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_leads.png) |
| **IMMEDIATELY LAGS** | ![\[Two blue rectangular shapes, one longer than the other, stacked vertically.\]](http://docs.aws.amazon.com/zh_cn/kinesisanalytics/latest/sqlref/images/sqlrf_immediately_lags.png) |
为了实现简洁的表达式，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 的新表达式中：
```
:=
:=
[ ]
:=
EQUALS
| [ STRICTLY ] CONTAINS
| [ STRICTLY ] OVERLAPS
| [ STRICTLY | IMMEDIATELY ] PRECEDES
| [ STRICTLY | IMMEDIATELY ] SUCCEEDS
| [ STRICTLY | IMMEDIATELY ] LEADS
| [ STRICTLY | IMMEDIATELY ] LAGS
:=
:= |
:= [ PERIOD ] ( , )
:=
:=
:=
an expression which evaluates to a TIMESTAMP value
where may evaluate to a only if
the immediately preceding is [ STRICTLY ] CONTAINS
```
以下内置函数支持此布尔表达式：
```
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)
```