趋势标签查询

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 |

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

我当前的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