使用 PostgreSQL generate_series() 生成更真实的样本时间序列数据

Postgredaxiang

582人浏览 · 2022-08-15 06:20:29

Postgredaxiang · 2022-08-15 06:20:29 发布

在系列的第 1 部分中,我们回顾了generate_series()的工作原理,包括通过称为 CROSS(或笛卡尔)JOIN 的功能将多个系列连接到更大的时间序列数据表中的能力。我们通过向您展示如何快速计算查询将产生的行数并修改generate_series()的参数以微调数据的大小和形状来结束第一篇文章。

但是,我们在第一篇文章末尾可以生成的数据存在一个问题。我们能够生成的数据非常基础,也不是很现实。不费吹灰之力,使用像random()这样的函数来生成值并不能精确控制生成的数字,因此数据仍然比我们想要的更假。

这第二篇文章将展示一些方法来创建超出一列或两列随机十进制值的更逼真的数据。继续阅读以了解更多信息。

在接下来的几周内,本博客系列的第 3 部分将添加一个最终工具 - 将下面的数据格式化技术与其他方程和关系数据相结合,将您的示例时间序列输出塑造成更类似于现实生活应用程序的东西.

在本系列结束时,您将准备好测试 TimescaleDB 提供的几乎所有功能,并为您的测试和演示创建快速数据集!

简要回顾 generate_series()

在第一篇文章中,我们演示了generate_series()(一个集合返回函数)如何基于一系列数值或日期快速创建一个数据集。生成的数据本质上是一个内存表,可以快速创建大量样本数据。

-- create a series of values, 1 through 5, incrementing by 1
SELECT * FROM generate_series(1,5);

generate_series|
--------------------|
              1|
              2|
              3|
              4|
              5|


-- generate a series of timestamps, incrementing by 1 hour
SELECT * from generate_series('2021-01-01','2021-01-02', INTERVAL '1 hour');

    generate_series     
-----------------------------
 2021-01-01 00:00:00+00
 2021-01-01 01:00:00+00
 2021-01-01 02:00:00+00
 2021-01-01 03:00:00+00
 2021-01-01 04:00:00+00

进入全屏模式退出全屏模式

然后,我们讨论了当我们将各种集合(以及一些值返回函数)连接在一起以创建两个集合的倍数时,数据如何迅速变得更加复杂。

第一篇文章中的这个示例加入了时间戳集、数字集和random()函数,随着时间的推移为四个假设备创建假 CPU 数据。

-- there is an implicit CROSS JOIN between the two generate_series() sets
SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series('2021-01-01 00:00:00','2021-01-01 04:00:00',INTERVAL '1 hour') as time, 
generate_series(1,4) device_id;


time               |device_id|cpu_usage          |
------------------------+---------+-------------------+
2021-01-01 00:00:00|        1|0.35415126479989567|
2021-01-01 01:00:00|        1| 14.013393572770028|
2021-01-01 02:00:00|        1|   88.5015939122006|
2021-01-01 03:00:00|        1|  97.49037810105996|
2021-01-01 04:00:00|        1|  50.22781125586846|
2021-01-01 00:00:00|        2|  46.41196423062297|
2021-01-01 01:00:00|        2|  74.39903569177027|
2021-01-01 02:00:00|        2|  85.44087332221935|
2021-01-01 03:00:00|        2|  4.329394730750735|
2021-01-01 04:00:00|        2| 54.645873866589056|
2021-01-01 00:00:00|        3|  63.01888063314749|
2021-01-01 01:00:00|        3|  21.70606884856987|
2021-01-01 02:00:00|        3|  32.47610779097485|
2021-01-01 03:00:00|        3| 47.565982341726354|
2021-01-01 04:00:00|        3|  64.34867263419619|
2021-01-01 00:00:00|        4|   78.1768041898232|
2021-01-01 01:00:00|        4|  84.51505102850199|
2021-01-01 02:00:00|        4| 24.029611792753514|
2021-01-01 03:00:00|        4|  17.08996115345549|
2021-01-01 04:00:00|        4| 29.642690955760997|

