sql - 热搜标签查询

Question

我有以下数据库结构:

可标记表格

| ID | Title          |
|----|----------------|
| 1  | Taggable title |

标记(连接表)

| id | taggable_id | taggable_type | tag_id | created_at          |
|----|-------------|---------------|--------|---------------------|
| 1  | 1           | Taggable      | 100    | 2018-01-01 09:00:00 |

标签

| id | name      |
|----|-----------|
|100 | First tag |

我需要确定最近流行的是那些“标签”中的哪一个。第一个挑战是发明一些简单的算法来计算“趋势”的含义。我决定使用一个可能的最简单的方法(这没什么大不了的，以后可以对其进行微调)，它可能有点蹩脚但有效——计算每个标签在过去一小时和 3 小时前有多少标签，计算两者之间的差异当前计数和旧计数，并根据该差异对结果进行排序。

我当前对此的 SQL 查询如下所示:

select DISTINCT(tags.id), tags.*, (
    select COUNT(*)
    from taggings 
    where taggings.tag_id = tags.id
    and taggings.created_at::timestamp > now() - interval '3 hour'
) - (
    select COUNT(*) 
    from taggings 
    where taggings.tag_id = gutentag_tags.id
    and taggings.created_at <= now()::timestamp - interval '3 hour'
    and taggings.created_at > now()::timestamp - interval '12 hour'
) as hottness
from tags
left join taggings on tags.id = taggings.tag_id
where taggings.created_at >= now()::timestamp - interval '12 hours'
order by hottness desc

一个随时可用的 sqlfiddle 在这里: http://sqlfiddle.com/#!17/2298a/1

而且我很确定它是完全糟糕和不理想的，它会在更高的负载下杀死我的服务器 - 但它有效。有没有人知道我该如何改进它或如何完全改变我的尝试以使其更好更安全地工作？提前致谢。

Answer 1

可以通过只查询一次标签来优化

select tags.id, 
  count( case when taggings.created_at::timestamp > now() - interval '3 hour' then 1 else null end
  ) 
- 
   count ( case when
    taggings.created_at <= now()::timestamp - interval '3 hour'
    and taggings.created_at > now()::timestamp - interval '12 hour'
    then 1 else null end
) as hottness 
from tags
left join taggings on tags.id = taggings.tag_id
where taggings.created_at >= now()::timestamp - interval '12 hours'
group by tags.id
order by hottness desc