更多查询示例
本文档演示了一些典型的查询模式，您可以在Timeplus中实现这些模式来解决各种用例。
创建一个 docker-compose 文件以将Proton图像与数据生成器捆绑在一起。
- 下载 docker-compose.yml 然后放入新文件夹。
- 打开终端并在此文件夹中运行 docker compose up。
- 等待几分钟，提取所有必需的镜像并启动容器。
- 使用 proton-client 运行 SQL 来查询数据：
docker exec-it <folder>-proton-1 proton-1 proton-client你可以通过docker ps获取容器名称。
客户场景和数据模型
你是一家汽车共享公司的首席业务分析师。 每辆车都配备了传感器，用于报告汽车位置。 客户使用移动应用程序查找附近的可用汽车，进行预订，解锁并上路。 在行程结束时，客户将汽车停好，上锁，然后结束行程。 将使用注册的信用卡自动付款。
时间敏感型见解的一些典型用例是：
- 用户在某些地点驾驶了多少辆汽车？ 我们需要将一些汽车从不太繁忙的地方转移到那些热点区域吗？
- 哪些汽车的行驶速度过快或燃料不足？ 服务团队可能需要采取行动。
- 哪些用户继续预订汽车然后取消？ 我们是否应向这些用户发送实时通知以避免滥用。
系统中有多个数据流：
dim_user_info
包含所有注册用户信息的相对静态流。
| 专栏 | 类型 | 示例值 |
|---|---|---|
| uid | 字符串 | u00001 |
| 名字_name | 字符串 | Foo |
| 姓氏 | 字符串 | Bar |
| 电子邮件地址 | 字符串 | a@timeplus.io |
| 信用卡 | 字符串 | 371712345678910 |
| 两性平等 | 字符串 | F |
| 生日 | 字符串 | 1990-01-15 |
dim_car_info
所有注册汽车的相对静态流
| 专栏 | 评论 | 类型 | 示例值 |
|---|---|---|---|
| cid | 汽车身份证 | 字符串 | c00001 |
| 牌照号 | 字符串 | KM235L | |
| 服务中 | 如果汽车处于暂停状态（重试或保养中），则返回 false | 布尔值 | 真的 |
car_live_data
包含来自汽车传感器的最新数据的数据流。 当汽车发动机启动时，每秒报告一次数据。 否则，每半小时报告一次数据。
| 专栏 | 评论 | 类型 | 示例值 |
|---|---|---|---|
| 时间 | 传感器数据的日 期时间 | 日期时间 | 2022-01-12 23:00:58.476 |
| cid | 汽车身份证 | 字符串 | c00001 |
| 经度 | 当前职位 | 浮点数 | 40.75896 |
| 纬度 | 当前职位 | 浮点数 | -73.985195 |
| gas_percent | 气体液位百分比，100 表示油箱已满 | 小数 | 86.12 |
| speed_kmh | 当前行驶速度（以 km/h 为单位） | 整数 | 56 |
| total_km | 这辆车的总距离（以千米为单位）。 旅行后继续增加 | 浮点数 | 3536 |
| 锁定 | 汽车是否上锁 | 布尔值 | 真的 |
| 使用中 | 是否有人在用车 | 布尔值 | 真的 |
预订
包含行程详细信息和付款信息的数据流。 每行都是在预订生命周期内生成的
- 当用户预订汽车时，将出现一个带有 action=add 的新活动，booking_time=now，expire=now+30m
- 当用户解锁汽车时，将出现一个带有 action=service 的新事件
- 当用户完成行程并锁定汽车时，将出现一个名为 action=end 的新事件
- 当用户取消预订时，将出现一个带有 action=cancel 的新活动
- 当用户将预订再延长 30 分钟时，一个带有 action=extend 的新活动，并更新到期字段
- 如果用户未在到期时间之前解锁汽车，则会使用 action=expire 添加新事件
| 专栏 | 评论 | 类型 | 示例值 |
|---|---|---|---|
| 时间 | 当事件发生时 | 日期时间 | 2022-01-12 13:00:58.476 |
| 出价 | 预订编号 | 字符串 | b00001 |
| 预订时间 | 当用户预订汽车时。 30 分钟后过期 | 日期时间 | 2022-01-12 13:30:58.476 |
| uid | 用户 ID | 字符串 | u00001 |
| cid | 汽车身份证 | 字符串 | c00001 |
| 行动 | 其中一个值：添加、取消、延长、服务、过期、结束 | 字符串 | 添加 |
| 过期 | 预订何时到期 | 日期时间 | 2022-01-12 13:30:58.476 |
旅行
包含行程详细信息和付款信息的数据流。 每行都是在行程结束时生成的
| 专栏 | 评论 | 类型 | 示例值 |
|---|---|---|---|
| 潮汐的 | 行程编号 | 字符串 | t00001 |
| start_time | 旅行何时开始 | 日期时间 | 2022-01-12 13:00:58.476 |
| end_time | 当旅程结束时 | 日期时间 | 2022-01-12 24:00:58.476 |
| 出价 | 预订编号 | 字符串 | b00001 |
| start_lon | 起始地点 | 浮点数 | 40.75896 |
| start_lat | 起始地点 | 浮点数 | -73.985195 |
| end_lon | 终点位置 | 浮点数 | 42.75896 |
| end_lat | 终点位置 | 浮点数 | -71.985195 |
| 距离 | 行驶距离（km） | 浮点数 | 23.2 |
| 金额 | 用户应该为旅行支付多少费用 | 小数 | 40.75 |
以下部分展示了如何查询Timeplus以了解业务。
流分析
S-TAIL：显示带或不带过滤条件的原始数据
用例： 为了开始数据探索，分析师希望显示所有最近报告的汽车物联网数据
从 car_live_data 中选择 *
或者关注哪些汽车快要耗尽汽油了（这样他们就可以派出服务小组加油或暂停汽车）
从 car_live_data 中选择时间、cid、gas_percent 其中 gas_percent < 25
结果：
| 时间 | cid | gas_percent | 使用中 |
|---|---|---|---|
| 2022-01-12 23:00:58.476 | c00001 | 18 | false |
S-DOWNSAMPLING：将详细数据点转换为高级数据
用例： 每辆车上的传感器可能会报告从半秒到每 10 秒的数据。 分析师可能会降低粒度，只需要将每分钟的数据保存到下游即可
从
tumble (car_live_data,1m) 选择 window_start、avg (gas_percent) 作为 avg_percent、avg (speed_kmh) 作为 avg_speed 作为 avg_speed（car_live_data,1m）按 window_start 分组，cid
结果：
| window_start | cid | 平均气体百分比 | avg_speed |
|---|---|---|---|
| 2022-01-12 23:01:00.000 | c00001 | 34 | 35 |
More practically, the user can create a materialized view to automatically put downsampled data into a new stream/view.
将物化视图 car_live_data_1min 创建为
选择 window_start 作为时间、cid、avg (gas_percent) 作为 avg_gas、avg (speed_kmh) 作为 avg_speed
FROM tumble (car_live_data,1m) 按 window_start 分组，cid
然后，用户可以通过以下方式搜索数据
选择 * 来自 car_live_data_1min
结果：
| 时间 | cid | avg_gas | avg_speed |
|---|---|---|---|
| 2022-01-12 23:01:00.000 | c00001 | 34 | 35 |
S-AGG-RECENT：显示最近数据的聚合信息
用例： 分析师想要监控过去 1 小时的总收入。
Timeplus 提供了一种特殊的语法来轻松获得这样的结果
选择最近 1 小时发出的行程总额（金额）
提交查询后，它将根据过去一天显示相当多的行，然后以流方式显示新的结果。
结果：
| 总和（金额） |
|---|
| 3500.42 |
还有其他方法可以获得类似的结果，包括更详细的查询
-
我们可以在最近 1 小时的时间段内对数据应用全局聚合。 `从行程中选择总和（金额），其中 end_time > date_sub（now ()，1h）
-
另一种解决方法是使用 hop 窗口聚合。 与 [S-DOWNSAMPLING]（#s-downsampling）中的 “tumble” 窗口类似，数据 是按固定大小的时间窗分组的，例如一个小时。 Tumble 窗口不相互重叠，因此非常适合在不重复数据的情况下进行缩减采样（例如，对于 “count” 聚合，不会对数据进行两次计数）对于 hop 窗口，它将通过滑动步骤向左或向右移动（时间轴中的过去或未来）。 例如，以下查询将使用 hop 窗口来获取过去 1 小时的总收入，结果将每秒发送一次。 `从 hop（行程、结束时间,1秒,1h）中选择 window_start、window_end、总和（金额） 按 window_start、window_end 分组
S-SESSION：分析活跃会话中的活动
用例： 分析师想要追踪汽车的每日行驶情况。 汽车上的传感器在发动机启动时每秒报告一次数据，发动机关闭时每半小时报告一次数据。 如果服务器在 5 秒内没有收到正在运行的汽车的数据，则该汽车被视为已断开连接。 我们可以运行以下查询来显示每辆正在运行的汽车的行程距离
选择 cid、window_start、window_end、max (total_km)-min (total_km) 作为 trip_km
来自会话（car_live_data、时间、5s、cid）
按 __tp_session_id、cid、window_start、window_end
行程大于 0 进行分组
结果：
| cid | window_start | window_end | trip_km |
|---|---|---|---|
| c00040 | 2022-03-23 21:42:08.000 | 2022-03-23 21:42:12.000 | 0.05395412226778262 |
| c00078 | 2022-03-23 21:42:08.000 | 2022-03-23 21:42:33.000 | 0.4258001818272703 |
可以创建更复杂的查询，以按汽车 ID 和行程结束时间聚合数据。
使用 query_1 AS (
选择 cid、window_start AS w_start、window_end AS w_end、max (total_km)-min (total_km) AS trip_km
从会话（cid_live_data、time,20m、cid）分组按 __tp_session_id、cid、cid、cid、cid、cid、cid 进行分组
)
选择 cid、window_start _end，sum (trip_km)
从 tumble (query_1、w_end,1h) 中按 cid、window_start、window_end 分组
结果：
| cid | window_start | window_end | trip_km |
|---|---|---|---|
| c00001 | 2022-01-12 00:00:00.000 | 2022-01-12 23:59:59.999 | 17.2 |
| c00002 | 2022-01-12 00:00:00.000 | 2022-01-12 23:59:59.999 | 4.1 |
此查询是一个持续的流式查询。 每小时（或每天，视tumble窗口大小而定），可以将分析结果发送到电子邮件/slack 或 Kafka 主题以进行进一步处理。
S-TIME-TRAVEL：回到过去，从那以后进行分析
用例： 分析师无需继续关注流图表或仪表板。 从那一刻起，他们可以回到过去的时间来进行流分析。 这可以帮助他们更好地了解几个小时前（例如午夜）发生的事情。
例如，分析师想了解用户在 2 小时前是如何预订汽车的
选择 window_start，从 tumble 中计数 (*) (bookings,15m)
WHERE action='add' 分组 by window_start
发出最近 2 小时的消息
或者他们可以指定确切的时间戳，例如
选择 window_start，从 tumble（bookings,15m）中计数 (*)
其中 action='add' 和 _tp_time>='2022-01-12 06:00:00.000 '按 window_start 分组
结果：
| window_start | 计数 (\ *) |
|---|---|
| 2022-01-12 06:00:00.000 | 34 |
| 2022-01-12 06:15:00.000 | 23 |
不仅会分析过去的数据，而且还将持续处理最新的传入数据。
S-MVIEW：创建物化视图以保留最新分析结果并缓存以供其他系统查询
用例： 与传统 SQL 查询不同，流式查询只有在用户取消后才会结束。 分析结果不断推送到 Web 用户界面或 slack/kafka 目的地。 分析人员希 望在Timeplus中运行高级流式查询，并将结果缓存为物化视图。 这样他们就可以使用常规 SQL 工具/系统以常规表格的形式获得流式见解。 物化视图还可用于对数据进行降采样以减少数据量以供将来分析和存储
将 today_revenue 创建为
从行程中选择总和（金额），其中 end_time > today ()；
--在 Timeplus 或其他连接的 SQL 客户端中
从 today_revenue 中选择 * FROM today_revenue
S-DROP LATE：删除延迟事件以获得实时聚合见解
用例： 由于多种原因，例如网络延迟、物联网传感器故障等，流式传输数据可能会延迟到达。 当我们进行流分析（例如每分钟付款）时，我们会根据他们的事件时间（实际付款的时间，而不是Timeplus收到数据的时间）汇总数据，并且我们不想等待为时已晚的事件。
水印是流处理世界中设定事件延迟时间标准的常用机制。 与其他系统不同，Timeplus无需明确设置水印政策即可轻松识别延迟事件。
对于这样的查询
选择 window_start、window_end、sum（金额）、count (*)
FROM tumble（trips、end_time,1m）按 window_start、window_end 分组
例如，它将显示每分钟的总付款额
| window_start | window_end | 总和（金额） | 计数 (\ *) |
|---|---|---|---|
| 2022-01-12 10:00:00.000 | 2022-01-12 10:01:00.000 | 200 | 42 |
| 2022-01-12 10:01:00.000 | 2022-01-12 10:02:00.000 | 300 | 67 |
考虑到两辆车将在 10:00:10 同时返回。 对于 TripA 和行程，两者都应该在第一个时间窗口内计算。 但是，由于某种原因，数据点TripA于10点01点15分到达Timeplus，而TripB数据点则在10点01点16分到达。 Timeplus将接受TripA数据并将其添加到第一个窗口聚合中，并关闭第一个窗口。 水印将发送到 10:01:00。 因此，当TripB数据点到达时，被认为为时已晚，并且不会在流结果中进行计算。 但是当我们运行历史查询时，它仍然可用。
| 数据点 | 事件时间 Event Time | 到达时间 | 备注 |
|---|---|---|---|
| TripA | 2022-01-12 10:00:10.000 | 2022-01-12 10:01:15.000 | 包含在第一个窗口中，触发水印更改 |
| TripB | 2022-01-12 10:00:10.000 | 2022-01-12 10:01:16.000 | 它的时间低于水印。 第 1 个窗口已关闭（不接受更多数据） 数据已删除以进行流式分析。 仍然可以通过历史搜索进行分析 |
S-WAIT-LATE：等着晚间赛事的加时赛
用例： 要对上述案例进行更高级的用法，分析师可能需要设定额外的时间来等待晚期事件。 这将使流分析不那么实时，但是，如果网络延迟不可预测，则可以包括更多数据点。
在类似的情况下，具有高级设置的查询是
选择 window_start、window_end、sum（金额）、count (*)
FROM tumble（trips、end_time,1m）按 window_start、window_end
在水印后发出，延迟 30 秒
S-TOP-K：获取每个流窗口的最常见值
用例： 分析师想了解每天或每小时最常预订哪些汽车
选择 window_start、top_k (cid,3) 作为 tumble (bookings,1h) 中的 popular_cars by window_start 分组 by window_start
这将生成这样的每日报告
| window_start | popul_cars |
|---|---|
| 2022-01-12 00:00:00.000 | ['car1', 'car2', 'car3'] |
| 2022-01-13 00:00:00.000 | ['car2', 'car3', 'car4'] |
S-MAX-K：获取每个流窗口的最大值
用例： 分析师想了解每天哪些行程最长
选择 window_start、max_k（金额、3、出价、距离）作为 longest_trips from tumble (trips,1d) 按 window_start 分组
这将生成这样的每日报告
| window_start | 最长的行程 |
|---|---|
| 2022-01-12 00:00:00.000 | [(7.62, 'b01',13.8), (2.45, 'b02',2.37), (12.66, 'b03',22.6)] |
要获取第二长行程的预订 ID，你可以 “选择..., longest_trips [2] .2 AS bookingID `
S-MIN-K：获取每个流窗口的最小值
用例： 分析师想了解每天哪些行程最短
选择 window_start、min_k（金额、3、出价、距离）作为 shortest_trips From tumble (trips,1d) 按 window_start 分组
这将生成这样的每日报告
| window_start | 最短的行程 |
|---|---|
| 2022-01-12 00:00:00.000 | [(2.56, 'b06',3.10), (7.68, 'b07',10.8), (10.24, 'b08',15.36)] |
S-OVER-TIME：获取每个时间窗口中结果的差异/差距
**用例：**使用Timeplus，分析师可以轻松地将当前分钟数据与最后一分钟的数据进行比较。
例如，用户想了解每分钟使用了多少辆汽车，以及它与最后一分钟有何不同
选择 window_start，将 (*) 计为 num_of_trips，
延迟 (num_of_trips) 作为 last_min_trips，num_of_trips-last_min_trips 作为间隔
FROM tumble (trips,1m) 按 window_start 分组
结果
| window_start | 行程次数 | 最后一分钟行程 | 间隙 |
|---|---|---|---|
| 2022-01-12 10:00:00.000 | 88 | 0 | 88 |
| 2022-01-12 10:01:00.000 | 80 | 88 | -8 |
| 2022-01-12 10:02:00.000 | 90 | 80 | 10 |
这是一项非常强大和有用的功能。 除了比较上次的汇总结果外，分析师还可以比较过去的数据。 例如，这一秒与最后一分钟或最后一小时的秒数相同。
以下查询比较了每秒汽车传感器数据的数量，比较了过去 m 中的事件数
选择 window_start，计数 (*) AS num_of_events，
lag (window_start,60) AS last_min，
延迟 (num_of_events,60) AS last_min_events，
num_of_events-last_min_events 作为间隔，
concat (to_string (to_decimal (gap*100/num_of_events,2))，'%') AS change
FROM tumble (car_live_data,1s) 按 window_start 分组
查询开始运行后，在前 1 分钟内，只有较新的结果可用。 然后我们可以从 60 个窗口 中得到结果，这样我们就可以比较差异。
结果
| window_start | 事件数目 | 最后_分钟 | 最后一分钟的事件 | 间隙 | 改变 |
|---|---|---|---|---|---|
| 2022-01-12 10:01:00.000 | 88 | 2022-01-12 10:00:00.000 | 83 | 5 | 5 |
| 2022-01-12 10:01:01.000 | 80 | 2022-01-12 10:00:01.000 | 87 | -7 | -8.75% |
S-UNION-STREAMS：将相同架构中的多个流合并为单个流
用例： 某些数据流可能位于相同的数据架构中，但有意放入不同的数据流中，例如一个城市或一个国家的数据流（例如出于性能或监管方面的考虑）。 我们想合并数据以了解大局。
例如，这家汽车共享公司首先在不列颠哥伦比亚省温哥华开展业务。 然后将其扩展到不列颠哥伦比亚省的维多利亚州。 根据当地城市政府的法规要求，建立了两个系统。 总部希望显示这两个城市的流分析。
选择 * 来自 trips_vancouver
UNION
选择 * 来自 trips_victoria
S-JOIN-STREAMS：同时查询多个数据流
用例： 数据不断变化，每种类型的变化数据都是一个流。 同时查询多种数据以丰富数据、获取更多背景信息并了解它们的相关性是一项常见的要求。
例如，我们想了解用户预订汽车和开始行程之间的平均分钟数。 预订 流和 行程 流中的预订信息包含行程开始时间和结束时间
从
中选择平均值（间隔）（选择
date_diff（“秒”，bookings.booking_time，trips.start_time）作为间隔
从预订中选择
内部加入行程 ON（bookings.bid = trips.bid）
和 date_diff_within（2m，bookings.bid_time，trips.start_time）
) 其中 _tp_time >= 现在 () -1d
我们可以为这个演示集运行其他流查询
获取在用车的数量
每辆车都会向 car_live_data 流报告其状态，包括 “in_use” 布尔标志。 对于同一辆车 ID，它可能会在 2 分钟前报告 “in_use=false”，然后在 1 分钟前报告 “in_use=true”。 那么这辆车应该被视为在用。 我们不应该进行全局聚合，因为我们只关心当前状态，而不关心累积的数据（每辆跑车应每秒报告两次数据）。 以 1 秒为窗口大小时，tumble窗口应该没问题。
选择 window_start，从 tumble (car_live_data,1s) 中计数（不同 cid）
WHERE in_use 按 window_start 分组
获取收入排名前 10 的汽车订单
我们可能想了解哪些汽车可以帮助公司获得最多的收入，或者哪些汽车没有获得足够的收入。 这可以通过以下查询来完成
选择 cid、sum（金额）作为旅行收入
INNER 在 trips.bid=bookings.bid 上进行预订
WHERE end_time > 今天 () 按 cid 分组
按收入排序 DESC LIMIT 10
设置 query_mode='table'
结果是这样的
| cid | 收入 |
|---|---|
| c87850 | 675.9 |
| c30765 | 637.48 |
| c72990 | 487.36 |
| c00956 | 481.66 |
| c21898 | 479.64 |
| c96280 | 476.62 |
| c59872 | 461.2 |
| c51800 | 451.14 |
| c20995 | 445.48 |
| c04604 | 445.3 |
你可以通过从 dim_car_info中查找汽车牌照来进一步丰富数据
将排名前十的汽车作为（
选择 cid，总和（金额）作为旅行收入
INNER 在 tips.bid=bookings.bid 上加入预订
今天的结束时间（）按 cid 分组
按收入减去限额 10
）
从前 10 名汽车中选择 cid、收入、牌照_否
INNER 在 top10cars.cid=dim_car_info.ci.ci 上加入 dim_car_info.info d
设置 query_mode='table'
结果是
| cid | 收入 | 牌照号 |
|---|---|---|
| c30765 | 749.06 | OPIH21 |
| c87850 | 701.14 | 384K9Z |
| c00956 | 522.66 | 3JHAYX |
| c68812 | 497.12 | LR3AF9 |
| c51363 | 495.98 | B8UVY6 |
| c72990 | 487.36 | W6IZOB |
| c91904 | 486.14 | LIHUZW |
| c21898 | 479.64 | 1KTBIJ |
| c20995 | 477.38 | 6GN7SP |
| c96280 | 476.62 | YSK84J |
数据转换/过滤/清理
T-MASK：清理敏感字段
用例： 清理敏感数据字段（例如 PII 或付款信息）以向下游消费者隐藏
在此示例中，在用户活动分析期间仅显示信用卡号的前四位和后四位数字。
从 user_info 中选择 uid、replace_regex (credit_card、'(\\ d{4}) (\\ d*) (\\ d{4})'、'\\ 1***\\ 3') 作为卡片
结果：
| uid | 卡 |
|---|---|
| u00001 | 3717\ _\ _\ *8910 |
T-DERIVE：根据原始数据计算派生列
用例： 创建新列以合并来自原始数据中多列的信息，或者将某些列中的数据转换为另一种格式，以便为显示做好准备。
从 user_info 中选择 uid，concat（first_name，'，last_name）作为全名，
年（今天（））-年（to_date（生日））作为年龄
结果：
| uid | 全名 | 年龄 |
|---|---|---|
| u00001 | Foo酒吧 | 32 |
T-LOOKUP：将标识符转换为更具可读性的信息
用例： 在检查汽车物联网数据时，我们想将汽车 ID 转换为其车牌号。
从 car_live_data 中选择时间、c.license_plate_no AS 许可证、gas_percent、speed_kmh
INNER 在 car_live_data.cid=cid上以 c
的身份加入 car_info
结果：
| 时间 | cid | 执照 | gas_percent | speed_kmh |
|---|---|---|---|---|
| 2022-01-12 23:00:58.476 | c00001 | KM235L | 55 | 50 |