问题描述

使用 Postgres 11.6，我正在尝试分析一些事件数据。目标是找到具有特定名称的所有事件的持续时间，然后将每个事件分成大小均匀的存储桶。我们正在寻找特定事件“聚集”的任何时间。我正在编辑我的问题，因为具体情况可能会掩盖我要问的问题。

简单的例子

问题是“你如何按一个值对行进行分组，然后按频率将出现的次数分成每个桶的计数和平均值。” 这是一个带有四舍五入平均值的手工玩具示例：

带值的月份，这里的每个数字代表一行。

Jan    12    24    60    150    320    488                        
Feb     8    16    40    100    220                            
Mar     4     8    20    310

总体数据

Month    Count    Avg    Min    Max                                
Jan      6        176    12    488                                
Feb      5         77     8    220                                
Mar      4         86     4    310

相同的原始数据，但包含更多的数据，包括重复值和更广泛的范围。

Jan    12    12    12    12    24    24    60     60    150    320     488    500
Feb     8     8     8     8     8    16    40    100    220    440    1100    
Mar     4     8     8     8     8    20    20     20     20    310

总体数据

Month    Count    Avg    Min     Max                                
Jan      12       140    12      500                                
Feb      11       178     8     1100                                
Mar      10        43     4      310

一组数据的模型分成 3 个桶

Month    Count    Avg    Min    Max    Bucket                            
Jan      4         12     12     12    0                            
Jan      4         42     24     60    1                            
Jan      4        365    150    500    2                            
...and so on for Feb and Mar

我只是在猜测桶在上面的模型中是如何分裂的。

这几乎捕捉到了我正在尝试做的事情。按月份名称分组（在我的实际情况下为 from_to_node），将结果行拆分为桶，然后获取每个桶的min、max、avg 和 count 。它开始听起来像一个支点（？）

真实表设置

这是我要获取的表的结构：

CREATE TABLE IF NOT EXISTS data.edge_event (
    id uuid,
    inv_id uuid,
    facility_id uuid,
    from_node citext,
    to_node citext,
    from_to_node citext,
    from_node_dts timestamp without time zone,
    to_node_dts timestamp without time zone,
    seconds integer,
    cycle_id uuid
);

持续时间以秒为单位预先计算，现在感兴趣的区域只是 from_to_node 名称。所以，把这个例子想象成是公平的

CREATE TABLE IF NOT EXISTS data.edge_event (
    from_to_node citext,
    seconds integer
);

原始数据

在表中，大约 300K 事件行edge_event中有 159 个不同的值。from_to_node有些只在少数edge_event记录中发现，有些则在数千或数万条记录中被发现。这太多了，无法提供一个好的样本。但是为了使问题更容易理解，afrom_to_node可能是

Boxing_Assembly 1256

意思是“这部分从拳击阶段移动到组装阶段需要 1256 秒。” 在这里，我们可能有 10,000 条不同持续时间的“Boxing_Assembly”其他记录。

目标

我们正在寻找两件事from_to_node。对于像 Boxing_Assembly 这样的东西，我正在尝试这样做：

将秒数分类到桶中，比如 20 个桶。这是一个直方图。
对于每个存储桶，获取存储桶内的 edge_event 行数 avg(seconds) min/first_value(seconds) 在存储桶内 max/last_value(seconds)

因此，我们希望绘制持续时间图表以查找集群，然后从任何常见集群中获取原始秒数。

我试过的

我尝试了很多不同的代码，但都没有成功。这似乎是GROUP BY和/或窗口函数的问题。有些东西我没有得到，因为我的结果离目标很远。

我知道我没有提供样本数据，因此很难提供帮助。但我猜我缺少的是一个 ++ 概念。差不多，我想知道如何edge_event按 from_to_node 然后按秒拆分数据。鉴于 from_to_nodes 的巨大范围，我试图根据自己的最小值/最大值单独存储每个。

非常感谢您的帮助。

草案尝试

我开发了一个有点作用的查询，但不完全。这是我原始帖子的编辑，代码损坏。

WITH
min_max AS
(
    SELECT from_to_node,
           min(seconds),
           max(seconds)

     FROM edge_event

 GROUP BY from_to_node
) 

   SELECT edge_event.from_to_node,
           width_bucket (seconds, min_max.min, min_max.max, 99) as bucket, -- Bucket are counted from 0, so 9 gets you 10 buckets, if you have enough data.
          count(*) as frequency,
          min(seconds) as seconds_min,
          max(seconds) as seconds_max,
          max(seconds) - min(seconds) as bucket_width,
          round(avg(seconds)) as seconds_avg

      FROM edge_event
      JOIN min_max ON (min_max.from_to_node = edge_event.from_to_node)

     WHERE min_max.min <> min_max.max AND -- Can't have a bucket with an upper and lower bound that are the same.
             edge_event.from_to_node IN (
                'Boxing_Assembly',
                'Assembly_Waiting For QA')

  GROUP BY edge_event.from_to_node,
             bucket

  ORDER BY from_to_node,
           bucket

我回来的东西看起来不错：

from_to_node            bucket   frequency    seconds_min   seconds_max     bucket_width   seconds_avg
Boxing_Assembly              1         912            17           7052     7035                  3037
Boxing_Assembly              2         226          7058          13937     6879                  9472
Boxing_Assembly              3          41         14151          21058     6907                 16994
Boxing_Assembly              4          16         21149          27657     6508                 23487
Boxing_Assembly              5           4         28926          33896     4970                 30867
Boxing_Assembly              6           1         37094          37094        0                 37094
Boxing_Assembly              7           1         43228          43228        0                 43228
Boxing_Assembly             10           2         63666          64431      765                 64049
Boxing_Assembly             14           1         94881          94881        0                 94881
Boxing_Assembly             16           1         108254        108254        0                108254
Boxing_Assembly             37           1         257226        257226        0                257226
Boxing_Assembly             40           1         275140        275140        0                275140
Boxing_Assembly             68           1         471727        471727        0                471727
Boxing_Assembly            100           1         696732        696732        0                696732
Assembly_Waiting For QA      1       41875              1         18971    18970                   726
Assembly_Waiting For QA      9           1         207457        207457        0                207457
Assembly_Waiting For QA     15           1         336711        336711        0                336711
Assembly_Waiting For QA     38           1         906519        906519        0                906519
Assembly_Waiting For QA    100           1        2369669       2369669        0               2369669

这里的一个问题是桶的大小不均匀......它们看起来有点奇怪。我还尝试指定 10、20 或 100 个存储桶，并得到类似的结果。我希望有一种更好的方法可以将数据分配到我丢失的存储桶中，并且有一种方法可以使用零入口存储桶而不是间隙。

标签： postgresqlpivot-tablegroupinghistogramwindow-functions

postgresql - 在 Postgres 11.6 中按频率将数据分组到桶中