postgresql - 在没有 CROSS JOIN 的情况下计算 Postgres 11.7 中每一行的平均计数

Question

我试图找出将总体平均计数附加到 Postgres 11.7 中每一行的最佳方法。我经常对计数的平均值感兴趣，但发现用 SQL 计算出乎意料地尴尬。我怀疑我错过了一些非常基本的东西。我已经设置了一个玩具桌来使用：

BEGIN:

DROP TABLE IF EXISTS progress CASCADE;

CREATE TABLE IF NOT EXISTS progress (
    package_id     integer     NOT NULL DEFAULT NULL,
    completed_dts  timestamptz NOT NULL DEFAULT NULL,
    step           citext      NOT NULL DEFAULT NULL);

INSERT INTO progress (package_id, completed_dts, step) VALUES
    (1,'2020-01-01 01:00:00.000000+10','Assemble'),
    (1,'2020-01-01 01:15:00.000000+10','Inspect'),
    (1,'2020-01-01 01:25:00.000000+10','Box'),

    (2,'2020-01-01 01:00:00.000000+10','Pull'),
    (2,'2020-01-01 01:10:00.000000+10','Inspect'),
    (2,'2020-01-01 01:13:00.000000+10','Dispatch'),

    (3,'2020-01-01 01:00:00.000000+10','Assemble');

COMMIT;

正在进行三个包1，2、 和3。两者都有三步，而1有一步。所以，平均值为。这是目标输出：237/3 = 2.333...

package_id    step_count    step_avg
1             3             2.3333333333333333
2             3             2.3333333333333333
3             1             2.3333333333333333

我在这里找到了很多线程，并在其他地方找到了示例。看起来可以使用子查询或 CTE 计算总体平均值。这是 CTE 版本：

WITH steps AS (
  select package_id,
         count(*) AS step_count      

    from progress

 group by package_id)

select avg(step_count) from steps;

这是子查询的替代方案：

select avg(total_steps) 
  from 
     (  select count(*) AS total_steps 
        from progress 
    group by package_id) AS steps_count

感觉就像我想要的是一个窗口函数，像这样：

   select package_id,
          count(*) as step_count,
          avg(count(*) OVER (PARTITION BY package_id)) as step_count_avg

     from progress 

 group by package_id
 order by package_id

这不起作用：

ERROR:  aggregate function calls cannot contain window function calls
LINE 3:           avg(count(*) OVER (PARTITION BY package_id)) as st...
                      ^. (Line 9)

我还没有找到任何方法将平均值与我的分组查询相结合，而不是CROSS JOIN. 这会产生正确的输出：

-- Group the records and get the count(*).
WITH 
steps AS (
  select package_id,
         count(*) AS step_count      

    from progress

 group by package_id),

-- Calculate the step average.
 step_summary AS (
   select avg(step_count) AS step_avg from steps
   )

-- CROSS JOIN to agglutinate the results
  select steps.package_id,
         steps.step_count,
         step_summary.step_avg

    from steps,
         step_summary

order by package_id

这似乎有点过头了。有没有更直接有效的方法来获得这样的整体价值？

背景： * 我们的真实表格将有数千万行。（测试数据集现在只有 250 万。）

• 我只看avg(count())这里，但我们还有其他示例aggregate(aggregate())要实现。

• 简单地将平均值附加到没有的组合输出CROSS JOIN似乎也完全失败了。

• 我们正在生成这样的输出以导入 Domo，它喜欢每一行的冗余值。

• 是的，如有必要，我们可以使用临时表或汇总表。但我也很想找出将aggregate(aggregate())结果集成到查询中的最简单方法。

建议跟进

这是一个旧 Oracle 线程的链接，其中包含一个非常接近的问题：

SQL AVG（计数（*））？

如果我正确理解了该线程，这里是用两个 CTE 和 no 重写我的查询CROSS JOIN：

with 
step_total as 
(
    select package_id, count(*) as step_count
    from progress
    group by package_id
),

step_stats as
(
    select package_id, 
           step_count, 
           avg(step_count) over (partition by package_id) as average

    from step_total
)

select *
from step_stats

这提供了每行count(*)的平均值，在这种情况下，这将等于。我想要得到的是用螺栓固定在每一行上的总体平均值。2.3333....这是上面的查询为我返回的内容：

package_id  step_count  average
1           3           3
2           3           3
3           1           1

标签： postgresqljoinaggregate-functionswindow-functions