进入全屏模式退出全屏模式

最后,我们讨论了如何根据时间范围、时间戳之间的间隔以及创建假数据的“事物”数量来计算查询将生成的总行数。

读数范围

间隔长度

“设备”数量

总行数

1年

1小时

35,040

1年

10分钟

100

5,256,000

6个月

5分钟

1,000

52,560,000

尽管如此,主要问题仍然是。即使我们可以用几行 SQL 生成 5000 万行数据,但我们生成的数据也不是很现实。这都是随机数,有很多小数和最小的变化。

正如我们在上面的查询中看到的(生成虚假 CPU 数据),我们添加到 SELECT 查询的任何数据列都将添加到结果集的每一行中。如果我们添加静态文本(如“你好,时间刻度!”),该文本会在每一行重复。同样,对于最终集合的每一行,将调用一次作为列值的函数。

这就是 CPU 数据示例中的random()函数发生的情况。每一行都有不同的值,因为该函数是为每一行生成的数据单独调用的。我们可以利用这一点来开始使数据看起来更真实。

通过更多的思考和自定义 PostgreSQL 函数,我们可以开始将我们的示例数据“栩栩如生”。

什么是真实数据?

这是确保我们在同一页面上的好时机。我所说的“现实”数据是什么意思?

使用我们已经讨论过的基本技术,您可以快速创建大量数据。然而,在大多数情况下,您通常知道您尝试探索的数据是什么样的。它可能不是一堆十进制或整数值。即使您尝试模拟的数据只是数值,它们也可能具有有效范围并且可能具有可预测的频率。

以我们上面的 CPU 和温度数据的简单示例为例。只有两个字段,如果我们希望生成的数据感觉更真实,我们可以做出一些选择。

CPU 是百分比吗?在 100% 中,还是我们代表可以呈现为 200%、400% 或 800% 的多核 CPU?
温度是以华氏还是摄氏度为单位测量的?每个单元中 CPU 温度的合理值是多少?我们在模式中是用小数还是整数来存储温度?
如果我们在模式中添加一个“注释”字段以用于我们的监控软件可能会不时添加到读数中的消息怎么办?每次阅读都会有一个注释,或者只是在达到阈值时?我们需要以某种方式复制每个小时的顶部是否有特殊的诊断消息?

单独使用random()和静态文本可以让我们生成具有多列的大量数据,但在测试数据库中的功能时它不会很有趣或有用。

这是本系列第二篇和第三篇文章的目标,帮助您生成看起来更像真实事物的样本数据,而无需太多额外工作。是的,它仍然是随机的,但在您探索时间序列数据的各个方面时,它会在限制内帮助您感觉与数据的联系更加紧密。

而且,通过使用函数,所有的工作都可以轻松地从一个表重用到另一个表。

先走后跑

在下面的每个示例中,我们将按照我们在初等数学课中学到的方法来处理我们的解决方案:展示你的工作!如果不先使用简单的 SQL 语句,通常很难在 PostgreSQL 中创建函数或过程。这抽象出一开始就考虑函数输入和输出的需要,以便我们可以专注于 SQL 如何工作以产生我们想要的值。

因此,下面的示例向您展示了如何在将 SQL 转换为可重用的函数之前,首先在 SELECT 语句中获取值(随机数、文本、JSON 等)。这种迭代过程是学习 PostgreSQL 特性的好方法,特别是当它与generate_series()结合使用时。

所以,把一只脚放在另一只脚前面,让我们开始创建更好的样本数据。

创建更真实的数字

在时间序列数据中,数值通常是最常见的数据类型。使用像random()这样没有任何其他格式的函数会创建非常......嗯......带有很多小数点的随机(和精确)数字。虽然它有效,但这些值是不现实的。大多数用户和设备不会将 CPU 使用情况跟踪到小数点后 12 位以上。我们需要一种方法来操纵和约束查询中返回的最终值。

