Carey Tilden avatar Carey Tilden committed eeabf6c

Added detailed list of deaths

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
---            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 (
+    time_segments 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 = 'SuperHostile14' and player_user_name is not null
-    ),
-    lives as (
-        select row_number() over () as life_id, q.*
+            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;
+
+drop table if exists minutes_per_life;
+
+create temporary table minutes_per_life as
+    with
+        actions as (
             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'
-    ),
-    minutes_per_life as (
+                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 = '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 (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
             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
+            group by 1, 2, 3, 4, 5
         ) q
-        group by life_id, player_user_name, clock_minutes order by 1
-    ),
+        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,
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.