1. Carey Tilden
  2. WhatHappened

Commits

Carey Tilden  committed 063b7dd

Reworked life length distribution to avoid counting idle time

  • Participants
  • Parent commits 3e44c51
  • Branches default

Comments (0)

Files changed (1)

File share/SuperHostile08-deaths.sql

View file
 \set time_group_secs (60*60)
-\set interval_group_secs (5*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 = 'SuperHostile08' group by 1
-    ) q1 group by 1
-)
+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 = 'SuperHostile08' group by 1
+        ) q1 group by 1
+    )
 select
     time_segment,
     deaths,
 ) 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
+    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 = 'SuperHostile08'
     ),
-    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
+    lives as (
+        select row_number() over () as life_id, q.*
         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'
+                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 this_action = 'started'
+        where life_boundary = 'start'
     ),
-    play_times as (
+    minutes_per_life as (
+        select life_id, count(*) as life_len_minutes from (
+            select life_id, date_trunc('minute', actions.time_stamp) as minute
+            from lives join actions on actions.time_stamp between lives.time_stamp1 and lives.time_stamp2
+            group by 1, 2
+        ) q
+        group by 1 order by 1
+    ),
+    life_len_groups 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 * round(extract('epoch' from play_time)/:interval_group_secs) / 60
-            as life_len
-        from play_times order by play_time
+            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(play_time), avg(play_time), max(play_time)
-from play_time_groups
+select '   total' as life_len, count(*), min(life_len_minutes), round(avg(life_len_minutes), 1)::real as avg, max(life_len_minutes)
+from life_len_groups
 union all
-select to_char(life_len, '9999999'), count(*), min(play_time), avg(play_time), max(play_time)
-from play_time_groups
-group by life_len
+select to_char(life_len_group, '9999999'), count(*), min(life_len_minutes), round(avg(life_len_minutes), 1)::real, max(life_len_minutes)
+from life_len_groups
+group by life_len_group
 order by 1
-