sql - 删除列和列计数的冗余函数调用

Question

问题

在下面的查询中，plr_stations 被调用了两次:

1. 一次限制WHERE子句；和
2. 一次计算它返回的结果数。

代码类似于:

  SELECT
 m.*,
 s.*,
 (
  SELECT
    count(1)
  FROM
    climate.plr_stations('48.5146','-123.4447')
 ) AS count_stations
  FROM 
 climate.station s,
 climate.measurement m,
 (
  SELECT
    id
  FROM
    climate.plr_stations('48.5146','-123.4447')
 ) stations
  WHERE
    s.applicable AND
    s.id = stations.id AND
    m.station_id = s.id AND ...

此查询的结果然后由日期查询聚合。

解决方案

使用函数调用的结果填充临时表或数组变量。

更新 #1

如果函数调用的参数定义的球形多边形内有太多站，函数调用将随机选择一个站样本。

更新 #2

启动完整查询的日期查询聚合如下所示:

        SELECT 
          extract(YEAR FROM m.taken) AS year_taken,
          avg(m.amount) AS amount,
          count(m.amount) AS count_measurements,
          md.count_stations,
          min(md.elevation) AS elevation_min,
          max(md.elevation) AS elevation_max
        FROM
          climate.measurement m, (
          SELECT
            m.*,
            s.*, ...

问题

还有什么方法可以消除冗余调用？

谢谢。

Answer 1

怀疑它会折叠，因为它不应该被标记为 IMMUTABLE(如果我理解这样的意图)。

沿着这条线的东西应该工作...取决于您的要求...

with R_stations as (
SELECT
     id,   
     count(1) over () c
      FROM
        climate.plr_stations('48.5146','-123.4447')
)
     SELECT
     m.*,
     s.*,
     stations.c count_stations
      FROM 
     climate.station s,
     climate.measurement m,
     R_stations stations
      WHERE
        s.applicable AND
        s.id = stations.id AND
        m.station_id = s.id AND ...

但考虑到您的聚合可能更容易做到这一点......

 SELECT
 m.*,
 s.*,
 stations.c count_stations
  FROM 
 climate.station s,
 climate.measurement m,
 (SELECT
 id,   
 count(1) over () c
  FROM
    climate.plr_stations('48.5146','-123.4447')

) stations
      WHERE
        s.applicable AND
        s.id = stations.id AND
        m.station_id = s.id AND ...