对于数值,PostgreSQL 提供了许多内置函数来修改输出。在许多情况下,将round()和floor()与基本算术一起使用可以快速开始以更适合您的架构和用例的方式塑造数据。

让我们修改用于获取设备指标、返回 CPU 和温度值的示例查询。我们希望更新查询以确保为每一列“自定义”数据值,返回特定范围和精度内的值。因此,我们需要对 SELECT 查询中的每个数值应用一个标准公式。

Final value = random() * (max allowed value - min allowed value) + min allowed value

进入全屏模式退出全屏模式

该等式将始终在最小值和最大值之间(包括在内)生成一个十进制值。如果random()返回值 1,则最终输出将等于最大值。如果random()返回值 0,则结果将等于最小值。random()返回的任何其他数字都会在最小值和最大值之间产生一些输出。

根据我们想要的是小数还是整数值,我们可以进一步格式化公式的“最终值”为round()和floor()。

此示例为 10 台设备每分钟生成一个读数,持续一小时。 cpu 值将始终介于 3 到 100 之间(精度为四位小数),温度始终是 28 到 83 之间的整数。

SELECT
  time,
  device_id,
  round((random()* (100-3) + 3)::NUMERIC, 4) AS cpu,
  floor(random()* (83-28) + 28)::INTEGER AS tempc
FROM 
    generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id;


time                         |device_id|cpu    |tempc        |
----------------------------------+---------+-------+-------------+
2021-11-03 12:47:01.181 -0400|        1|53.7301|           61|
2021-11-03 12:48:01.181 -0400|        1|34.7655|           46|
2021-11-03 12:49:01.181 -0400|        1|78.6849|           44|
2021-11-03 12:50:01.181 -0400|        1|95.5484|           64|
2021-11-03 12:51:01.181 -0400|        1|86.3073|           82|
…|...|...|...

进入全屏模式退出全屏模式

通过使用我们的简单公式并正确格式化结果,查询产生了我们想要的“精选”输出(虽然是随机的)。

函数的力量

但是这里也有一些令人失望的地方,不是吗?为每个值重复输入该公式——试图记住参数的顺序以及何时需要转换一个值——很快就会变得乏味。毕竟,你只剩下这么多击键。

解决方案是创建和使用 PostgreSQL 函数,这些函数可以接受我们需要的输入、进行正确的计算并返回我们想要的格式化值。有很多方法可以在函数中完成这样的计算。将此示例用作您学习和探索的起点。

注意:在此示例中,我选择将此函数的值作为numeric数据类型返回,因为它可以返回看起来像整数(无小数)或浮点数(小数)的值。只要将返回值插入到具有预期模式的表中,这是一个“技巧”,可以直观地看到我们所期望的 - 整数或浮点数。一般来说,numeric数据类型在查询和压缩等特性中的性能通常会更差,因为numeric值在内部是如何表示的。我们建议在架构设计中尽可能避免使用numeric类型,而是更喜欢浮点或整数类型。

/*
 * Function to create a random numeric value between two numbers
 * 
 * NOTICE: We are using the type of 'numeric' in this function in order
 * to visually return values that look like integers (no decimals) and 
 * floats (with decimals). However, if inserted into a table, the assumption
 * is that the appropriate column type is used. The `numeric` type is often
 * not the correct or most efficient type for storing numbers in a table.
 */
CREATE OR REPLACE FUNCTION random_between(min_val numeric, max_val numeric, round_to int=0) 
   RETURNS numeric AS
$$
 DECLARE
    value NUMERIC = random()* (min_val - max_val) + max_val;
BEGIN
   IF round_to = 0 THEN 
     RETURN floor(value);
   ELSE 
     RETURN round(value,round_to);
   END IF;
END
$$ language 'plpgsql';

进入全屏模式退出全屏模式

此示例函数使用提供的最小值和最大值,应用我们之前讨论过的“范围”公式,最后返回一个numeric值,该值要么有小数(到指定的位数),要么没有。在我们的查询中使用这个函数,我们可以简化为样本数据创建格式化值的过程,并清理 SQL,使其更易于阅读和使用。

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c
FROM 
    generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id;

