統計データ用テーブル作成

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;