PostgreSQL：とにかく簡単にトレンドやホットランキングを出したい

SQL PostgreSQL

CGMなどを作っていると、現在人気の投稿をトレンドとしてランキング表示したいときがある
これをSQLだけで実装する
パフォーマンスも何も考えていないしテーブル構成にも依存しているし、さらには手動で調整するマジックナンバーさえある
だが、簡単なのでとりあえずそれっぽい機能を付けたいとなったときに役立つかもしれない

環境

PostgreSQL

テーブル構成1

ランク付けの対象として投稿を表すPOSTテーブルがあり、閲覧数を表すVIEWテーブルと一対多のリレーションになっているとする（閲覧されるたびにVIEWテーブルのレコードが増えるパターン）

また、各テーブルには作成日時created_atと更新日時updated_atがあるとする

SQL

SELECT "post".*, (COUNT(DISTINCT view))  as rank_point
FROM "post"
LEFT OUTER JOIN "view" ON "view"."post_id" = "post"."id" AND "view"."created_at" >= now() - interval '1 hour'
GROUP BY post.id
ORDER BY rank_point DESC;

説明

LEFT OUTER JOINで結合する時にVIEWテーブルの条件を調整している
"view"."created_at" >= now() - interval '1 hour'は現在時刻から1時間前までの間に作成されたVIEWのレコードのみ結合するという条件で、これをカウントしてrank_pointとし、post.idでグルーピングしたものを降順に並べれば1時間のうちで閲覧が多い投稿が順に並ぶはずである
interval '1 hour'の部分は好きなように調整できる

テーブル構成2

他の指標を使いたくなったらいくらでも追加できる
例えば投稿には一対多でコメントがあって、コメントの多さでも人気なことを表したいとする

SQL

SELECT "post".*, ( (COUNT(DISTINCT view) * 10) + (COUNT(DISTINCT comment) * 1000 ) ) / 2  as rank_point
FROM "post"
LEFT OUTER JOIN "view" ON "view"."post_id" = "post"."id" AND "view"."created_at" >= now() - interval '1 hour'
LEFT OUTER JOIN "comment" ON "comment"."post_id" = "post"."id" AND "comment"."created_at" >= now() - interval '1 hour'
GROUP BY post.id
ORDER BY rank_point DESC;