进入全屏模式退出全屏模式

此查询提供相同格式的输出,但现在重复该过程要容易得多。

创建更逼真的文本

文字呢?到目前为止,在这两篇文章中,我们只讨论了如何生成数字数据。然而,我们都知道,时间序列数据通常不仅仅包含数值。让我们转向另一种常见的数据类型:文本。

时间序列数据通常包含文本值。当您的架构包含存储为文本的日志消息、项目名称或其他识别信息时,我们希望生成感觉更真实的示例文本,即使它是随机的。

让我们考虑前面使用的为一组设备创建 CPU 和温度数据的查询。如果这些设备是真实的,那么它们创建的数据可能包含长度不等的间歇性状态消息。

为了弄清楚如何生成这个随机文本,我们将遵循与之前相同的过程,直接在独立的 SQL 查询中工作,然后将我们的解决方案移动到可重用的函数中。经过一些初步尝试(和充足的谷歌搜索),我想出了这个示例,用于使用定义的字符集生成可变长度的随机文本。与上面的random_between()函数一样,可以根据需要对其进行修改。例如,通过限制字符集和长度,很容易获得唯一的随机十六进制值。

让你的创造力引导你。_

WITH symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 {}')),
w1 AS (
    SELECT string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '') r_text, 'g1' AS idx
    FROM symbols,
generate_series(1,10) as word(chr_idx) -- word length
    GROUP BY idx)
SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  w1.r_text AS note
FROM w1, generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;

time                         |device_id|cpu     |temperature_c|note      |
----------------------------------+---------+--------+-------------+----------+
2021-11-03 16:49:24.218 -0400|        1| 88.3525|           50|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        2| 29.5313|           53|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        3| 97.6065|           70|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        4| 96.2170|           40|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        5| 53.2318|           82|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        6| 73.7244|           56|I}3U}FIsX9|

进入全屏模式退出全屏模式

在这种情况下,更容易在 CTE 内部生成一个随机值,我们可以稍后在查询中引用该值。但是,这种方法有一个问题,很容易在返回的前几行数据中发现。

虽然 CTE 确实创建了 10 个字符的随机文本(继续运行几次以验证),但 CTE 的值每次生成一次,然后缓存,对每一行重复相同的结果。一旦我们将查询转移到一个函数中,我们希望每行看到一个不同的值。

对于生成随机长度的“单词”(或在某些情况下根本没有文本)的第二个示例函数,用户需要为生成的文本的最小和最大长度提供一个整数。经过一些测试,我们还添加了一个简单的随机化功能。

注意我们添加的 IF...THEN 条件。每当生成的数字除以五并且余数为零或一时,该函数都不会返回文本值。这种为输出频率提供随机性的方法没有什么特别之处,因此请随意调整这部分功能以满足您的需要。

/*
 * Function to create random text, of varying length
 */
CREATE OR REPLACE FUNCTION random_text(min_val INT=0, max_val INT=50) 
   RETURNS text AS
$$
DECLARE 
    word_length NUMERIC  = floor(random() * (max_val-min_val) + min_val)::INTEGER;
    random_word TEXT = '';
BEGIN
    -- only if the word length we get has a remainder after being divided by 5. This gives
    -- some randomness to when words are produced or not. Adjust for your tastes.
    IF(word_length % 5) > 1 THEN
    SELECT * INTO random_word FROM (
        WITH symbols(characters) AS (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 '))
        SELECT string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), ''), 'g1' AS idx
        FROM symbols
        JOIN generate_series(1,word_length) AS word(chr_idx) on 1 = 1 -- word length
        group by idx) a;
    END IF;
    RETURN random_word;
END
$$ LANGUAGE 'plpgsql';

进入全屏模式退出全屏模式

当我们使用此函数向示例时间序列查询添加随机文本时,请注意文本的长度(2 到 10 个字符之间)和频率是随机的。

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  random_text(2,10) AS note
FROM generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;

