sql - 在 postgresql 的子查询中使用外部查询结果

Question

我有两个表 points 和 contacts 并且我正在尝试获取每月分组的每个联系人的平均 points.score . 请注意点数和联系人不相关，我只想用一个月内创建的点数总和除以该月存在的联系人数。

因此，我需要对按 created_at 月份分组的点数求和，并且我只需要计算那个月的联系人数量。这是欺骗我的最后一部分。我不确定如何在子查询中使用来自外部查询的列。我试过这样的事情:

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 的开始，这样我就可以说“让我知道他们创建时间 < next_month 的联系人数量"

但它提示 column next_month doesn't exist 这是可以理解的，因为子查询对外部查询一无所知。使用 points.next_month 进行资格赛也无效。

那么有人可以指出我如何实现这一目标的正确方向吗？

表格:

积分

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"

Answer 1

编辑

现在有联系人的运行总和。我的初稿使用每月新联系人，这显然不是 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() 生成月份列表并加入它。
• 使用窗口函数在 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

解决错误的直接原因是将聚合放入子查询中。你以一种不可能的方式混合关卡。
我希望我的变体在使用大表时会稍微快一些。不确定较小的 table 。如果您能报告测试结果，那就太好了。
加上一个小修复:<而不是 <= .