使用子选择值进行PostgreSQL计算

Question

使用PostgreSQL 9.3，我试图根据电子邮件表中的子选择结果计算一些值。下面是我想要做的事情，这当然会导致SQL错误，因为您不能在SELECT中引用别名值：

SELECT 
    email.key,
    email.col1,
    email.col2,
    (SELECT count(sent.subscriber_key) FROM sent WHERE sent.email_id = email.key) AS sent_count,
    (SELECT count(bounce.subscriber_key) FROM bounce WHERE bounce.email_id = email.key) AS bounce_count,
    ((bounce_count / sent_count) * 100) as bounce_pct
FROM
    email
ORDER BY 
    email.key

我尝试将子选择逻辑移到WITH子句中：

WITH
    sent_tmp AS (SELECT count(sent.subscriber_key) as count FROM sent INNER JOIN email ON sent.send_id = email.key),
    bounce_tmp AS (SELECT count(bounce.subscriber_key) as count FROM bounce INNER JOIN email ON bounce.send_id = email.key)
SELECT 
    email.key,
    email.col1,
    email.col2,
    email.sent_date,
    sent_tmp.count,
    bounce_tmp.count,
    ((bounce_tmp.count / sent_tmp.count) * 100) as bounce_pct
FROM
    email,
    sent_tmp,
    bounce_tmp
ORDER BY 
    email.key

...and我尝试将它移到FROM子句中：

SELECT 
    email.key,
    email.col1,
    email.col2,
    sent_count,
    bounce_count,
    ((bounce_count / sent_count) * 100) as bounce_pct
FROM
    email,
    (SELECT count(sent.subscriber_key) FROM sent INNER JOIN email ON sent.send_id = email.key) AS sent_count,
    (SELECT count(bounce.subscriber_key) FROM bounce INNER JOIN email ON bounce.send_id = email.key) AS bounce_count
ORDER BY 
    email.key

.，但无论哪种方式，都会产生无效的发送和弹回数字。(每封邮件的号码都是一样的。)我可能不了解PostgreSQL查询语法的执行顺序，或者没有对电子邮件表中的每一行结果计算子选择。

如果有人能指点我在正确的方向，并让我知道什么是可能的，它将不胜感激！在将子选择逻辑移到WITH或FROM中时，是否缺少一些关键语法？我需要使用命名变量吗？功能？

我意识到可以添加另一个子subselect，它将在发送表和反射表中重新计算subscriber_key列，但为了效率起见，我试图避免重新计数。(以及可读性，就这一点而言)

Answer 1

简单的解决方案是计算外部查询中的值：

SELECT key, col1, col2, sent_count, bounce_count,
       100.0 * bounce_count / sent_count AS bounce_pct
FROM (SELECT key, col1, col2,
            (SELECT count(sent.subscriber_key)
             FROM sent
             WHERE sent.email_id = email.key
            ) AS sent_count,
            (SELECT count(bounce.subscriber_key)
             FROM bounce
             WHERE bounce.email_id = email.key
            ) AS bounce_count
      FROM email
     ) q
ORDER BY key;