time                         |device_id|cpu     |temperature_c|note     |
----------------------------------+---------+--------+-------------+---------+
2021-11-04 14:17:03.410 -0400|        1| 86.5780|           67|         |
2021-11-04 14:17:03.410 -0400|        2|  3.5370|           76|pCVBp AZ |
2021-11-04 14:17:03.410 -0400|        3| 59.7085|           28|kMrr     |
2021-11-04 14:17:03.410 -0400|        4| 69.6153|           46|3UdA     |
2021-11-04 14:17:03.410 -0400|        5| 33.0906|           56|d0sSUilx |
2021-11-04 14:17:03.410 -0400|        6| 44.2837|           74|         |
2021-11-04 14:17:03.410 -0400|        7| 14.2550|           81|TOgbHOU  |

进入全屏模式退出全屏模式

希望您开始看到一种模式。使用generate_series()和一些自定义函数可以帮助您创建多种形状和大小的时间序列数据。

我们展示了创建更真实的数字和文本数据的方法,因为它们是时间序列数据中使用的主要数据类型。您可能需要使用示例数据生成时间序列数据中是否包含任何其他数据类型?

JSON 值呢?

创建示例 JSON

注意:下面的示例查询创建 JSON 字符串作为输出,目的是将其插入表中以供进一步测试和学习。在 PostgreSQL 中,JSON 字符串数据可以存储在 JSON 或 JSONB 列中,每个列都提供不同的功能来查询和显示 JSON 数据。在大多数情况下,JSONB 是首选的列类型,因为它提供了更高效的存储以及为内容创建索引的能力。主要缺点是 JSON 字符串的实际格式(包括键和值的顺序)没有保留,并且可能难以准确重现。为了更好地理解何时将 JSON 字符串数据存储为一种列类型的差异,请参考 PostgreSQL 文档。

PostgreSQL 多年来一直支持 JSON 和 JSONB 数据类型。在每个主要版本中,使用 JSON 和整体查询性能的功能集都会得到改进。在越来越多的数据模型中,尤其是在涉及 REST 或 Graph API 时,将额外的元信息存储为 JSON 文档可能是有益的。如果需要,数据是可用的,同时有助于对存储在常规列中的序列化数据进行有效查询。

我们在NFT Starter Kit中使用了与此类似的设计模式。用作入门工具包数据源的 OpenSea JSON API 包括每个资产和集合的许多属性和值。许多值对于该教程中的具体分析没有帮助。但是,我们知道 JSON 属性中的某些值可能在未来的分析、教程或演示中很有用。因此,我们将有关资产和集合的其他元数据存储在 JSONB 字段中,以便在需要时对其进行查询。尽管如此,它并没有使其他常见数据(如name和asset_id)的模式设计复杂化。

在 JSON 字段中存储数据也是 IIoT 设备数据等领域的常见做法。工程师通常有一个商定的架构来存储和查询设备生成的指标,然后是一个“自由格式”的 JSON 列,允许工程师发送随着硬件修改或更新而随时间变化的错误或诊断数据。

有几种方法可以将 JSON 数据添加到我们的示例查询中。另一个挑战是 JSON 数据包含键和值,以及多个级别的子对象嵌套的可能性。您采用的方法将取决于您希望 PostgreSQL 函数的复杂程度以及示例数据的最终目标。在此示例中,我们将创建一个函数,该函数接受 JSON 的键数组,并为每个键生成随机数值,无需嵌套。借助用于读取和写入 JSON 字符串的内置 PostgreSQL 函数,从我们的值生成 SQL 中的 JSON 字符串非常简单。 🎉

与本文中的其他示例一样,我们将首先使用 CTE 在独立的 SELECT 查询中生成随机 JSON 文档,以验证结果是否是我们想要的。请记住,我们将观察到之前在独立查询中生成随机文本时遇到的相同问题,因为我们使用的是 CTE。每次查询运行时,JSON 都是随机的,但该字符串会重复用于结果集中的所有行。 CTE 为查询中的每个引用实现一次,而函数为每一行再次调用。正因为如此,在将 SQL 移动到函数中以供以后重用之前,我们不会观察每一行中的随机值。

