Commits

Carey Tilden  committed 269dd00

Added script for new map

  • Participants
  • Parent commits 2ed559c

Comments (0)

Files changed (1)

File 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
+    actions as (
+        select
+            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 log_entry_type_name = 'Player died' then 'stop'
+                when lead(log_entry_type_name) over (order by time_stamp) is null then 'stop'
+            end as life_boundary
+        from log_entry_list where server_cwd_basename = 'SuperHostile14'
+    ),
+    lives as (
+        select row_number() over () as life_id, q.*
+        from (
+            select
+                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
+            from actions
+            where actions.life_boundary is not null
+        ) q
+        where life_boundary = 'start'
+    ),
+    minutes_per_life as (
+        select life_id,
+            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,
+                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
+        ) q
+        group by life_id, clock_minutes order by 1
+    ),
+    life_len_groups as (
+        select
+            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)
+from life_len_groups
+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)
+from life_len_groups
+group by life_len_group
+order by 1