Commits

Carey Tilden committed c3a1582

Added player user name to the results

Comments (0)

Files changed (1)

share/SuperHostile14-deaths.sql

 \set time_group_secs (60*60)
 \set life_len_group_secs (5*60)
 
-with
-    time_segments as (
-        select
-            to_char(
-                timestamp with time zone 'epoch' + interval '1 second'
-                * floor(extract('epoch' from time_stamp_minute) / :time_group_secs)
-                * :time_group_secs,
-                'YYYY-MM-DD HH24:MI'
-            ) as time_segment,
-            count(*) as minutes,
-            sum(deaths) as deaths
-        from (
-            select date_trunc('minute', time_stamp) as time_stamp_minute,
-                sum(case when log_entry_type_name = 'Player died' then 1 else 0 end) as deaths
-            from log_entry_list where server_cwd_basename = 'SuperHostile14' group by 1
-        ) q1 group by 1
-    )
-select
-    time_segment,
-    deaths,
-    minutes,
-    round(deaths/minutes, 1) as "d/min",
-    round(minutes/(deaths+1), 1)::real as "mins/life"
-from (
-    select time_segment, minutes, deaths from time_segments
-    union all
-    select 'total', sum(minutes), sum(deaths) from time_segments
-) q2 order by 1;
+--with
+--    time_segments as (
+--        select
+--            player_user_name,
+--            to_char(
+--                timestamp with time zone 'epoch' + interval '1 second'
+--                * floor(extract('epoch' from time_stamp_minute) / :time_group_secs)
+--                * :time_group_secs,
+--                'YYYY-MM-DD HH24:MI'
+--            ) as time_segment,
+--            count(*) as minutes,
+--            sum(deaths) as deaths
+--        from (
+--            select player_user_name, date_trunc('minute', time_stamp) as time_stamp_minute,
+--                sum(case when log_entry_type_name = 'Player died' then 1 else 0 end) as deaths
+--            from log_entry_list where server_cwd_basename = 'SuperHostile14' and player_user_name is not null
+--            group by 1, 2
+--        ) q1 group by 1, 2
+--    )
+--select
+--    player_user_name,
+--    time_segment,
+--    deaths,
+--    minutes,
+--    round(deaths/minutes, 1) as "d/min",
+--    round(minutes/(deaths+1), 1)::real as "mins/life"
+--from (
+--    select player_user_name, time_segment, minutes, deaths from time_segments
+--    union all
+--    select player_user_name, 'total', sum(minutes), sum(deaths) from time_segments group by player_user_name
+--) q2 order by 1, 2;
 
 with
     actions as (
         select
+            player_user_name,
             time_stamp,
             log_entry_type_name,
             case
-                when lag(log_entry_type_name) over (order by time_stamp) is null then 'start'
-                when lag(log_entry_type_name) over (order by time_stamp) = 'Player died' then 'start'
+                when lag(log_entry_type_name) over (partition by player_user_name order by time_stamp) is null then 'start'
+                when lag(log_entry_type_name) over (partition by player_user_name order by time_stamp) = 'Player died' then 'start'
                 when log_entry_type_name = 'Player died' then 'stop'
-                when lead(log_entry_type_name) over (order by time_stamp) is null then 'stop'
+                when lead(log_entry_type_name) over (partition by player_user_name order by time_stamp) is null then 'stop'
             end as life_boundary
-        from log_entry_list where server_cwd_basename = 'SuperHostile14'
+        from log_entry_list where server_cwd_basename = 'SuperHostile14' and player_user_name is not null
     ),
     lives as (
         select row_number() over () as life_id, q.*
         from (
             select
+                player_user_name,
                 life_boundary,
                 time_stamp as time_stamp1,
                 log_entry_type_name as log_entry_type_name1,
-                lead(time_stamp) over (order by time_stamp) as time_stamp2,
-                lead(log_entry_type_name) over (order by time_stamp) as log_entry_type_name2
+                lead(time_stamp) over (partition by player_user_name order by time_stamp) as time_stamp2,
+                lead(log_entry_type_name) over (partition by player_user_name order by time_stamp) as log_entry_type_name2
             from actions
             where actions.life_boundary is not null
         ) q
     ),
     minutes_per_life as (
         select life_id,
+            player_user_name,
             round(
                 case
                     when clock_minutes < count(*) then clock_minutes
         from (
             select
                 life_id,
+                lives.player_user_name,
                 extract(epoch from time_stamp2 - time_stamp1) / 60 as clock_minutes,
                 date_trunc('minute', actions.time_stamp) as active_minute
             from lives join actions on actions.time_stamp between lives.time_stamp1 and lives.time_stamp2
-            group by 1, 2, 3
+            group by 1, 2, 3, 4
         ) q
-        group by life_id, clock_minutes order by 1
+        group by life_id, player_user_name, clock_minutes order by 1
     ),
     life_len_groups as (
         select
+            player_user_name,
             life_len_minutes,
             interval '1 second' * life_len_minutes * 60 as life_len,
             :life_len_group_secs * round((life_len_minutes*60)/:life_len_group_secs) / 60 as life_len_group
         from minutes_per_life order by 1
     )
-select '   total' as life_len, count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real as avg, max(life_len_minutes)
+select player_user_name, '   total' as life_len, count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real as avg, max(life_len_minutes)
 from life_len_groups
+group by player_user_name
 union all
-select to_char(life_len_group, '9999999'), count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real, max(life_len_minutes)
+select player_user_name, to_char(life_len_group, '9999999'), count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real, max(life_len_minutes)
 from life_len_groups
-group by life_len_group
-order by 1
+group by player_user_name, life_len_group
+order by 1, 2