在PostgreSQL中使用外部查询结果进行子查询

Question

在PostgreSQL中使用外部查询结果进行子查询

9

我有两个表格 points 和 contacts，我想按月份分组获得每个联系人的平均 points.score。请注意，这两个表格没有关系，我只是想得到一个月内创建的点数总和除以那个月存在的联系人数量。

所以，我需要按创建日期月份对点数进行求和，并且我需要仅针对该月份获取联系人的数量。正是最后一个部分让我感到困惑。我不确定如何在子查询中使用外部查询的列。我尝试了以下内容：

SELECT SUM(score) AS points_sum,
  EXTRACT(month FROM created_at) AS month,
  date_trunc('MONTH', created_at) + INTERVAL '1 month' AS next_month,
  (SELECT COUNT(id) FROM contacts WHERE contacts.created_at <= next_month) as contact_count
FROM points
GROUP BY month, next_month
ORDER BY month

所以，我正在提取我的积分被累加的实际月份，同时获取下个月的开始时间，这样我就可以说“给我联系人计数，其中他们的创建时间 < 下一个月”。但是它抱怨说列 next_month 不存在。这是可以理解的，因为子查询对外部查询一无所知。用points.next_month限定也不起作用。那么有谁能指导我该如何实现呢？

表:

Points

score | created_at
10    | "2011-11-15 21:44:00.363423"
11    | "2011-10-15 21:44:00.69667" 
12    | "2011-09-15 21:44:00.773289"
13    | "2011-08-15 21:44:00.848838"
14    | "2011-07-15 21:44:00.924152"

联系人

id | created_at
6  | "2011-07-15 21:43:17.534777"
5  | "2011-08-15 21:43:17.520828"
4  | "2011-09-15 21:43:17.506452"
3  | "2011-10-15 21:43:17.491848"
1  | "2011-11-15 21:42:54.759225"

sum、month和next_month（不使用子查询）

sum | month | next_month
14  | 7     | "2011-08-01 00:00:00"
13  | 8     | "2011-09-01 00:00:00"
12  | 9     | "2011-10-01 00:00:00"
11  | 10    | "2011-11-01 00:00:00"
10  | 11    | "2011-12-01 00:00:00"

- brad

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

编辑

现在带有联系人的累加和。我的第一版使用了每月新增联系人数，这显然不是 OP 想要的。

WITH c AS (
    SELECT created_at
          ,count(id) OVER (order BY created_at) AS ct
    FROM   contacts
    ), p AS (
    SELECT date_trunc('month', created_at) AS month
          ,sum(score) AS points_sum
    FROM   points
    GROUP  BY 1
    )
SELECT p.month
      ,EXTRACT(month FROM p.month) AS month_nr
      ,p.points_sum
      ,( SELECT c.ct
         FROM   c
         WHERE  c.created_at < (p.month + interval '1 month')
         ORDER  BY c.created_at DESC
         LIMIT  1) AS contacts
FROM   p
ORDER  BY 1

这适用于跨年的任何月份。
假定表格 points 中没有缺少的月份。如果你想要包括在 points 中缺失的所有月份，可以使用 generate_series() 生成一个月份列表，并与其进行 LEFT JOIN。
使用窗口函数在 CTE 中进行累加和计算。
为了性能和简化而存在，两个 CTE 并非必需。
在子查询中获取 contacts_count。

原始查询语句可以像这样工作：

SELECT month
      ,EXTRACT(month FROM month) AS month_nr
      ,points_sum
      ,(SELECT count(*)
        FROM   contacts c
        WHERE  c.created_at < (p.month + interval '1 month')) AS contact_count
FROM   (
    SELECT date_trunc('MONTH', created_at) AS month
          ,sum(score) AS points_sum
    FROM   points p
    GROUP  BY 1
    ) p
ORDER  BY 1

您出错的直接原因是要将聚合函数放入子查询中。您以不可能的方式混合了级别。
我预计我的变体在大表上会稍微快一些。对于较小的表格尚不确定。如果您能用测试结果回报一下就好了。
另外还有一个小问题需要修复：< 应该改成 <=。