create table hatebu_statistics (
user_id integer primary key,
favoring_count integer,
favored_count integer,
favorite_rank real
);
-- お気に入られIDの方にもインデックス
create index hatebu_favorite_dest_id_idx on hatebu_favorite (dest_id);
-- 全ユーザ分のレコードを作成
insert into hatebu_statistics select record_id as user_id from hatebu_user;
-- お気に入り数とお気に入られ数を更新
update hatebu_statistics set favoring_count = (select count(*)
from hatebu_favorite f where hatebu_statistics.user_id = f.src_id);
update hatebu_statistics set favored_count =
(select count(*) from hatebu_favorite f where hatebu_statistics.user_id = f.dest_id);
-- お気に入り数の多い順に表示
select u.hatena_id, s.favoring_count from hatebu_user u, hatebu_statistics s
where u.record_id = s.user_id order by s.favoring_count desc;
-- お気に入られ数の多い順に表示
select u.hatena_id, s.favored_count from hatebu_user u, hatebu_statistics s
where u.record_id = s.user_id order by s.favored_count desc;