时间戳

首页 > 解决方案 > 使用 width_bucket 获取结果范围

sql - 使用 width_bucket 获取结果范围

问题描述

我正在尝试使用width_bucket并在结果中有一个列与我的存储桶的边界进行查询。有没有一种简单的方法可以做到这一点？

我的查询：

SELECT width_bucket(
            (EXTRACT(epoch FROM S.end - S.start) / 60)::integer, 
            array[0, 15, 30, 45, 60, 75, 90, 1000]
       ) AS buckets,
       count(*)
FROM shipments_site S
WHERE S.deleted IS NULL
GROUP BY buckets
ORDER BY buckets

结果：

buckets | count
1       | 20
2       | 6
3       | 22
4       | 25
5       | 10
6       | 11
7       | 6

我想要得到什么：

buckets | count    | interval
1       | 20       | [0, 15]
2       | 6        | [15, 30]
3       | 22       | [30, 45]
4       | 25       | [45, 60]
5       | 10       | [60, 75]
6       | 11       | [75, 90]
7       | 6        | [90, 1000]

标签： sqlpostgresql

解决方案

这是一种方法：

SELECT v.bucket, count(*),
       CONCAT( (array[0, 15, 30, 45, 60, 75, 90, 1000])[v.bucket], '-',
               (array[0, 15, 30, 45, 60, 75, 90, 1000])[v.bucket + 1]
             ) as bounds
FROM shipments_site S CROSS JOIN LATERAL
     (VALUES (width_bucket((EXTRACT(epoch FROM S.end - S.start) / 60)::integer, 
                           array[0, 15, 30, 45, 60, 75, 90, 1000]
                          )
             )
     ) v(bucket)
WHERE S.deleted IS NULL
GROUP BY v.bucket
ORDER BY v.bucket;

这个想法是使用横向连接来定义bucket. 然后只需索引数组中的存储桶即可获得边界。

如果您不想重复数组，也可以将其包含在FROM子句中：

SELECT v.bucket, count(*),
       CONCAT( (v_ar.ar_bounds)[v.bucket], '-',
               (v_ar.ar_bounds)[v.bucket + 1]
             ) as bounds
FROM shipments_site S CROSS JOIN
     (values (array[0, 15, 30, 45, 60, 75, 90, 1000])
     ) v_ar(ar_bounds) CROSS JOIN LATERAL
     (VALUES (width_bucket((EXTRACT(epoch FROM S.end - S.start) / 60)::integer, 
                           v_ar.ar_bounds
                          )
             )
     ) v(bucket)
WHERE S.deleted IS NULL
GROUP BY v.bucket, v_ar.ar_bounds
ORDER BY v.bucket;

这是一个 db<>fiddle。

推荐阅读