sql - 然后使用前 10 个查询来搜索与其关联的所有记录

Question

一般来说，我对 sql 的使用不是特别丰富，我正在尝试完成一项非常具体的任务 - 我想先运行一个查询，以获取我所有具有最高点击数的单元的 ID，然后再次运行以获取特定时间段内这些 ID 的所有类型的点击消息和计数。对于第一个查询，我有这个:

SELECT entity, count(entity) as Count
from plugin_status_alerts
where entered BETWEEN now() - INTERVAL '14 days' AND now()
group by entity
order by count(entity) DESC
limit 10

导致此返回的结果:

"38792";3
"39416";2
"37796";2
"39145";2
"37713";2
"37360";2
"37724";2
"39152";2
"39937";2
"39667";2

想法是然后使用该结果集运行另一个按实体和状态代码排序的查询。我试过这样的事情:

SELECT status_code, entity, COUNT(status_code) statusCount
FROM plugin_status_alerts
where updated BETWEEN now() - INTERVAL '14 days' AND now() AND entity IN 
(SELECT id.entity, count(id.entity) as Count
from plugin_status_alerts id
where id.updated BETWEEN now() - INTERVAL '14 days' AND now()
group by id.entity
order by count(id.entity) DESC
limit 10
)
GROUP BY status_code, entity

但是我得到了错误

ERROR: subquery has too many columns

我不确定这是否是我应该走的路线，或者我是否应该尝试 self 加入 - 无论哪种方式都不确定如何纠正现在发生的事情。

Answer 1

使用 JOIN 而不是 IN(子查询)。这通常会更快，如果需要，您可以使用子查询中的其他值(例如每个 entity 的总计数):

SELECT entity, status_code, count(*) AS status_ct
FROM  (
   SELECT entity  -- not adding count since you don't use it, but you could
   FROM   plugin_status_alerts
   WHERE  entered BETWEEN now() - interval '14 days' AND now()
   GROUP  BY entitiy
   ORDER  BY count(*) DESC, entitiy  -- as tie breaker to get stable result
   LIMIT  10
   ) sub
JOIN   plugin_status_alerts USING (entity)
WHERE  updated BETWEEN now() - interval '14 days' AND now()
GROUP  BY 1, 2;

注意事项

• 如果您没有设计 future 的条目，您可以简化:

  WHERE  entered > now() - interval '14 days'
  

• 由于子查询只返回一个列(entity)，它与USING子句合并，列名没有歧义，我们不需要表格限定在这里。

• LIMIT 10 按计数排序后可能会产生歧义。多行可以并列为第 10 行。如果 ORDER BY 中没有其他项目，Postgres 会返回任意选择，这可能会也可能不会。但是查询的结果可以在调用之间发生变化，而不会对基础数据进行任何更改。通常，这是不可取的，您应该向列表中添加列或表达式以打破平局。

• count(*) 比 count(status_code) 快一点并且做同样的事情 - 除非 status_code 可以为 null ，在这种情况下，您将得到 0 作为该行的计数(count() 从不返回 null)，而不是实际的行数，这要么无用，要么明显错误。在这里使用 count(*) 两种方式。

• GROUP BY 1, 2 只是语法简写。详情:

  • Select first row in each GROUP BY group?