分析时间序列数据¶
您可以使用专为分析时间序列数据而设计的功能，在 Snowflake 中分析时间序列数据。数据库管理员、数据科学家和应用程序开发者必须确保时间序列高效存储和加载，并在很多情况下汇总为完整且一致的形式，然后才能将数据提供给业务分析师和其他使用者。
Introduction to time-series data¶
时间序列 由连续的观察结果组成，这些观察结果捕获了系统、流程和行为在一段时间内的变化。时间序列数据从各行各业的各种设备中收集。常见示例包括为金融应用程序收集的股票交易数据、天气观测结果、从智能工厂中传感器收集的温度读数以及数字广告中的用户点击日志。
时间序列中的单个记录通常包含以下组件：
具有一致粒度级别（毫秒、秒、分钟、小时等）的日期、时间或时间戳。
一个或多个某种测量结果或指标，通常为数字（可能揭示数据中的趋势或异常的事实）。
与测量结果关联的关注维度，例如温度读数对应的位置或给定交易的股票代码。
例如，以下天气观测结果具有开始和结束时间戳，降雨量测量结果 (0.32) 和位置信息：
EVENTID | TYPE | SEVERITY | START_TIME | END_TIME | PRECIP | TIME_ZONE | CITY | COUNTY | STATE | ZIP
W100 | Rain | Moderate | 2020-12-20 16:35:00.000 | 2020-12-20 17:15:00.000 | 0.32 | US/Eastern | Southport | Brunswick | NC | 28461
从出厂设备收集的以下数据具有命名空间 (IOT)、标签 ID 或传感器 ID (3000)、设备上温度读数的时间戳、温度读数本身 (21.1673) 和“代理时间戳”，即数据随后到达数据代理的时间。例如，数据代理可能是将数据引入 Snowflake 表的 Kafka 服务器。
DEVICE | LINE | DEVICE_TIMESTAMP | TEMP | BROKER_TIMESTAMP
IOT | 3000 | 2023-01-01 00:01:00.000 | 21.1673 | 2023-01-01 00:01:32.000
当读数因某种原因而发生巨大变化时，时间序列可能会显示峰值。例如，下图显示了每隔 15 秒采集的一系列温度读数，其中值在前一天稳定在 35°C 左右，之后峰值超过 40°C。
以下部分介绍如何借助能提供快速、准确结果的 SQL 函数和联接，分析和可视化大量此类数据。
如何存储时间序列数据¶
支持以下 日期时间数据类型：
DATE
TIME
TIMESTAMP（和变体，包括 TIMESTAMP_TZ）
有关加载、管理和查询使用这些数据类型的数据的信息，请参阅 使用日期和时间值。
一些常用的 SQL 函数 可用于帮助存储和查询时间序列数据。例如，您可以使用 CONVERT_TIMEZONE 将时间戳从一个时区转换为另一个时区。您可以使用 EXTRACT 和 TIMEADD 之类的函数，根据需要操作基于时间的数据。
备注
对于 TIMESTAMP_TZ 数据，Snowflake 存储给定时区的偏移，而非给定值创建时的实际时区。
为优化查询性能，用于时间序列分析的表通常按时间（有时也按传感器 ID 或类似维度）进行群集。请参阅 群集密钥和聚类表。
汇总时间序列数据¶
时间序列数据管理可能需要将大量精细粒度记录汇总为更概括的形式（这一过程有时称为“降采样”）。给定具有特定时间粒度（毫秒、秒、分钟等）的大量记录后，您可以将这些记录汇总到更粗的粒度，从而有效地生成较小的样本。
降采样很有价值，因为它缩小了数据集的大小并降低了其存储要求。较粗的粒度级别还可以减少查询执行期间的计算资源要求。进行降采样的另一个关键原因是，从分析师的角度来看，时间序列中的大量记录可能是多余的。例如，如果传感器每秒发出一次新值，但此测量值在每 60 秒的间隔内很少变化，则数据可以汇总到分钟级进行分析。
当需要将两个不同的数据集作为一个数据集进行分析，但它们具有不同的时间粒度时，会发生降采样的另一种情况。例如，工厂中的传感器 A 每 15 秒收集一次数据，但传感器 B 每 30 秒收集一次相关数据。在这种情况下，将记录汇总到 1 分钟的桶中可能是有效解决方案。每个数据集中的 IDs 和维度保持原样，但数值测量结果按通用时间间隔相加或求平均值。
降采样示例¶
您可以使用 TIME_SLICE 函数对表中存储的数据集进行降采样。此函数计算固定宽度“桶”的开始和结束时间，以便可以使用标准聚合函数（如 SUM 和 AVG）划分和汇总单个记录。
同样，DATE_TRUNC 函数会截断一系列日期或时间戳值的一部分，从而降低其粒度。以下部分将展示每个函数的示例。
使用 TIME_SLICE 降采样¶
以下示例对名为 sensor_data_ts 的表进行降采样，该表包含来自两个工厂传感器的读数，并包含 530 万行。这些读数每秒引入一次，因此 530 万行仅代表一个月的数据，每个传感器刚刚超过 250 万行。例如，您可以使用 TIME_SLICE 函数，每分钟、每小时或每天最多汇总一行。
要运行此示例，请首先创建并加载 sensor_data_ts 表；请参阅 创建 sensor_data_ts 表。以下是表中的一小部分数据：
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE1 | 2024-03-01 00:00:00.000 | 32.6908 | 0.3158 | 1492 |
| DEVICE2 | 2024-03-01 00:00:00.000 | 35.2086 | 0.3232 | 1461 |
| DEVICE1 | 2024-03-01 00:00:01.000 | 35.9578 | 0.3302 | 1452 |
| DEVICE2 | 2024-03-01 00:00:01.000 | 26.2468 | 0.3029 | 1455 |
+-----------+-------------------------+-------------+-----------+-----------+
该表包含每台设备每分钟的 60 个读数，如此查询所示：
SELECT device_id, count(*) FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY device_id;
+-----------+----------+
| DEVICE_ID | COUNT(*) |
|-----------+----------|
| DEVICE2 | 60 |
| DEVICE1 | 60 |
+-----------+----------+
在此降采样查询中，TIME_SLICE 函数定义一分钟桶，并返回每个桶的开始时间。AVG 函数计算每台设备每个桶的平均温度。包括 COUNT(*) 函数以供参考，仅仅为了显示归入每个时间桶的行数。
vibration 和 motor_rpm 列不包括在内，但它们可以采用与 temperature 列相同的方式汇总，或者使用不同的聚合函数。
重要
如果您自行运行此示例，输出将不完全匹配，因为 sensor_data_ts 表加载了随机生成的值。
SELECT
TIME_SLICE(TO_TIMESTAMP_NTZ(timestamp), 1, 'MINUTE') minute_slice,
device_id,
COUNT(*),
AVG(temperature) avg_temp
FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY 1,2
ORDER BY 1,2;
+-------------------------+-----------+----------+---------------+
| MINUTE_SLICE | DEVICE_ID | COUNT(*) | AVG_TEMP |
|-------------------------+-----------+----------+---------------|
| 2024-03-01 00:01:00.000 | DEVICE1 | 60 | 32.4315466667 |
| 2024-03-01 00:01:00.000 | DEVICE2 | 60 | 30.4967783333 |
+-------------------------+-----------+----------+---------------+
通过使用 TIME_SLICE 函数，您可以创建更小的聚合表以进行分析，并且可以在不同级别（小时、天、周等）应用降采样过程。
使用 DATE_TRUNC 降采样¶
以下示例在 Tasty Bytes 样本数据库 (https://quickstarts.snowflake.com/guide/tasty_bytes_introduction/index.html#0) 的 raw.pos 架构中，从名为 order_header 的表中选择数据。此表包含 2.48 亿行。
order_header 表具有名为 order_ts 的 TIMESTAMP 列。查询将此列用作 DATE_TRUNC 函数的第二个实参，从而创建汇总时间序列。第一个实参指定 day 间隔。这意味着，具有小时/分钟/秒粒度的各个记录将按天汇总。
查询按两个维度划分记录：truck_id 和 location_id。avg_amount 列返回记录中每个工作日内每个订单、每辆餐车、每个位置的平均价格。
此处显示的查询将结果限制为 2022 年 1 月 1 日的前 25 行。如果您移除此日期筛选器和 LIMIT 子句，查询会将原始的 2.48 亿行降采样到大约 500,000 行。
SELECT DATE_TRUNC('day', order_ts)::date sliced_ts, truck_id, location_id, AVG(order_amount)::NUMBER(4,2) as avg_amount
FROM order_header
WHERE EXTRACT(YEAR FROM order_ts)='2022'
GROUP BY date_trunc('day', order_ts), truck_id, location_id
ORDER BY 1, 2, 3 LIMIT 25;
+------------+----------+-------------+------------+
| SLICED_TS | TRUCK_ID | LOCATION_ID | AVG_AMOUNT |
|------------+----------+-------------+------------|
| 2022-01-01 | 1 | 3223 | 19.23 |
| 2022-01-01 | 1 | 3869 | 20.15 |
| 2022-01-01 | 2 | 2401 | 39.29 |
| 2022-01-01 | 2 | 4199 | 34.29 |
| 2022-01-01 | 3 | 2883 | 35.01 |
| 2022-01-01 | 3 | 2961 | 39.15 |
| 2022-01-01 | 4 | 2614 | 35.95 |
| 2022-01-01 | 4 | 2899 | 40.29 |
| 2022-01-01 | 6 | 1946 | 26.58 |
| 2022-01-01 | 6 | 14960 | 18.59 |
| 2022-01-01 | 7 | 1427 | 26.91 |
| 2022-01-01 | 7 | 3224 | 28.88 |
| 2022-01-01 | 9 | 1557 | 35.52 |
| 2022-01-01 | 9 | 2612 | 43.80 |
| 2022-01-01 | 10 | 2217 | 32.35 |
| 2022-01-01 | 10 | 2694 | 32.23 |
| 2022-01-01 | 11 | 2656 | 44.23 |
| 2022-01-01 | 11 | 3327 | 52.00 |
| 2022-01-01 | 12 | 3181 | 52.84 |
| 2022-01-01 | 12 | 3622 | 49.59 |
| 2022-01-01 | 13 | 2516 | 31.13 |
| 2022-01-01 | 13 | 3876 | 28.13 |
| 2022-01-01 | 14 | 1359 | 72.04 |
| 2022-01-01 | 14 | 2505 | 68.75 |
| 2022-01-01 | 15 | 2901 | 41.90 |
+------------+----------+-------------+------------+
使用窗口聚合进行滚动计算¶
通过使用窗口化聚合函数来观察指标如何随时间变化，您可以分析趋势的时间序列。窗口化聚合可用于分析较大数据集的已定义子集（“窗口”）内的数据。您可以计算数据集中每一行的滚动计算（例如移动平均值和总和），考虑当前行之前、之后或周围的一组行。这种分析与常规聚合形成对比，后者总结了整个数据集。
通过使用基于范围的窗口框架和显式偏移量，您可以对计算这些滚动聚合应用非常灵活的方法。RANGE BETWEEN 窗口框架，按时间戳或编号排序，不会被时间序列数据中可能出现的间隙所干扰。例如，在以下插图中，Day 4 数据在记录系列中缺失并不影响对三天移动窗口的聚合函数的计算。特别是，考虑到 Day 4 数据是未知的，框架 3、4 和 5 的计算是正确的。
以下示例计算了天气数据的移动总和，该数据记录了不同城市和县的每小时降水量读数。您可以运行这种查询来评估各种时间序列数据集中的趋势，例如传感器和其他 IoT 设备，特别是当这些数据集已知或预计会有间隙时。
窗口函数在其框架中包括当前降水读数和 所有在当前读数之前指定时间间隔内的读数。 滚动计算基于这一灵活而合理的 范围 行，而不是确切的 行数。每个城市的第一行都有匹配的 precip 和 moving_sum_precip 值。之后，重新计算框架中每个后续行的总和。原始值波动显著，但移动总和具有很强的平滑效果。
要运行此示例，请首先遵循以下说明：创建并加载 heavy_weather 表。这个非常小的表格包含零散的每小时天气观测，存在很多间隙，包括缺失的一天。查询返回按 start_time 列排序的降水值的移动总和。窗口框架定义了当前行和当前行之前 12 小时之间的范围。因此，框架由当前行以及仅包含那些时间戳在当前行的 ORDER BY 时间戳之前 12 小时的行组成。
SELECT city, start_time, precip,
SUM(precip) OVER(
PARTITION BY city
ORDER BY start_time
RANGE BETWEEN INTERVAL '12 hours' PRECEDING AND CURRENT ROW) moving_sum_precip
FROM heavy_weather
WHERE city IN('South Lake Tahoe','Big Bear City')
GROUP BY city, precip, start_time
ORDER BY city;
+------------------+-------------------------+--------+-------------------+
| CITY | START_TIME | PRECIP | MOVING_SUM_PRECIP |
|------------------+-------------------------+--------+-------------------|
| Big Bear City | 2021-12-24 05:35:00.000 | 0.42 | 0.42 |
| Big Bear City | 2021-12-24 16:55:00.000 | 0.09 | 0.51 |
| Big Bear City | 2021-12-26 09:55:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-23 16:23:00.000 | 0.56 | 0.56 |
| South Lake Tahoe | 2021-12-23 17:24:00.000 | 0.38 | 0.94 |
| South Lake Tahoe | 2021-12-23 18:30:00.000 | 0.28 | 1.22 |
| South Lake Tahoe | 2021-12-23 19:36:00.000 | 0.80 | 2.02 |
| South Lake Tahoe | 2021-12-24 06:49:00.000 | 0.17 | 0.97 |
| South Lake Tahoe | 2021-12-24 15:53:00.000 | 0.07 | 0.24 |
| South Lake Tahoe | 2021-12-26 05:43:00.000 | 0.16 | 0.16 |
| South Lake Tahoe | 2021-12-27 14:53:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-27 17:53:00.000 | 0.07 | 0.14 |
+------------------+-------------------------+--------+-------------------+
大贝尔城的三个 moving_sum_precip 值如下计算：
0.42 = 0.42（没有前面的行）
0.42 + 0.09 = 0.51（前两行在 12 小时窗口内）
0.07 = 0.07（没有前面的行在 12 小时窗口内）
南太浩湖的行包括以下计算，例如：
0.56 + 0.38 + 0.28 + 0.80 = 2.02 （2024 年 12 月 23 日的所有四行都在 12 小时内）
0.80 + 0.17 = 0.97（前一行在 12 小时窗口内）
其他窗口函数，例如 LEAD 和 LAG 排名函数，也常用于时间序列分析。使用 LEAD 窗口函数可查找时间序列中相对于当前数据点的下一个数据点，使用 LAG 函数可查找上一个数据点。
在 Snowsight 中可视化查询结果¶
您可以使用 Snowsight 可视化聚合查询的结果，并通过滑动窗口框架更好地了解计算的平滑效果。在查询工作表中，点击 Results 旁边的 Chart 按钮。
例如，在下面的条形图中，相较于表示原始温度的蓝线，黄线表明平均温度的趋势更加平稳。查询本身看起来是这样的：
SELECT device_id, timestamp, temperature, AVG(temperature)
OVER (PARTITION BY device_id ORDER BY timestamp
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_temp
FROM sensor_data_ts
WHERE timestamp BETWEEN '2024-03-15 00:00:59.000' AND '2024-03-15 00:01:10.000'
ORDER BY 1, 2;
使用 MIN_BY 和 MAX_BY 聚合函数¶
对于处理时间序列数据的 SQL 开发者来说，通常需要能够根据同一行中另一列的最小值或最大值选择一列。MIN_BY 和 MAX_BY 是便捷函数，当数据按其他列（如时间戳）排序时，函数返回表中的开始值和结束值（或最高值和最低值，或第一个值和最后一个值）。
第一个示例仅查找整个表中的最后一个（最新的） precip 值。MAX_BY 函数按其 start_time 值对所有行进行排序，然后返回“最大”开始时间的 precip 值。
要创建并加载以下示例中使用的表，请参阅 创建 heavy_weather 表。
SELECT MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather;
+--------------------+
| MOST_RECENT_PRECIP |
|--------------------|
| 0.07 |
+--------------------+
您可以通过运行以下查询来验证此结果（并获取相关详细信息）：
SELECT * FROM heavy_weather WHERE start_time=
(SELECT MAX(start_time) FROM heavy_weather);
+-------------------------+--------+-------+-------------+
| START_TIME | PRECIP | CITY | COUNTY |
|-------------------------+--------+-------+-------------|
| 2021-12-30 20:53:00.000 | 0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+
您可以添加 GROUP BY 子句，就此数据提出更有趣的问题。例如，以下查询查找为加利福尼亚州的每个城市观测到的最后降水量值，按 precip 值排序（从高到低）。结果按 city 分组，返回每个不同城市的最后 precip 值。
SELECT city, MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+--------------------+
| CITY | MOST_RECENT_PRECIP |
|------------------+--------------------|
| Alta | 0.89 |
| Bishop | 0.75 |
| Mammoth Lakes | 0.37 |
| Alturas | 0.23 |
| Mount Shasta | 0.09 |
| South Lake Tahoe | 0.07 |
| Big Bear City | 0.07 |
| Montague | 0.07 |
| Lebec | 0.07 |
+------------------+--------------------+
上次采集阿尔塔市的观测结果时，precip 值为 0.89，上次采集南太浩湖、大贝尔城、蒙塔古和勒贝克等城市的观测结果时，这四个位置的 precip 值为 0.07。（请注意，查询不会告诉您这些观测结果的采集时间。）
您可以使用 MIN_BY 函数，返回“相反”结果集（更早的 precip 记录与近期记录）。
SELECT city, MIN_BY(precip, start_time) oldest_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+---------------+
| CITY | OLDEST_PRECIP |
|------------------+---------------|
| South Lake Tahoe | 0.56 |
| Big Bear City | 0.42 |
| Mammoth Lakes | 0.37 |
| Alta | 0.25 |
| Alturas | 0.23 |
| Bishop | 0.08 |
| Lebec | 0.08 |
| Mount Shasta | 0.08 |
| Montague | 0.07 |
+------------------+---------------+
联接时间序列数据¶
您可以使用 ASOF JOIN 构造，联接包含时间序列数据的表。虽然 ASOF JOIN 查询可以通过使用复合 SQL、其他类型的联接和窗口函数来模拟，但使用 ASOF JOIN 语法可以更轻松地写入（和优化）这些查询。
ASOF 联接的常见用途是分析金融交易数据。例如，交易成本分析需要“滑移”计算，衡量在决定购买股票时的报价与执行和记录交易时实际支付的价格之间的差额。ASOF JOIN 可以加快此类分析。鉴于这种联接方法的关键功能是相对于另一个时间序列来分析一个时间序列，ASOF JOIN 可用于分析任何过往的数据集。在许多此类用例中，当来自不同设备的读数具有不完全相同的时间戳时，ASOF JOIN 可用于关联数据。
假设您需要分析的时间序列数据存在于两个表中，并且每个表中的每一行都有一个时间戳。此时间戳表示记录事件的精确“截至”日期和时间。对于第一个（或左侧）表中的每一行，联接使用“匹配条件”，其中包含您指定的比较运算符，用于在第二个（或右侧）表中查找时间戳值为以下值的单行：
小于或等于左侧表中的时间戳值。
大于或等于左侧表中的时间戳值。
小于左侧表中的时间戳值。
大于左侧表中的时间戳值。
右侧的限定行是最接近的匹配项，可能时间相等、时间更早或更晚，具体取决于指定的比较运算符。
ASOF JOIN 的结果的基数始终等于左侧表的基数。如果左侧表包含 4000 万行，则 ASOF JOIN 返回 4000 万行。因此，左侧表可以视作“保留”表，右侧表视作“引用”表。
联接关于最接近的匹配项的两个表（一致性）¶
例如，在财务应用程序中，您可能有名为 quotes 的表和名为 trades 的表。一个表记录购买股票的出价历史记录，另一个表记录实际交易的历史记录。购买股票的出价发生在交易之前（或者可能在“相同”时间，具体取决于记录时间的粒度）。两个表都有时间戳，并且都有您可能想要比较的其他感兴趣的列。简单的 ASOF JOIN 查询将在每笔交易之前（及时）返回最更接近的报价。换言之，查询会询问：在我进行交易时，给定股票的价格是多少？
假设 trades 表包含三行，并且 quotes 表包含七行。当基于匹配的股票代码联接行，并比较它们的时间戳列时，单元格的背景颜色显示来自 quotes 的哪三行符合 ASOF JOIN 的资格。
TRADES 表（左侧表或“保留”表）
QUOTES 表（右侧表或“参考”表）
这个概念性示例很容易变成特定的 ASOF JOIN 查询：
SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
FROM trades t ASOF JOIN quotes q
MATCH_CONDITION(t.trade_time >= quote_time)
ON t.stock_symbol=q.stock_symbol
ORDER BY t.stock_symbol;
+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY | QUOTE_TIME | PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL | 2023-10-01 09:00:05.000 | 2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW | 2023-10-01 09:00:05.000 | 1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW | 2023-10-01 09:00:10.000 | 1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+
ON 条件按其股票代码对匹配的行进行分组。
要运行此示例，请按如下方式创建并加载表：
CREATE OR REPLACE TABLE trades (
stock_symbol VARCHAR(4),
trade_time TIMESTAMP_NTZ(9),
quantity NUMBER(38,0)
);
CREATE OR REPLACE TABLE quotes (
stock_symbol VARCHAR(4),
quote_time TIMESTAMP_NTZ(9),
price NUMBER(12,8)
);
INSERT INTO trades VALUES
('SNOW','2023-10-01 09:00:05.000', 1000),
('AAPL','2023-10-01 09:00:05.000', 2000),
('SNOW','2023-10-01 09:00:10.000', 1500);
INSERT INTO quotes VALUES
('SNOW','2023-10-01 09:00:01.000', 166.00),
('SNOW','2023-10-01 09:00:02.000', 163.00),
('SNOW','2023-10-01 09:00:07.000', 166.00),
('SNOW','2023-10-01 09:00:08.000', 165.00),
('AAPL','2023-10-01 09:00:03.000', 139.00),
('AAPL','2023-10-01 09:00:07.000', 142.00),
('AAPL','2023-10-01 09:00:11.000', 142.00);
有关 ASOF JOIN 查询的更多示例，请参阅 示例。
Filling gaps in time-series data¶
时间序列分析通常要求数据具有一致的粒度且每个间隔都有记录，但现实中的数据往往以不规则间隔到达或存在缺失。例如，您可能拥有一个以小时为主的数据集，但需要生成半小时条目以匹配下游分析需求；或者您已具备一致的分辨率，但发现序列中存在间隙。Snowflake 间隙填充功能提供了高效的方法，可为时间序列数据应用统一间隔并填补所有间隙。
例如，以下八条记录捕获了 2025 年 3 月 15 日加利福尼亚州两个城市的天气观测数据。
+-------------------------+-------------+------------------+----------------+
| OBSERVED | TEMPERATURE | CITY | COUNTY |
|-------------------------+-------------+------------------+----------------|
| 2025-03-15 09:49:00.000 | 48 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | 44 | South Lake Tahoe | El Dorado |
| 2025-03-15 09:55:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 09:55:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:10:00.000 | 51 | Big Bear City | San Bernardino |
| 2025-03-15 10:10:00.000 | 52 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:15:00.000 | 54 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:18:00.000 | 54 | Big Bear City | San Bernardino |
+-------------------------+-------------+------------------+----------------+
尽管这些记录具有大致一致的粒度（天、时、分），但行之间的间隔并不一致，变化范围在 1 到 15 分钟之间。若目标是以五分钟为间隔收集数据，则存在多行数据缺失。
使用 RESAMPLE 子句¶
您可以通过将一组行“上采样”至特定时间间隔来调整其粒度并提升一致性。要进行此类更改，请使用您在 SELECT 语句 FROM 子句中定义的 RESAMPLE 子句。重采样数据集的结果是一个 更大 的数据集，它保留所有现有输入行，并生成若干新行以填补时间序列中的间隙值。（请注意，您也可以使用 RESAMPLE 子句将行“下采样”为更小、更粗粒度的结果集。）
根据定义，时间序列中始终有一列包含一系列日期、时间戳或代表日期或时间的数值。重采样对源表中的此类列进行操作，并且必须使用 INTERVAL 值（如 5 minutes、30 minutes 或 1 hour）来指定所需的粒度。
通常，您还可以定义分区，在特定维度上创建时间序列行，而不仅仅是每个间隔生成一个新的时间戳。
RESAMPLE 查询的结构如下所示：
SELECT *
FROM time_series_table
RESAMPLE (
USING time_series_column
INCREMENT BY INTERVAL '5 minutes'
PARTITION BY other_column_1, other_column_2)
ORDER BY time_series_column;
生成的行中的列设置为 NULL，在 USING 和 PARTITION BY 子句中指定的列除外。指定的日期、时间或数值列以及分区列均包含有意义的生成值。
要运行一个使用前述八条记录的简单示例，请先创建并加载以下表：
CREATE OR REPLACE TABLE march_temps
(observed TIMESTAMP, temperature INT, city VARCHAR(20), county VARCHAR(20));
INSERT INTO march_temps VALUES
('2025-03-15 09:50:00.000',44,'South Lake Tahoe','El Dorado'),
('2025-03-15 09:55:00.000',46,'South Lake Tahoe','El Dorado'),
('2025-03-15 10:10:00.000',52,'South Lake Tahoe','El Dorado'),
('2025-03-15 10:15:00.000',54,'South Lake Tahoe','El Dorado'),
('2025-03-15 09:49:00.000',48,'Big Bear City','San Bernardino'),
('2025-03-15 09:55:00.000',49,'Big Bear City','San Bernardino'),
('2025-03-15 10:10:00.000',51,'Big Bear City','San Bernardino'),
('2025-03-15 10:18:00.000',54,'Big Bear City','San Bernardino')
;
现在以 5 minutes 为间隔从该表中选择上采样行：
SELECT *
FROM march_temps
RESAMPLE (
USING observed
INCREMENT BY INTERVAL '5 minutes')
ORDER BY observed;
+-------------------------+-------------+------------------+----------------+
| OBSERVED | TEMPERATURE | CITY | COUNTY |
|-------------------------+-------------+------------------+----------------|
| 2025-03-15 09:45:00.000 | NULL | NULL | NULL |
| 2025-03-15 09:49:00.000 | 48 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | 44 | South Lake Tahoe | El Dorado |
| 2025-03-15 09:55:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 09:55:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 10:00:00.000 | NULL | NULL | NULL |
| 2025-03-15 10:05:00.000 | NULL | NULL | NULL |
| 2025-03-15 10:10:00.000 | 52 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:10:00.000 | 51 | Big Bear City | San Bernardino |
| 2025-03-15 10:15:00.000 | 54 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:18:00.000 | 54 | Big Bear City | San Bernardino |
+-------------------------+-------------+------------------+----------------+
此查询保留了原来的八行并生成了三行新行，填补了三个时间间隔的空白，分别是 09:45、10:00 和 10:05。NULL 值将插入到 temperature、city 和 county 列中。
时间序列的起点为 2025-03-15 09:45:00.000，因为它距离输入数据集 (2025-03-15 09:49:00.000) 中的最早时间戳不到 5 分钟。
如果要删除间隔不一致的行（在本例中为 09:49 和 10:18），请参阅 使用 BUCKET_START() 筛选掉非均匀行的 RESAMPLE 示例。
现在在查询中添加一个 PARTITION BY 子句：
SELECT *
FROM march_temps
RESAMPLE (
USING observed
INCREMENT BY INTERVAL '5 minutes'
PARTITION BY city, county)
ORDER BY city, county, observed;
+-------------------------+-------------+------------------+----------------+
| OBSERVED | TEMPERATURE | CITY | COUNTY |
|-------------------------+-------------+------------------+----------------|
| 2025-03-15 09:45:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 09:49:00.000 | 48 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 09:55:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 10:00:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 10:05:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 10:10:00.000 | 51 | Big Bear City | San Bernardino |
| 2025-03-15 10:15:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 10:18:00.000 | 54 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | 44 | South Lake Tahoe | El Dorado |
| 2025-03-15 09:55:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:00:00.000 | NULL | South Lake Tahoe | El Dorado |
| 2025-03-15 10:05:00.000 | NULL | South Lake Tahoe | El Dorado |
| 2025-03-15 10:10:00.000 | 52 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:15:00.000 | 54 | South Lake Tahoe | El Dorado |
+-------------------------+-------------+------------------+----------------+
分区结果在两个方面有所不同：
生成 7 行，共计 15 行。现在，每个分区每 5 分钟间隔就有一行。
分区列已正确生成
city和county值。生成的行中唯一具有 NULL 值的列是temperature。
您还可以在 RESAMPLE 语法中指定 METADATA_COLUMNS 参数，将以下列添加到结果中：
is_generated元数据列用于标识由 RESAMPLE 操作生成的行以及原本已存在的行。bucket_start元数据列返回的值标记了 RESAMPLE 操作生成的当前桶或间隔的起始点。您可以使用此列来确定重采样后特定行属于哪个间隔，也可以使用它对重采样的数据运行聚合查询。请参阅 使用 BUCKET_START() 汇总重采样行的 RESAMPLE 示例。
有关完整的 RESAMPLE 语法，请参阅 RESAMPLE。
要存储 RESAMPLE 查询结果，请使用选择数据并将其插入新表的 CTAS语句：
CREATE OR REPLACE TABLE march_temps_every_five_mins AS
SELECT *
FROM march_temps
RESAMPLE (
USING observed
INCREMENT BY INTERVAL '5 minutes'
PARTITION BY city, county)
ORDER BY city, county, observed;
将值插值或“填充间隙”到时间序列中¶
尽管您可以独立使用 RESAMPLE 语法和插值函数，但它们最常一起使用，以填补 单个查询 范围内的时间序列数据。对数据集进行重新采样后，您可以调用插值函数来更新新生成的行中其他感兴趣的列。插值过程会更新以前为 NULL 的列（例如数值测量值），根据前一行或下一行中的值为它们提供有意义的值。
您可以通过调用 INTERPOLATE_FFILL、INTERPOLATE_BFILL 和 INTERPOLATE_LINEAR 窗口函数来插值。例如，INTERPOLATE_FFILL 函数查找相关列的时间序列中先前（最后一个）的值：
SELECT observed,
INTERPOLATE_FFILL(temperature) OVER (PARTITION BY city, county ORDER BY observed) ffill_temp,
city, county
FROM march_temps_every_five_mins
ORDER BY city, county, observed;
+-------------------------+------------+------------------+----------------+
| OBSERVED | FFILL_TEMP | CITY | COUNTY |
|-------------------------+------------+------------------+----------------|
| 2025-03-15 09:45:00.000 | NULL | Big Bear City | San Bernardino |
| 2025-03-15 09:49:00.000 | 48 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | 48 | Big Bear City | San Bernardino |
| 2025-03-15 09:55:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 10:00:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 10:05:00.000 | 49 | Big Bear City | San Bernardino |
| 2025-03-15 10:10:00.000 | 51 | Big Bear City | San Bernardino |
| 2025-03-15 10:15:00.000 | 51 | Big Bear City | San Bernardino |
| 2025-03-15 10:18:00.000 | 54 | Big Bear City | San Bernardino |
| 2025-03-15 09:50:00.000 | 44 | South Lake Tahoe | El Dorado |
| 2025-03-15 09:55:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:00:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:05:00.000 | 46 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:10:00.000 | 52 | South Lake Tahoe | El Dorado |
| 2025-03-15 10:15:00.000 | 54 | South Lake Tahoe | El Dorado |
+-------------------------+------------+------------------+----------------+
ffill_temp 列的第一行返回 NULL，因为 INTERPOLATE_FFILL 函数没有前一行可供使用。
有关这些窗口函数的更多信息，请参阅 INTERPOLATE_BFILL、INTERPOLATE_FFILL、INTERPOLATE_LINEAR。
在一次操作中对结果进行上采样、填充间隙和存储结果¶
为了简化填充数据集的整个过程，可以在单个查询中对数据进行向上采样和插值，并使用 CTAS 操作保存结果。例如，以下 CTAS 语句创建了一个新表，该表将测量值插入到向上采样的数据集中：
CREATE OR REPLACE TABLE march_temps_every_five_mins_with_interpolations
(observed TIMESTAMP, temperature INT, ffill INT, bfill INT, linear INT, city VARCHAR(20), county VARCHAR(20))
AS
SELECT observed, temperature,
INTERPOLATE_FFILL(temperature) OVER (PARTITION BY city ORDER BY observed) ffill,
INTERPOLATE_BFILL(temperature) OVER (PARTITION BY city ORDER BY observed) bfill,
INTERPOLATE_LINEAR(temperature) OVER (PARTITION BY city ORDER BY observed) linear,
city,
county
FROM march_temps
RESAMPLE(
USING observed
INCREMENT BY INTERVAL '5 minutes'
PARTITION BY city, county)
ORDER BY observed;
Gap-filling with ASOF JOIN¶
备注
要使用推荐的方法进行间隙填充和插值，请参阅 Filling gaps in time-series data。RESAMPLE 构造和 INTERPOLATE 函数是预览版功能，以下填充间隙的 ASOF JOIN 方法仅作为潜在的解决方法包括在内。
In addition to aligning the data in two tables by finding non-exact matches on time-based columns, ASOF JOIN is useful for filling gaps in a time series when your raw data table is missing rows for particular dates or timestamps. For example, when rows are missing because faulty equipment, or a power failure, results in skipped sensor readings, you can use ASOF JOIN to interpolate values from a generated time series into the table. The missing rows are filled in with the last known value for the readings that are missing. This value is also known as the "last observation carried forward" (LOCF). The ASOF JOIN query returns a complete set of rows that are in chronological order and contiguous.
要为插值使用 ASOF JOIN，请执行以下步骤：
通过运行简单查询来确定表间隙。
为您需要涵盖的时间段生成完整的时间序列，并使用适当的粒度。例如，时间序列可能是特定年份的简单日期序列，或者是特定天数的每秒时间戳的更精细序列。您可以使用 SQL 或电子表格应用程序生成值列表。
对于稍后在 ASOF JOIN ON 条件中指定的每个行，时间序列还需要有意义的 ID 或维度。
编写 ASOF JOIN 查询，将值插入缺失行。生成的时间序列将是保留表，原始数据表将是引用表。
以下示例需要 sensor_data_ts 表。如果尚未创建并加载该表，请参阅 创建 sensor_data_ts 表。要模拟间隙填充操作需求，请从表中删除一些行，如下所示：
DELETE FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP > ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:20');
结果是一个表，该表在 3 月 7 日缺失 DEVICE2 的五行（1:16 至 1:20）。
+------------------------+
| number of rows deleted |
|------------------------|
| 5 |
+------------------------+
现在，按照以下步骤完成间隙填充练习。
备注
如果您自行运行此示例，输出将不完全匹配，因为 sensor_data_ts 表加载了随机生成的值。
第 1 步：验证表是否存在间隙¶
运行以下查询以识别间隙：
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
此查询返回 DEVICE2 的两行：间隙前的最后一行和间隙后的第一行。
第 2 步：生成完整的时间序列以填补已知的间隙¶
要为 sensor_data_ts 表中的间隙生成具有细粒度（每秒一行）的时间序列，请创建以下表，其中包含生成的时间戳：
CREATE OR REPLACE TABLE continuous_timestamps AS
SELECT 'DEVICE2' as DEVICE_ID,
DATEADD('SECOND', ROW_NUMBER() OVER (ORDER BY SEQ8()), '2024-03-07 00:01:15')::TIMESTAMP_NTZ AS TS
FROM TABLE(GENERATOR(ROWCOUNT => 5));
在此 SQL 语句中，5 是填补间隙所需的秒数。请注意，生成的行中包含设备 ID 值 (DEVICE2)。
以下查询返回五个生成的行。
SELECT * FROM continuous_timestamps ORDER BY ts;
+-----------+-------------------------+
| DEVICE_ID | TS |
|-----------+-------------------------|
| DEVICE2 | 2024-03-07 00:01:16.000 |
| DEVICE2 | 2024-03-07 00:01:17.000 |
| DEVICE2 | 2024-03-07 00:01:18.000 |
| DEVICE2 | 2024-03-07 00:01:19.000 |
| DEVICE2 | 2024-03-07 00:01:20.000 |
+-----------+-------------------------+
第 3 步：使用 ASOF JOIN 插入值¶
现在，您可以运行 ASOF JOIN 查询，将 continuous_timestamps 与 sensor_data_ts 联接，并为 DEVICE2 的缺失行插入值。匹配条件为每个缺失行查找时间最接近的行，并且 ON 条件确保根据匹配设备 IDs 插值。
缺失行的最接近行是带 2024-03-07 00:01:16.000 时间戳的行，假设 >= 在匹配条件中指定，如此示例中所示。
INSERT INTO sensor_data_ts(device_id, timestamp, temperature, vibration, motor_rpm)
SELECT t.device_id, t.ts, s.temperature, s.vibration, s.motor_rpm
FROM continuous_timestamps t
ASOF JOIN sensor_data_ts s
MATCH_CONDITION(t.ts >= s.timestamp)
ON t.device_id = s.device_id
WHERE TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP < ('2024-03-07 00:01:21');
INSERT 语句通过 ASOF JOIN 操作选择五行，并将它们插入 sensor_data_ts 表。
+-------------------------+
| number of rows inserted |
|-------------------------|
| 5 |
+-------------------------+
要检查插值的结果，请从 sensor_data_ts 表中选择这五行及紧随其前和其后的两行。请注意，五个插值行已为 2024-03-07 00:01:15.000 行中记录的 temperature、vibration 和 motor_rpm 列选择了相同的值。插值成功。
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:16.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:17.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:18.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:19.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:20.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
为时间序列数据应用基于 ML 的函数¶
您可以使用 ML 函数训练模型，对时间序列数据进行预测性分析：
预测使用历史时间序列数据对未来数据进行预测。给定记录的时间序列，其中包含过去日期和时间的实际观测值，则 ML 模型会预测未来日期和时间的观测值可能是多少。
异常检测可识别异常值，即偏离预期范围的数据点。在时间序列的上下文中，异常值是指在相似的时间间隔内比其他测量值大得多或小得多的测量值。要查找异常值，ML 函数会生成正在检查异常的同一时间段的预测，然后将预测结果与实际数据进行比较。
精辟见解在数据集中找到最重要的维度，从这些维度构建片段，然后检测这些片段中哪些对指标有影响。
备注
出于机器学习目的，时间序列中的时间戳必须表示固定的时间间隔。如有必要，您可以对 TIMESTAMP 列使用 DATE_TRUNC 或 TIME_SLICE 函数，以在训练预测模型时去除不规则性。
时间序列中的异常检测示例¶
以下示例使用只有 30 行的视图来训练异常检测模型。首先将数据生成到表中，然后基于表创建视图。视图不是必需的（您可以使用表来训练模型），但视图选项为您提供了一些灵活性，可以在不更新源数据的情况下，以不同的行计数迭代方式训练模型。
备注
如果您自行运行此示例，输出将不完全匹配，因为 sensor_data_30_rows 表加载了随机生成的值。
CREATE OR REPLACE TABLE sensor_data_30_rows (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_30_rows (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE3', timestamp,
UNIFORM(30.2345, 36.3456, RANDOM()), --
UNIFORM(0.4000, 0.4718, RANDOM()), --
UNIFORM(1510, 1625, RANDOM()) --
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 30))
);
CREATE OR REPLACE VIEW sensor_data_view AS SELECT * FROM sensor_data_30_rows;
现在创建模型：
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature',
LABEL_COLNAME => '');
+---------------------------------------------+
| status |
|---------------------------------------------|
| Instance SENSOR_MODEL successfully created. |
+---------------------------------------------+
成功构建模型后，调用 <model_name>!DETECT_ANOMALIES 方法以检测指定测试数据集中的异常值。测试数据中的时间戳必须按时间顺序遵循训练数据中的时间戳，但训练数据和测试数据之间的时间间隔不能太大。例如，如果您有每秒的时间戳，则不要使用比训练数据早数百万秒的测试数据。
此示例使用另一个表作为测试数据，其中只有三行。这些行的时间戳与训练数据中的时间戳密切相关。
CREATE OR REPLACE TABLE sensor_data_device3 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_device3 VALUES
('DEVICE3','2024-03-01 00:00:30.000',36.0422,0.4226,1560),
('DEVICE3','2024-03-01 00:00:31.000',36.1519,0.4341,1515),
('DEVICE3','2024-03-01 00:00:32.000',36.1524,0.4321,1591);
CALL sensor_model!DETECT_ANOMALIES(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature'
);
当异常检测调用完成时，它将返回类似于以下内容的输出：
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
| TS | Y | FORECAST | LOWER_BOUND | UPPER_BOUND | IS_ANOMALY | PERCENTILE | DISTANCE |
|-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------|
| 2024-03-01 00:00:30.000 | 36.0422 | 30.809998241 | 25.583156942 | 36.036839539 | True | 0.9950380683 | 2.578470982 |
| 2024-03-01 00:00:31.000 | 36.1519 | 32.559470456 | 27.332629158 | 37.786311755 | False | 0.961667911 | 1.770378085 |
| 2024-03-01 00:00:32.000 | 36.1524 | 32.205610776 | 26.978769478 | 37.432452075 | False | 0.9741130751 | 1.945009377 |
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
TS 和 Y 列返回测试数据中的时间戳和温度值。在这个非常小的测试用例中，函数发现了一个异常 (IS_ANOMALY=True)。有关输出列的更多信息，请参阅 功能说明 中的“返回”部分。
创建 sensor_data_ts 表¶
如果要测试本部分中查询 sensor_data_ts 表的示例，您可以通过运行以下 SQL 脚本来创建和加载此表的副本。该脚本通过调用 UNIFORM、RANDOM 和 GENERATOR 函数，为传感器读数生成一个月的合成数据；因此，表副本不会返回相同的结果。读数将在相同的范围内，但它们不会相同。
CREATE OR REPLACE TABLE sensor_data_device1 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE1', timestamp,
UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
CREATE OR REPLACE TABLE sensor_data_device2 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device2 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE2', timestamp,
UNIFORM(24.6642, 36.3107, RANDOM()), -- Temperature range in °C
UNIFORM(0.2876, 0.3333, RANDOM()), -- Vibration range in mm/s
UNIFORM(1425, 1505, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
INSERT INTO sensor_data_device1 SELECT * FROM sensor_data_device2;
DROP TABLE IF EXISTS sensor_data_ts;
ALTER TABLE sensor_data_device1 rename to sensor_data_ts;
DROP TABLE sensor_data_device2;
SELECT COUNT(*) FROM sensor_data_ts; -- verify row count = 5356800
创建 heavy_weather 表¶
以下脚本创建并加载 heavy_weather 表，该表用于 MAX_BY 函数的示例中。该表包含 2021 年最后一周加利福尼亚州城市的 55 行降雪降水记录。
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;