WITH random_json AS (
SELECT json_object_agg(key, random_between(1,10)) as json_data
    FROM unnest(array['a', 'b']) as u(key))
  SELECT json_data, generate_series(1,5) FROM random_json;

json_data       |generate_series|
---------------------+---------------+
{"a": 6, "b": 2}|              1|
{"a": 6, "b": 2}|              2|
{"a": 6, "b": 2}|              3|
{"a": 6, "b": 2}|              4|
{"a": 6, "b": 2}|              5|

进入全屏模式退出全屏模式

我们可以看到 JSON 数据是使用我们的键 (['a','b']) 创建的,其数字在 1 到 10 之间。我们只需要创建一个函数,它会在每次调用时创建随机 JSON 数据.此函数将始终返回一个 JSON 文档,其中包含我们为演示目的而提供的每个键的数字整数值。如果您需要,请随意增强此功能以返回具有各种数据类型的更复杂的文档。

CREATE OR REPLACE FUNCTION random_json(keys TEXT[]='{"a","b","c"}',min_val NUMERIC = 0, max_val NUMERIC = 10) 
   RETURNS JSON AS
$$
DECLARE 
    random_val NUMERIC  = floor(random() * (max_val-min_val) + min_val)::INTEGER;
    random_json JSON = NULL;
BEGIN
    -- again, this adds some randomness into the results. Remove or modify if this
    -- isn't useful for your situation
    if(random_val % 5) > 1 then
        SELECT * INTO random_json FROM (
            SELECT json_object_agg(key, random_between(min_val,max_val)) as json_data
                FROM unnest(keys) as u(key)
        ) json_val;
    END IF;
    RETURN random_json;
END
$$ LANGUAGE 'plpgsql';

进入全屏模式退出全屏模式

有了random_json()功能,我们可以通过几种方式对其进行测试。首先,我们将简单地直接调用函数,不带任何参数,这将返回一个 JSON 文档,其中包含函数定义中提供的默认键(“a”、“b”、“c”)和从 0 到 10 的值(默认最小值和最大值)。

SELECT random_json();

random_json             |
-----------------------------+
{"a": 7, "b": 3, "c": 8}|

进入全屏模式退出全屏模式

接下来,我们将把它连接到一个generate_series()的小数字集。

SELECT device_id, random_json() FROM generate_series(1,5) device_id;

device_id|random_json              |
--------------+-------------------------+
        1|{"a": 2, "b": 2, "c": 2} |
        2|                         |
        3|{"a": 10, "b": 7, "c": 1}|
        4|                         |
        5|{"a": 7, "b": 1, "c": 0} |

进入全屏模式退出全屏模式

请注意此示例中的两件事。

首先,每一行的数据都不同,这表明函数会为每一行调用并每次产生不同的数值。其次,因为我们保留了与random_text()示例相同的随机输出机制,所以并非每一行都包含 JSON。

最后,让我们将其添加到用于生成我们在本文中使用的设备数据的示例查询中,以了解如何为生成的 JSON 数据提供一组键(“building”和“rack”)。

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  random_text(2,10) AS note,
  random_json(ARRAY['building','rack'],1,20) device_location
FROM generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;


time                         |device_id|cpu     |temperature_c|note     |device_location             |
----------------------------------+---------+--------+-------------+---------+----------------------------+
2021-11-04 16:19:22.991 -0400|        1| 14.7614|           70|CTcX8 2s4|                            |
2021-11-04 16:19:22.991 -0400|        2| 62.2618|           81|x1V      |{"rack": 4, "building": 5}  |
2021-11-04 16:19:22.991 -0400|        3| 10.1214|           50|1PNb     |                            |
2021-11-04 16:19:22.991 -0400|        4| 96.3742|           29|aZpikXGe |{"rack": 12, "building": 4} |
2021-11-04 16:19:22.991 -0400|        5| 22.5327|           30|lM       |{"rack": 2, "building": 3}  |
2021-11-04 16:19:22.991 -0400|        6| 57.9773|           44|         |{"rack": 16, "building": 5} |
...

