Merge branch 'add-game-id-indexes'

diff --git a/scripts/refresh_active_servers_mv.sql b/scripts/refresh_active_servers_mv.sql
index 219a94d82bcfbbe6992b9e3eed65c9ed70d9f49c..05bdf7ba6d96e7a400d9396d1317b8366ac81960 100644 (file)
--- a/scripts/refresh_active_servers_mv.sql
+++ b/scripts/refresh_active_servers_mv.sql
@@ -1,9 +1,18 @@
-begin;
+do $$
+declare
+    cutoff timestamp := now() at time zone 'UTC' - interval '1 week';
+begin
     delete from active_servers_mv;
 
     insert into active_servers_mv
-    select row_number() over(order by count(1) desc) sort_order, s.server_id, s.name, count(1) games
-    from servers s join games g on s.server_id = g.server_id
-    where g.create_dt >= now() at time zone 'UTC' - interval '1 week'
-    group by 2, 3;
+    select row_number() over (order by sum(least(g.duration, pgs.alivetime)) desc) sort_order, 
+           s.server_id, s.name, sum(least(g.duration, pgs.alivetime)) play_time
+    from servers s join games g on g.server_id = s.server_id
+    join player_game_stats pgs on g.game_id = pgs.game_id
+    where pgs.player_id > 1
+    and g.create_dt > cutoff
+    and pgs.create_dt > cutoff
+    group by s.server_id, s.name
+    order by 1;
 end;
+$$

diff --git a/tables/active_servers_mv.tab b/tables/active_servers_mv.tab
index a6be890dfddd718a8f0ac60f0a1ec9bc2c2ca80f..3a5c5949c20199dc747de99c5437b3d30328ae13 100755 (executable)
--- a/tables/active_servers_mv.tab
+++ b/tables/active_servers_mv.tab
@@ -2,7 +2,7 @@ create table active_servers_mv(
        sort_order integer,
        server_id bigint,
        server_name character varying(128),
-       games integer,
+       play_time interval,
        create_dt timestamp without time zone default (now() at time zone 'UTC'),
     CONSTRAINT active_servers_mv_pk PRIMARY KEY (sort_order)
 );