如何过滤PostgreSQL查询中yesterday_sum≤1或为NULL的行？

问题背景 #

我编写了一条包含关联子查询的PostgreSQL查询，子查询用于生成yesterday_sum列。现在需要筛选出yesterday_sum > 1的行，但遇到了两个问题：

• 没法在HAVING子句里加yesterday_sum > 1，因为这个字段不在GROUP BY列表中
• 也没法在positions表的筛选条件里加限制，因为yesterday_sum不是原表的字段

原查询语句如下：

SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       (
           SELECT sum(ps.profit_percent)
           FROM positions ps
           WHERE ps.user_id = u.id
             AND ps.open_at BETWEEN
               :dateYesterday AND
               :dateYesterday + INTERVAL '1 day'
           GROUP BY (ps.user_id)
       )                                 AS yesterday_sum
FROM positions p
         INNER JOIN users u ON u.id = p.user_id
    AND p.profit_percent IS NOT NULL
    AND p.parent_ticket IS NULL
    AND p.close_at IS NOT NULL
    AND p.open_at BETWEEN :dateFrom AND :dateTo
GROUP BY (u.id, u.nickname)
HAVING sum(p.profit_percent) / :workDays > 1
ORDER BY middle DESC;

解决办法 #

方法1：嵌套子查询，外层加筛选

把原查询整个包成一个子查询，在外层的WHERE里直接过滤yesterday_sum即可，简单直接：

SELECT *
FROM (
    SELECT u.id                              AS id,
           u.nickname                        AS title,
           sum(p.profit_percent) / :workDays AS middle,
           (
               SELECT sum(ps.profit_percent)
               FROM positions ps
               WHERE ps.user_id = u.id
                 AND ps.open_at BETWEEN
                   :dateYesterday AND
                   :dateYesterday + INTERVAL '1 day'
               GROUP BY (ps.user_id)
           )                                 AS yesterday_sum
    FROM positions p
             INNER JOIN users u ON u.id = p.user_id
        AND p.profit_percent IS NOT NULL
        AND p.parent_ticket IS NULL
        AND p.close_at IS NOT NULL
        AND p.open_at BETWEEN :dateFrom AND :dateTo
    GROUP BY (u.id, u.nickname)
    HAVING sum(p.profit_percent) / :workDays > 1
) AS sub_query
WHERE yesterday_sum > 1
ORDER BY middle DESC;

方法2：转成JOIN预聚合（性能更优）

关联子查询在数据量大的时候可能效率偏低，我们可以先把昨天的聚合结果单独算出来，再和主查询结果JOIN，这样yesterday_sum就变成了可直接筛选的字段：

SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       ps_sum.yesterday_sum
FROM positions p
INNER JOIN users u ON u.id = p.user_id
-- 预聚合昨天的用户收益，提前筛选符合条件的用户
INNER JOIN (
    SELECT user_id, sum(profit_percent) AS yesterday_sum
    FROM positions
    WHERE open_at BETWEEN :dateYesterday AND :dateYesterday + INTERVAL '1 day'
    GROUP BY user_id
    HAVING sum(profit_percent) > 1
) ps_sum ON ps_sum.user_id = u.id
WHERE p.profit_percent IS NOT NULL
  AND p.parent_ticket IS NULL
  AND p.close_at IS NOT NULL
  AND p.open_at BETWEEN :dateFrom AND :dateTo
GROUP BY u.id, u.nickname, ps_sum.yesterday_sum
HAVING sum(p.profit_percent) / :workDays > 1
ORDER BY middle DESC;

这里用INNER JOIN直接过滤掉没有符合条件的yesterday_sum的用户，避免了逐行执行子查询，大数据量下性能更好。

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