进入全屏模式退出全屏模式

使用generate_series()、PostgreSQL 函数和一些自定义逻辑创建示例数据的可能性非常多。

放在一起

让我们将所学付诸实践,使用这三个函数创建和插入约 100 万行数据,然后使用超函数time_bucket()、time_bucket_ng()、approx_percentile()和time_weight()对其进行查询。为此,我们将创建两个表:一个是计算机主机列表,第二个是存储有关计算机的虚假时间序列数据的超表。

步骤 1:创建模式和超表

CREATE TABLE host (
    id int PRIMARY KEY,
    host_name TEXT,
    LOCATION jsonb
);

CREATE TABLE host_data (
    date timestamptz NOT NULL,
    host_id int NOT NULL,
    cpu double PRECISION,
    tempc int,
    status TEXT 
);

SELECT create_hypertable('host_data','date');

进入全屏模式退出全屏模式

第二步:生成并插入数据

-- Insert data to create fake hosts
INSERT INTO host
SELECT id, 'host_' || id::TEXT AS name, 
    random_json(ARRAY['building','rack'],1,20) AS LOCATION
FROM generate_series(1,100) AS id;


-- insert ~1.3 million records for the last 3 months
INSERT INTO host_data
SELECT date, host_id,
    random_between(5,100,3) AS cpu,
    random_between(28,90) AS tempc,
    random_text(20,75) AS status
FROM generate_series(now() - INTERVAL '3 months',now(), INTERVAL '10 minutes') AS date,
generate_series(1,100) AS host_id;

进入全屏模式退出全屏模式

第三步:使用`time_bucket()`和`time_bucket_ng()`查询数据

-- Using time_bucket(), query the average CPU and max tempc
SELECT time_bucket('7 days', date) AS bucket, host_name,
    avg(cpu),
    max(tempc)
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '1 month'
GROUP BY 1,2
ORDER BY 1 DESC, 2;


-- try the experimental time_bucket_ng() to query data in month buckets
SELECT timescaledb_experimental.time_bucket_ng('1 month', date) AS bucket, host_name,
    avg(cpu) avg_cpu,
    max(tempc) max_temp
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '3 month'
GROUP BY 1,2
ORDER BY 1 DESC, 2;

进入全屏模式退出全屏模式

第四步:使用工具包超函数查询数据

-- query all host in building 10 for 7 day buckets
-- also try the new percentile approximation function to 
-- get the p75 of data for each 7 day period
SELECT time_bucket('7 days', date) AS bucket, host_name,
    avg(cpu),
    approx_percentile(0.75,percentile_agg(cpu)) p75,
    max(tempc)
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '1 month'
    AND LOCATION -> 'building' = '10'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;



-- To test time-weighted averages, we need to simulate missing
-- some data points in our host_data table. To do this, we'll
-- randomly select ~10% of the rows, and then delete them from the
-- host_data table.
WITH random_delete AS (SELECT date, host_id FROM host_data
     JOIN host ON host_id = id WHERE 
    date > now() - INTERVAL '2 weeks'
    ORDER BY random() LIMIT 20000
)
DELETE FROM host_data hd
USING random_delete rd
WHERE hd.date = rd.date
AND hd.host_id = rd.host_id;


-- Select the daily time-weighted average and regular average
-- of each host for building 10 for the last two weeks.
-- Notice the variation in the two numbers because of the missing data.
SELECT time_bucket('1 day',date) AS bucket,
    host_name,
    average(time_weight('LOCF',date,cpu)) weighted_avg,
    avg(cpu) 
FROM host_data
    JOIN host ON host_data.host_id = host.id
WHERE LOCATION -> 'building' = '10'
AND date > now() - INTERVAL '2 weeks'
GROUP BY 1,2
ORDER BY 1 DESC, 2;

进入全屏模式退出全屏模式

