postgresql 如何根据应用于两列的值范围条件优化数据聚合

xjreopfe 于 2023-06-29 发布在 PostgreSQL

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

我在PostgreSQL 10.19中有一个particles表，看起来像这样：

CREATE TABLE particles (
   particle_diameter REAL, 
   particle_speed REAL
);

INSERT INTO particles VALUES
   (0.35, 0.74), 
   (0.57, 2.63), 
   (0.27, 1.05), 
   (0.65, 2.33);

我想要的是按直径范围（即每个颗粒具有例如0和0.2mm、0.2和0.4mm等之间的直径）和速度范围。我想生成一系列的直径范围，和一系列的速度范围和最后计数粒子的数量为每个双直径范围和速度范围。
到目前为止，我设法使用这个查询获得了所需的结果：

WITH speed_series AS (
   SELECT generate_series(-1, 19.8, 0.2) AS speed_from
), speed_range AS (
   SELECT speed_from, (speed_from + 0.2) AS speed_to FROM speed_series
), diameter_series AS (
   SELECT generate_series(0, 9.8, 0.2) AS diameter_from
), diameter_range AS (
   SELECT 
      diameter_from, (diameter_from + 0.2) AS diameter_to, speed_from, speed_to
   FROM diameter_series, speed_range
)
SELECT
   diameter_from,
   diameter_to,
   speed_from,
   speed_to,
   (SELECT 
       COUNT(particle_diameter) 
    FROM particles
    WHERE particle_diameter BETWEEN diameter_from AND diameter_to
    AND particle_speed BETWEEN speed_from AND speed_to
   )
FROM diameter_range;

你可以在这里探索：db<>fiddle
在一个相对较小的数据集（约30k条记录）上，执行此查询需要一分钟以上。所以我的问题是
有没有一种方法可以重写这个查询，使其更高效、更省时？

postgresql

来源：https://stackoverflow.com/questions/76563572/how-to-optimize-data-aggregation-based-on-value-range-conditions-applied-on-two

1条答案

按热度按时间

eoigrqb61#

我会在这里尝试width_bucket（）或floor（）：

with b as (
  select width_bucket(particle_diameter, 0, 10, 50) pd,
         width_bucket(particle_speed, -1, 20, 105) ps
  from particles)
select pd * .2 - .2 diam_from, pd * .2 diam_to, 
       ps * .2 - 1.2 speed_from, ps * .2 - 1 speed_to,  
       count(1) cnt 
from b group by pd, ps

当然，如果你需要这些零行，你可以加入生成的序列：

with b as (
  select width_bucket(particle_diameter, 0, 10, 50) pd,
         width_bucket(particle_speed, -1, 20, 105) ps
  from particles)
select d diam_from, d+.2 diam_to, s speed_from, s+.2 speed_to, coalesce(t.cnt, 0) cnt 
from (select generate_series(0, 9.8, 0.2)) as dm(d)
cross join (select generate_series(-1, 19.8, 0.2)) AS sp(s)
left join (
    select pd * .2 - .2 diam_from, ps * .2 - 1.2 speed_from, count(1) cnt 
    from b group by pd, ps) t
  on dm.d = t.diam_from and sp.s = t.speed_from

dbfiddle demo

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 如何根据应用于两列的值范围条件优化数据聚合

1条答案

相关问题

热门标签

最新问答