sql - 与查询具有相同 WHERE 子句的子查询

Question

在 Postgres 9.4 中，我有一个表格，其中包含不同发件人发送的用户电子邮件。我需要生成最近不同发件人的列表，我正在使用以下查询:

SELECT DISTINCT ON (tableA.senderName) 
       tableA.senderName,tableA.received,tableA.someOtherColumn
FROM tableA 
WHERE tableA.received BETWEEN timeA AND timeB
ORDER BY tableA.senderName,tableA.received DESC

由于 tableA 包含所有发件人的所有消息，因此此查询仅向我提供不同发件人的最近消息并且效果很好。

实际上，我在 View 中有整个查询(WHERE 子句除外，因为 timeA 和 timeB 每次都不同)，然后我仅使用 WHERE 子句中的特定 timeA 和 timeB 从该 View 中选择 SELECT *。

现在考虑以下查询:

SELECT SUM(ts_rank_cd(tableA.precompTSVector,constantTSQuery)) AS score 
FROM tableA 
WHERE tableA.precomTSVector @@ constantTSQuery
AND tableA.sender = someParticularSender
AND tableA.received BETWEEN timeA AND timeB

它给出了在给定时间范围内针对特定发件人的所有邮件搜索一些常量 TS 查询的总分。

我想将此查询(可能作为子查询)合并到我的 View 中，这样我还可以看到每个不同发件人的“分数”。明显的问题是“分数查询”依赖于 WHERE 子句中的实际 timeA 和 timeB。在查询 View 本身之前，这些时间是未知的。

编辑: 一个不同的问题是 someOtherColumn 在第一个查询中也被选中(可能还有一些我也想合并到 View 中的其他列)-因为正在使用聚合函数。

有什么想法可以实现吗？或者也许欢迎一些不同的方法!

Answer 1

SELECT DISTINCT ON (senderName) 
       senderName, received -- add more columns as you please
     , SUM(ts_rank_cd(precompTSVector,constantTSQuery)) 
         FILTER (WHERE precomTSVector @@ constantTSQuery)
         OVER (PARTITION BY senderName) AS score 
FROM   tableA
WHERE  received BETWEEN timeA AND timeB
ORDER  BY senderName, received DESC;

首先，您需要正确设置 DISTINCT ON:

• Select first row in each GROUP BY group?

DISTINCT ON 在聚合函数之后 应用，这就是为什么您可以将两者组合在一个 SELECT 中。考虑 SELECT 查询中的事件序列:

• Best way to get result count before LIMIT was applied

关于新的聚合 FILTER 子句(需要 Postgres 9.4):

• How can I simplify this game statistics query?

您需要以 window function 的身份运行它，如果您想添加更多(未聚合的)列。

如果 received 可以为 NULL，您需要使用 DESC NULLS LAST:

• PostgreSQL sort by datetime asc, null first?

根据数据分布和基数，其他查询技术可能会或可能不会更快:

• Optimize GROUP BY query to retrieve latest record per user