postgresql 如何在Postgres sql中按相似的整数值分组？

9q78igpj 于 2023-01-30 发布在 PostgreSQL

关注(0)|答案(1)|浏览(174)

我有一个非常简单的数据库表，其中一个新的条目是插入每次扫描产品（RFID扫描仪）。
扫描表：
| ID（PK）|产品ID（FK）|创建时间|
| - ------|- ------|- ------|
| 1个|1个|2023年1月26日10时39分00秒|
| 第二章|第二章|2023年1月26日10时39分02秒|
| 三个|三个|2023年1月26日10时39分04秒|
| 四个|四个|2023年1月26日10时47分00秒|
我的目标是按照产品id被扫描的时间，以指定的容差（以秒为单位）对产品id进行聚类，因此，例如，对于我的表中的条目和10秒的容差，所需的结果将是
| 产品ID|
| - ------|
| {1、2、3}|
| {4} |
我解决这个问题的第一次尝试是这样的：

SELECT ARRAY_AGG(DISTINCT Product_ID) FROM scans GROUP BY ROUND(EXTRACT(EPOCH FROM created_at) / 10);

这种方法有点效果，但在边缘情况下，例如，当一个产品在第19秒扫描，另一个产品在第21秒扫描时，它不会被分组在一起，尽管它应该被分组在一起。
解决这个问题的更好、更可靠的方法是什么？

postgresql

来源：https://stackoverflow.com/questions/75244503/how-to-group-by-similar-integer-values-in-postgres-sql

1条答案

按热度按时间

dgtucam11#

如果两行之间的时间超过10秒，我将假设组是分开的。

create table scans(ID int,  Product_ID int, Created_At TimeStamp);
insert into scans values
 (1,    1,cast('2023-01-26 10:39:00.000' as TimeStamp))
,(2,    2,cast('2023-01-26 10:39:02.000' as TimeStamp))
,(3,    3,cast('2023-01-26 10:39:11.000' as TimeStamp))
,(4,    4,cast('2023-01-26 10:47:00.000' as TimeStamp))
;

计算当前行和前一行之间的时间差。当时间差大于"10秒"时，开始新的扫描组。

with ScansDif as(
  select * 
    ,Created_At-lag(Created_At,1,Created_At)over(order by Created_At) dif
  from scans
)
,ScansGroup as(
  select * 
     ,sum(case when dif>cast('10'||' second' as interval) then 1 else 0 end)
          over(order by Created_At rows unbounded preceding) grN
  from ScansDif
)
SELECT ARRAY_AGG(DISTINCT Product_ID) 
FROM ScansGroup 
GROUP BY grn

组别编号
| 身份证|产品标识|创建时间|差异|格林|
| - ------|- ------|- ------|- ------|- ------|
| 1个|1个|2023年1月26日10时39分|00点00分|无|
| 第二章|第二章|2023年1月26日10时39分02秒|00时00分02秒|无|
| 三个|三个|2023年1月26日10时39分11秒|00时00分09秒|无|
| 四个|四个|2023年1月26日10时47分|00时07分56秒|1个|
组0的第一行和最后一行之间的时间差为00：11。结果
| 数组聚合|
| - ------|
| {1、2、3}|
| {4} |
Demo

赞(0）回复(0）举报 2023-01-30

我来回答

postgresql 如何在Postgres sql中按相似的整数值分组？

1条答案

相关问题

热门标签

最新问答