MySQLでランキングのテーブル設計

Question

前提・実現したいこと

ランキングを集計したいのですが、調べた記事に疑問がございます。

日間から週間への集計データはどのように移行すればいいのでしょうか？

一案としてのテーブル構造

さきほどのリンクにはこちらの図が掲載されています。
右のratingは当日の集計で、左のaggregate_pointsは日間、週刊、月間の集計です。

例えばユーザーに「今週のfavorite順」を表示するなら、左のaggregate_pointsのfavorite_weeklyを参照すればいいことになります。

不明な点

しかし不明な点があります。日間の更新はわかりますが、週間の更新がわからないのです。

例えば本日（火曜）の深夜0時にaggregate_pointsのfavorite_dailyを更新したい場合、ratingのfavorite_countをコピーすればいいとわかりますが、

ではそのとき、aggregate_pointsのfavorite_weelkyはどのように計算すればいいのでしょうか？

aggregate_pointsのfavorite_weelkyは直近７日間（「先週の火曜の、ratingのfavorite_count」から「今週の月曜の、ratingのfavorite_count」の合計）が入っている現状に対し、「本日のratingのfavorite_count」を加算しつつ、「先週の火曜の、ratingのfavorite_count」を除算しなければならないと思うのですが、いったい「先週の火曜の、ratingのfavorite_count」はどうやって得ればいいのか？というのが不明です。

それが得られなければ、favorite_weelkyを直近７日間とすることができないように思います。

###具体的な数字
具体的に言えば、aggregate_pointsのfavorite_weelkyが40だとして、それは以下のように日々のratingのfavorite_countが加算された結果だとします。
|曜日|火|水|木|金|土|日|月|
|:--|:--|:--:|--:|
|ratingのfavorite_count|10|5|5|5|5|5|5|

仮に「本日のratingのfavorite_count」が20ならば
現状の週間(40) ＋ 日間(20) － 週間の最古の値(10) ＝ 50
がaggregate_pointsのfavorite_weelkyに上書きしたいはずですが、この10はどうやって得ればいいのか？というのが不明です。

上記解決策がわかる方、ないし別のテーブル設計の案をお持ちの方いらっしゃいましたら一計を頂戴できましたら幸いでございます。
宜しくお願い致します。

Comments

[yambejp]

具体的なサンプルテーブルを提示し、どういう結果が欲しいのか示してください

Answer 1

aggregate_pointsのfavorite_weelkyはどのように計算すればいいのでしょうか？

history_ratingは「過去30日分の履歴格納」とあるので、直近の7日分はそこから求めるのでしょう。
※履歴としての日付を示す項目が何なのか分かりませんが。

ただ、history_ratingを「過去30日分の履歴格納」とすると、常に更新対象ですし件数0でもレコードを用意するような無駄も省き、アクセスが発生したデータのみとした方が良いと感じます。
そうすると、ratingはhistory_ratingが兼ねる事になります。

上記のような期間に対する集計を考えると、generate_series()のあるpostgresが個人的には好みです。

その場合、aggregate_pointsまで準備するかどうかは、性能を検証してみてですけど。
仮にaggregate_pointsの用途を求めるとしても、history_ratingのパーティショニングで対応しそう。

Comments

[ajakong]

なるほどhistory_ratingを使って得られそうですね！
どうもありがとうございます。

たしかに仰る通りratingの必要性が感じられませんね。

Answer 2

where target_date > curdate() - interval 1 week

的なことを聞きたいのでしょうか

Comments

[ajakong]

いえ、具体的な数字に書いてあるように「10」が得られなければ週間ランキングが取得できないと考えているので、それを得る方法を聞きたいのです。

[ajakong]

本日の時点でratingのfavorite_countは20になっていますから、10はどこからも得られず、それでは週間ランキングが集計できないと思うのです。

[yambejp]

日付で検索しない？ちょっと質問としてなりたっていません
テーブルをcreate table + insert で例示して
何が10なのかはっきりさせてください

[ajakong]

>何が10なのかはっきりさせてください
「先週の火曜の、ratingのfavorite_count」が「10」です。

[yambejp]

ごめんなさい、何が聞きたいのかさっぱりわからないし
これ以上情報を提示する気もなさそうなので、私のスキルでは
回答できそうもありません。ほかの識者にお任せします
悪しからず

[ajakong]

うまく質問できずすみません。ご協力ありがとうございます。