Introduction

現在、 Twitter Developer API を使ってシンプルなTwitter分析アプリを作成中です。それで、定期実行でリクエストをかけたものをMySQLにてデータをストックしていました。

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

もし、MySQLで取得する場合は、下記の通りツイートIDでgroupbyをしてその中でいいね数が一番大きいものを取得する記述をすればいいのですが、 DjangoにはSQLを書かずにコード上で取得できるQuerySetというものがあるので、なるべく使おうと思ったとです。

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 の [GET]search/tweets にてレスポンスされた値を使っています。この後で、MySQLのテーブルにほぼそのままデータを残しています。

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

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

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

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"])

一般化すると、、、

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

となります。

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

Django の QuerySet で group by をする方法

Introduction

Dataset

Method