Commits

Carey Tilden committed 77cf748

Created scripts for Restore the Wasteland

Comments (0)

Files changed (3)

share/RTW-deaths.sql

+\set time_group_secs (60*60)
+\set life_len_group_secs (5*60)
+
+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 = 'Restore_the_Wasteland' 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;
+
+drop table if exists minutes_per_life;
+
+create temporary table minutes_per_life as
+    with
+        actions as (
+            select
+                player_user_name,
+                time_stamp,
+                log_entry_type_name,
+                case
+                    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 (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 = 'Restore_the_Wasteland' 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 (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
+            where life_boundary = 'start'
+        )
+        select life_id,
+            player_user_name,
+            start_time,
+            round(
+                case
+                    when clock_minutes < count(*) then clock_minutes
+                    else count(*)::double precision
+                end::numeric,
+                1
+            )::real as life_len_minutes
+        from (
+            select
+                life_id,
+                lives.player_user_name,
+                time_stamp1 as start_time,
+                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, 4, 5
+        ) q
+        group by life_id, player_user_name, start_time, clock_minutes order by 1;
+
+select * from minutes_per_life;
+
+with 
+    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 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 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 player_user_name, life_len_group
+order by 1, 2

share/RTW-details.sql

+select
+    log_entry_type_name,
+    coalesce(block_type_name, damage_type_name, victim_entity_type_name, exploding_entity_type_name) as details,
+    count(*) as total,
+    sum(case when player_user_name = 'careytilden'   then 1 else 0 end) as cbt,
+    sum(case when player_user_name = 'revslowmo'     then 1 else 0 end) as rtb,
+    sum(case when player_user_name = 'jasonscheirer' then 1 else 0 end) as jes
+from log_entry_list
+where server_cwd_basename = 'Restore_the_Wasteland'
+group by 1, 2
+order by 1, 3 desc, 2;
+
+select
+    to_char(time_stamp, 'YYYY-MM-DD') as yyyymmdd,
+    log_entry_type_name,
+    coalesce(block_type_name, damage_type_name, victim_entity_type_name, exploding_entity_type_name) as details,
+    count(*) as total,
+    sum(case when player_user_name = 'careytilden'   then 1 else 0 end) as cbt,
+    sum(case when player_user_name = 'revslowmo'     then 1 else 0 end) as rtb,
+    sum(case when player_user_name = 'jasonscheirer' then 1 else 0 end) as jes
+from log_entry_list
+where server_cwd_basename = 'Restore_the_Wasteland'
+group by 1, 2, 3
+order by 1, 2, 4 desc, 3;
+
+select session_started, session_ended, player_user_name
+from (
+    select
+        lag(time_stamp) over (order by player_user_name, time_stamp) as session_started,
+        time_stamp as session_ended,
+        player_user_name,
+        server_cwd,
+        log_entry_type_name,
+        time_stamp
+    from log_entry_list
+    where log_entry_type_name in ('Player joined', 'Player quit')
+    and server_cwd_basename = 'Restore_the_Wasteland'
+) as player_sessions
+where log_entry_type_name = 'Player quit'
+order by 1;
+
+select yyyymmdd, player_list, round(ceil(10*count(*)::decimal/60)/10, 1) as hours
+from (
+    -- Group results to one row per day per simultaneous player list
+    select
+        yyyymmdd,
+        hhmm, 
+        string_agg(
+            player_user_initials, ', '
+            order by player_user_initials
+        ) as player_list
+    from (
+        -- Convert time_stamp to text fields and player user names to initials
+        select
+            to_char(time_stamp, 'YYYY-MM-DD') as yyyymmdd,
+            to_char(time_stamp, 'HH24:MI') as hhmm,
+            case player_user_name
+                when 'careytilden' then 'cbt'
+                when 'revslowmo' then 'rtb'
+                when 'jasonscheirer' then 'jes'
+                else player_user_name
+            end as player_user_initials
+        from log_entry_list
+        where server_cwd_basename = 'Restore_the_Wasteland'
+        group by 1, 2, 3 order by 1, 2, 3
+    ) q1
+    group by 1, 2 order by 1, 2
+) q2
+group by 1, 2 order by 1, 3 desc, 2

share/RTW-summary.sql

+select
+    log_entry_type_name,
+    count(*) as total,
+    sum(case when player_user_name = 'careytilden'   then 1 else 0 end) as cbt,
+    sum(case when player_user_name = 'revslowmo'     then 1 else 0 end) as rtb,
+    sum(case when player_user_name = 'jasonscheirer' then 1 else 0 end) as jes
+from log_entry_list
+where server_cwd_basename = 'Restore_the_Wasteland'
+group by 1 order by 1;
+
+select
+    to_char(time_stamp, 'YYYY-MM-DD') as yyyymmdd,
+    log_entry_type_name,
+    count(*) as total,
+    sum(case when player_user_name = 'careytilden'   then 1 else 0 end) as cbt,
+    sum(case when player_user_name = 'revslowmo'     then 1 else 0 end) as rtb,
+    sum(case when player_user_name = 'jasonscheirer' then 1 else 0 end) as jes
+from log_entry_list
+where server_cwd_basename = 'Restore_the_Wasteland'
+group by 1, 2
+order by 1, 2;
+
+select
+    yyyymmdd,
+    round(count(*)::decimal/60, 1) as total,
+    round(sum(cbt)::decimal/60, 1) as cbt,
+    round(sum(rtb)::decimal/60, 1) as rtb,
+    round(sum(jes)::decimal/60, 1) as jes
+from (
+    select
+        to_char(time_stamp, 'YYYY-MM-DD') as yyyymmdd,
+        to_char(time_stamp, 'HH24:MI') as hhmm,
+        max(case when player_user_name = 'careytilden'   then 1 else 0 end) as cbt,
+        max(case when player_user_name = 'revslowmo'     then 1 else 0 end) as rtb,
+        max(case when player_user_name = 'jasonscheirer' then 1 else 0 end) as jes
+    from log_entry_list
+    where server_cwd_basename = 'Restore_the_Wasteland'
+    group by 1, 2 order by 1, 2
+) as q
+group by 1 order by 1;