Commits

Carey Tilden committed 22addac

Created sql for analyzing superhostile 8 data

Comments (0)

Files changed (2)

share/SuperHostile08-deaths.sql

+\set time_group_secs (60*60)
+\set interval_group_secs (5*60)
+
+select
+    time_segment,
+    deaths,
+    minutes,
+    round(deaths/minutes, 1) as "d/min"
+from (
+    select
+        timestamp with time zone 'epoch' + interval '1 second'
+            * floor(extract('epoch' from time_stamp_minute) / :time_group_secs) * :time_group_secs
+            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 = 'SuperHostile08' group by 1
+    ) q1 group by 1
+) q2 order by 1;
+
+with
+    death_log as (
+        select q2.* from (
+            select q1.*, case
+                when death = 1 then 'died'
+                when log_entry_type_name = 'Player joined' then 'started'
+                when log_entry_type_name = 'Player quit' then 'stopped'
+                when lag(death) over (order by time_stamp) is null then 'started'
+                when lag(death) over (order by time_stamp) = 1 then 'started'
+                when lead(death) over (order by time_stamp) is null then 'stopped'
+                end as action
+            from (
+                select time_stamp, case when log_entry_type_name = 'Player died' then 1 else 0 end as death,
+                    log_entry_type_name
+                from log_entry_list where server_cwd_basename = 'SuperHostile08'
+            ) q1
+        ) q2
+        where action is not null
+    ),
+    windowed as (
+        select
+            time_stamp as this_time_stamp,
+            action as this_action,
+            lag(time_stamp) over (order by time_stamp) as prev_time_stamp,
+            lag(action) over (order by time_stamp) as prev_action,
+            lead(time_stamp) over (order by time_stamp) as next_time_stamp,
+            lead(action) over (order by time_stamp) as next_action
+        from death_log
+    ),
+    detail as (
+        select
+            this_time_stamp as starting_time_stamp,
+            next_time_stamp as ending_time_stamp,
+            next_time_stamp - this_time_stamp as time_elapsed,
+            next_action as closing_action
+        from windowed
+        where this_action = 'started'
+    ),
+    bookends as (
+        select this_bookend, that_bookend, row_number() over () as bookend_rownum
+        from (
+            select
+                this_action,
+                this_time_stamp as this_bookend,
+                lead(this_time_stamp) over (order by this_time_stamp) as that_bookend
+            from windowed
+            where prev_action is null
+            or prev_action = 'died'
+            or next_action is null
+            or next_action = 'started' and this_action = 'died'
+        ) q
+        where this_action = 'started'
+    ),
+    play_times as (
+        select
+            bookends.this_bookend,
+            bookends.that_bookend,
+            bookends.that_bookend - bookends.this_bookend as clock_time,
+            sum(detail.time_elapsed) as play_time
+        from detail join bookends
+            on detail.starting_time_stamp between bookends.this_bookend and bookends.that_bookend
+        group by bookends.bookend_rownum, bookends.this_bookend, bookends.that_bookend
+        order by 1
+    ),
+    play_time_groups as (
+        select play_time, :interval_group_secs * ceil(extract('epoch' from play_time)/:interval_group_secs) / 60
+            as interval_group
+        from play_times order by play_time
+    )
+select interval_group, count(*), min(play_time), avg(play_time), max(play_time)
+from play_time_groups
+group by interval_group order by 1

share/SuperHostile08-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 = 'SuperHostile08'
+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 = 'SuperHostile08'
+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 = 'SuperHostile08'
+    group by 1, 2 order by 1, 2
+) as q
+group by 1 order by 1;