如何在SQL中编写平均值的平均值查询？现有查询优化需求

首先修正原查询中的笔误：最后FROM子句里的pur p应该改为purs p，否则会因表不存在报错。

要实现每个唯一邮箱的所有交易平均值的总平均，只需将原查询的结果作为子查询（或新增一个CTE），然后按email分组对avg_per_deal再次聚合即可。

修改后的完整SQL如下：

WITH purs AS (
    SELECT
        u.email,
        di.internal_name,
        dh.id,
        dh.created_at,
        dh.deal_id,
        dh.new_demand_status 
    FROM
        deal.deal_history dh
        JOIN deal.deal d ON d.id = dh.deal_id 
        JOIN deal.deal_intent di ON di.id = d.supply_intent
        JOIN axd.user u ON u.public_id = di.user_public_id 
    WHERE
        dh.stat = 'purs'
        AND dh.ostat = 'teas'
        AND di.type != 'wav'
),
signda AS (
    SELECT
        dh.id,
        dh.created_at,
        dh.deal_id,
        dh.stat
    FROM
        deal.deal_history dh
        JOIN deal.deal d ON d.id = dh.deal_id 
        JOIN deal.deal_intent di ON di.id = d.supply_intent_id 
    WHERE
        stat = 'signda'
        AND di.type != 'wav'
),
deal_avg AS (
    SELECT
        p.email,
        p.internal_name,
        AVG(age(sn.created_at,p.created_at)) AS avg_per_deal
    FROM
        purs p  -- 修正原笔误：pur改为purs
        JOIN signda sn ON sn.deal_id = p.deal_id
    WHERE p.created_at > now() - interval '1 month'
    GROUP BY 1,2
)
SELECT
    email,
    AVG(avg_per_deal) AS total_avg_per_email
FROM deal_avg
GROUP BY email;

逻辑说明 #

1. 新增deal_avg CTE，用来存储原查询的结果：即每个邮箱+交易类型组合的单交易平均时长。
2. 外层查询按email分组，对deal_avg中的avg_per_deal再次计算平均值，得到每个邮箱所有交易类型的平均时长的总平均值。

如果不需要保留internal_name的中间结果，也可以直接用子查询嵌套的写法：

SELECT
    email,
    AVG(avg_per_deal) AS total_avg_per_email
FROM (
    SELECT
        p.email,
        AVG(age(sn.created_at,p.created_at)) AS avg_per_deal
    FROM
        purs p
        JOIN signda sn ON sn.deal_id = p.deal_id
    WHERE p.created_at > now() - interval '1 month'
    GROUP BY p.email, p.internal_name
) AS deal_avg
GROUP BY email;

内容的提问来源于stack exchange，提问作者noybhaha12