PostgresSQL中数字之间的平均数量

debugcn 发表于 Dev

乔达

我有一个这样的数据库：

ACTIONS
id   day   name
1    4     Bill
2    8     Susan
3    10    Bill
4    12    Bill
5    18    Susan
6    22    John

我想获得按名称分组的2条记录之间的平均天数或延迟时间。

账单计算步骤示例

10-4 = 6之间的天数
12-10之间的天数= 2
平均值= 4

Susan计算步骤示例

18-8 = 10之间的天数
平均值= 10

由于John只有一条记录，因此没有时间间隔，因此应该返回0或null，这两种方法都可以。

因此，我正在寻找编写执行这些计算步骤并返回以下数据集的查询：

RESULTS
name       average_days_between
Bill       4
Susan      10
John       null

我能够编写一个脚本，该脚本遍历每条记录并一次平均一次计算，但是生成大量记录需要花费很长时间。

是否可以编写PostgreSQL查询来生成这样的结果集？

迈克·欧根尼克

该lag()窗口功能会为你做到这一点。如果太慢，则将intervalsCTE重写为子查询。

with actions (id, day, name) as (
  values (1, 4, 'Bill'),
         (2, 8, 'Susan'),
         (3, 10, 'Bill'),
         (4, 12, 'Bill'),
         (5, 18, 'Susan'),
         (6, 22, 'John')
), intervals as (
  select name,
         day -
           lag(day)
             over (partition by name
                       order by day) as latency
  from actions
)
select name,
       avg(latency) as avg_latency,
       count(*) as observations
  from intervals
 where latency is not null
 group by name
 order by name;

┌───────┬─────────────────────┬──────────────┐
│ name  │     avg_latency     │ observations │
├───────┼─────────────────────┼──────────────┤
│ Bill  │  4.0000000000000000 │            2 │
│ Susan │ 10.0000000000000000 │            1 │
└───────┴─────────────────────┴──────────────┘
(2 rows)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。