# 部屋別メッセージ数の集計のためのインデクス
create index x_events_message_speed on events(room_id,origin_server_ts) 
where type='m.room.message';

# 部屋別メッセージ数を集計するビュー
CREATE OR REPLACE VIEW room_speed as 
 SELECT events.room_id,
    count(*) AS speed
   FROM events
  WHERE events.type = 'm.room.message'::text AND events.origin_server_ts::double precision >= (date_part('epoch'::text, CURRENT_TIMESTAMP) * 1000::double precision - 86400000::double precision)
  GROUP BY events.room_id;

# 集計して部屋の公開エイリアスと結合
select speed,canonical_alias from room_speed 
left join room_stats_state on room_stats_state.room_id = room_speed.room_id 
where speed>0 order by speed desc;
  • たていす@lemmy.juggler.jpOPM
    1·
    4 years ago
     speed |             canonical_alias
-------+------------------------------------------
  4953 | #news:smith.gdgd.jp.net
  1108 | #neta:matrix.juggler.jp
   698 | #element-web:matrix.org
   669 | #srad:matrix.juggler.jp
   267 | #synapse:matrix.org
   118 |
   113 | #omokan:matrix.juggler.jp
   100 | #soccer:matrix.juggler.jp
    86 | #lobby:matrix.juggler.jp
    81 | #iyh:matrix.juggler.jp
    55 | #nowplaying:matrix.juggler.jp
    50 | #miko:matrix.juggler.jp
    45 | #fediverse:nibbana.jp
    40 | #welcome:matrix.fedibird.com
    38 | #baseball-watch:matrix.juggler.jp
    30 |
    28 | #programming:matrix.juggler.jp
    22 | #talk:matrix.fedibird.com
    12 | #matrix-research:matrix-jp.net
    10 | #yamako-experimental:matrix.fedibird.com
     9 | #pc:matrix.juggler.jp
     8 | #anime-watch:matrix.juggler.jp
     6 | #freetalk:matrix.mstddntfdn.online
     3 | #retro-game:matrix.juggler.jp
     2 | #beginner:matrix.juggler.jp
     1 | #mjj:matrix.juggler.jp
     1 | #camera:matrix.juggler.jp
(27 rows)