Sql 根据现有时间序列中的间隔为每行生成新的 UUID

发布于06月24日

我有一个带有时间戳的table1，按添加的"时段ID"分组:

Period ID (uuid)    Created At (timestamptz)
  A                 2017-04-11 11:13:47.997+00
  A                 2017-04-11 14:42:51.843+00
  B                 2017-05-21 15:18:12.973+00
  B                 2017-05-21 15:28:41.054+00
  B                 2017-05-21 15:28:57.577+00
  C                 2017-06-11 22:48:00.637+00
  D                 2017-07-15 14:45:52.343+00
  D                 2017-07-15 14:47:53.343+00
  E                 2017-08-25 16:22:57.612+00
  F                 2017-09-16 00:10:18.577+00

我需要创建一个新ID"Period ID 2"作为Period ID的子集，以便:

UUID填充在新列"时段ID 2"的第一行中.
for each 不同的时段ID值生成一个新的时段ID 2(例如:所有时段ID为"A"的时间戳可以具有相同的"时段ID 2"，时段ID为"B"的时间戳可以具有相同的"时段ID 2".但A和B的"时段ID 2"不能相同)
对于具有相同"周期ID"的记录，如果连续时间戳之间的差异超过10分钟，则应生成新的"周期ID 2".(例如:对于"Period ID"=B的记录，第一个和第二个时间戳之间的间隔大于10分钟，因此将为第二个值生成新的"Period ID 2".第二个和第三个记录之间的间隔小于10分钟，因此第二个和第三个记录将具有相同的"Period ID 2").

因此，输出应如下所示:

Period ID (uuid)    Created At (timestamptz)    Period2 (uuid)
  A                 2017-04-11 11:13:47.997+00      1
  A                 2017-04-11 14:42:51.843+00      2
  B                 2017-05-21 15:18:12.973+00      3
  B                 2017-05-21 15:28:41.054+00      4
  B                 2017-05-21 15:28:57.577+00      4
  C                 2017-06-11 22:48:00.637+00      5
  D                 2017-07-15 14:45:52.343+00      6
  D                 2017-07-15 14:47:53.343+00      6
  E                 2017-08-25 16:22:57.612+00      7
  F                 2017-09-16 00:10:18.577+00      8

我可以添加列，但我不确定如何填充它.我查看了以下流程:ALTER TABLE、CREATE FUNCTION(用于触发功能)、CREATE TRIGGER、...但我无法正确地查询它.有没有其他方法来解决这个问题？

SELECT period_id, created_at, md5(period_id::text || grp)::uuid AS period_id2 FROM ( SELECT *, count(*) FILTER (WHERE step) OVER (PARTITION BY period_id ORDER BY created_at) AS grp FROM ( SELECT *, (created_at - lag(created_at) OVER (PARTITION BY period_id ORDER BY created_at)) > interval '10 min' AS step FROM tbl ) sub1 ) sub2;

SELECT period_id, created_at , md5(period_id::text || count(*) FILTER (WHERE step) OVER (PARTITION BY period_id ORDER BY created_at))::uuid AS period_id2 FROM ( SELECT *, (created_at - lag(created_at) OVER (PARTITION BY period_id ORDER BY created_at)) > interval '10 min' AS step FROM tbl ) sub;

CREATE TABLE tbl2 AS SELECT period_id, created_at , md5(period_id::text || count(*) FILTER (WHERE step) OVER (PARTITION BY period_id ORDER BY created_at))::uuid AS period_id2 FROM ( SELECT *, (created_at - lag(created_at) OVER (PARTITION BY period_id ORDER BY created_at)) > interval '10 min' AS step FROM tbl ) sub;

Sql 根据现有时间序列中的间隔为每行生成新的 UUID

推荐答案

是否作为表保留？

Sql相关问答推荐

创建每小时重置的序列号

Oracle SQL中的累计总数

如何更改函数返回的列名？

有没有一种正确的方法来利用SQL UNION来从三个潜在查询中 Select 最大值？

Oracle SQL-将结果列在单行中

如果元素包含通过SQL指定的字符串，则过滤掉数组元素

如何从JSON数组中 Select 多个值

雅典娜嵌套Json提取液

按分隔符和总和分析字符串

多条件SQL排序行为

如何用HeidiSQL在Firebird中设置超时？

如何使用聚合连接两个表

如何在Hive SQL中分别按多列进行分组？

如何在 SQL 中将两行(或多行)jsonb 数组合并为一行

SQL Server：将JSON对象数组转换为表格格式

正则表达式忽略特定数据部分的分隔符

为 sqlite 全文搜索 (fts) 创建触发器时出现虚拟表的不安全使用

SQL：获取连接表的第一个项目

snowflake插入覆盖行为

在 PostgreSQL 中，如何将数组中的每个元素用作另一个表中的键？