- •
- 2Y
- •
# 部屋別メッセージ数の集計のためのインデクス
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;
You must log in or register to comment.
mod
admin
creator
1•2Y