在几行 SQL 中,我们创建了 130 万行数据,并且能够在 TimescaleDB 中测试四个不同的函数,所有这些都无需依赖任何外部源。 💪

不过,您可能会注意到我们host_data表中的值的最后一个问题(即使这些值在本质上并不更现实)。通过使用random()作为我们查询的基础,计算出的数值都倾向于在指定范围内具有相等的分布,这导致值的平均值始终接近中位数。这在统计上是有道理的,但它突出了我们生成的数据的另一个改进领域。在本系列的第三篇文章中,我们将展示一些方法来影响生成的值以提供数据的形状(如果我们需要的话,甚至是一些异常值)。

回顾我们的进展

在使用像 TimescaleDB 这样的数据库或在 PostgreSQL 中测试功能时,生成具有代表性的数据集是 SQL 工具带中的一个有益工具。

在第一篇中,我们学习了如何通过组合多个generate_series()函数的结果集来生成大量数据。使用隐式 CROSS JOIN,最终输出中的总行数是每个集合的乘积。当其中一个数据集包含时间戳时,输出可用于创建时间序列数据以进行测试和查询。

我们最初示例的问题在于,我们生成的实际值是随机的,并且缺乏对其精度的控制——而且所有数据都是数字的。因此,在第二篇文章中,我们演示了如何格式化给定列的数字数据并生成其他类型的随机数据,例如文本和 JSON 文档。我们还在 text 和 JSON 函数中添加了一个示例,该示例创建了为每个列发出值的频率的随机性。

同样,所有这些都是供您使用的构建块示例,创建生成您需要测试的数据类型的函数。

要查看其中一些示例的实际效果,请观看我关于创建真实示例数据的视频:

在本系列的第 3 部分中,我们将演示如何使用本文中的格式化函数以及关系查找表和附加数学函数。了解如何操作生成数据的模式对于可视化时间序列数据和学习分析 PostgreSQL 或 TimescaleDB 函数特别有用。

如果您对使用generate_series()有任何疑问或对 TimescaleDB 有任何疑问,请加入我们的社区 Slack 频道,在大多数情况下,您会在这里找到一个活跃的社区和少数 Timescale 团队。

如果您想尝试使用generate_series()创建更大的样本时间序列数据集并了解 TimescaleDB 的令人兴奋的功能如何工作,请注册 30 天免费试用或在您的实例上安装和管理它。 (您还可以在我们的众多教程之一](https://docs.timescale.com/timescaledb/latest/tutorials/?utm_source=dev-to&utm_medium=blog&utm_campaign=generate-series&utm_content=tutorials-docs)之后通过[了解更多信息。)

PostgreSQL

PostgreSQL社区为您提供最前沿的新闻资讯和知识内容

更多推荐

PostgreSQL 计数查询效率,物化视图 [重复]

问题:PostgreSQL 计数查询效率,物化视图 [重复] 可能重复: PostgreSQL 计数查询优化使用 PostgreSQL 9.2,我们试图弄清楚是否有一种方法可以跟踪查询的结果数量,并以有效的方式返回该数字。这个查询应该每秒执行几次(可能几十到几百甚至几千次)。我们现在的查询看起来像这样,但我们想知道这是否效率低下: -- Get # of rows that do not hav

PostgreSQL

多对多中的唯一性

问题:多对多中的唯一性我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。我相信我有一个典型的多对多关系: CREATE TABLE groups ( id integer PRIMARY KEY); CREATE TABLE elements ( id integer PRIMARY KEY); CREATE TABLE groups_elements

PostgreSQL

Django 与 postgresql - manage.py syncdb 返回错误

问题:Django 与 postgresql - manage.py syncdb 返回错误我从 Django 开始。我设置了一些使用 SQLite 工作的站点,但是在将 DB 引擎更改为 postgresql manage.py syncdb 后返回错误。我已经用谷歌搜索了 2 天,但对我仍然没有任何作用。Postgres 用户 'joe' 具有超级用户权限和本地 'joe ' 数据库存在。