Django の QuerySet で group by をする方法


# Introduction

現在、 [Twitter Developer API](https://developer.twitter.com/ja) を使ってシンプルな[Twitter分析アプリ](https://featurepoints.jp/) を作成中です。それで、定期実行でリクエストをかけたものをMySQLにてデータをストックしていました。

あるとき、
*いいね(favorite)が多く押されたツイートを探す*
ことをしようとしました。

もし、MySQLで取得する場合は、下記の通りツイートIDでgroupbyをしてその中でいいね数が一番大きいものを取得する記述をすればいいのですが、 DjangoにはSQLを書かずにコード上で取得できる[QuerySet](https://docs.djangoproject.com/en/3.2/ref/models/querysets/)というものがあるので、なるべく使おうと思ったとです。

```sql
select tweet_id, max(favorite_count) as fav_max 
from ONE_WEEK_TWEET 
group by tweet_id
order by fav_max desc 
limit 10
```

# Dataset

データセットですが、 [Twitter Development API](https://developer.twitter.com/ja) の `[GET]search/tweets` にてレスポンスされた値を使っています。 この後で、MySQLのテーブルにほぼそのままデータを残しています。

テーブル構成はこんな感じ

```sql
CREATE TABLE `ONE_WEEK_TWEET` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tweet_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `query` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `media` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_screen_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `retweet_count` int DEFAULT NULL,
  `favorite_count` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ONE_WEEK_TWEET_timestamp` (`timestamp`),
  KEY `idx_ONE_WEEK_TWEET_query` (`query`),
  KEY `idx_ONE_WEEK_TWEET_tweet_id` (`tweet_id`),
  KEY `idx_ONE_WEEK_TWEET_favorite_count` (`favorite_count`)
) ENGINE=InnoDB AUTO_INCREMENT=128216863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

それで、APIによる取得は15分間隔で取得しているので、`tweet_id`が重複したかたちで随時取り込まれます。例として、ユーザに数時間トレンドとして注目されるツイートあったとして、時間を追うごとにリツイート数やいいね数がプラスされ、`tweet_id`のタイムログとしてデータが残ることになります。 

|id|tweet_id|user_screen_name|favorite_count|created_at|
|:-:|:-:|:-:|:-:|:-:|
|127925165|1381948770779205633|shingeki__kun|146282|"2021-04-14 23:07:26"|
|127919064|1381948770779205633|shingeki__kun|144569|"2021-04-14 22:49:54"|
|127910044|1381948770779205633|shingeki__kun|141736|"2021-04-14 22:21:01"|
|127907040|1381948770779205633|shingeki__kun|140661|"2021-04-14 22:10:28"|
|127901833|1381948770779205633|shingeki__kun|138909|"2021-04-14 21:54:04"|
|127893406|1381948770779205633|shingeki__kun|136266|"2021-04-14 21:32:43"|
|127442169|1381948770779205633|shingeki__kun|311|"2021-04-13 21:49:43"|
|127438233|1381948770779205633|shingeki__kun|61|"2021-04-13 21:37:28"|

ここでの目的ですが、各ツイートでいいね数が一番多い値を取得する、ということです。

上記をふまえて、QuerySetを使ってのgroupbyの方法を書いてみます。

# Method

前置きなしにコードを書いてみます。

```Python
from django.db.models import Max
ONE_WEEK_TWEET.objects.values("tweet_id").annotate(fav_max=Max('favorite_count')).order_by('fav_max').reverse().values(*["tweet_id", "fav_max"])
```

一般化すると、、、

```Python
ONE_WEEK_TWEET.objects.values("[groupbyで集合をかけるカラム]").annotate(fav_max=Max('[集合した中での最大値をとりたいカラム]'))
```
となります。

あとはツイートで最大のいいね数が`fav_max`カラムで管理されるので、`order_by('fav_max').reverse()`で降順し、必要なカラムを`values(*["tweet_id", "fav_max"])` すれば完了:raised_hands: