Carey Tilden avatar Carey Tilden committed 3e44c51

Added total rows

Comments (0)

Files changed (1)

share/SuperHostile08-deaths.sql

 \set time_group_secs (60*60)
 \set interval_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
+)
 select
     time_segment,
     deaths,
     round(deaths/minutes, 1) as "d/min",
     round(minutes/(deaths+1), 1)::real as "mins/life"
 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
+    select time_segment, minutes, deaths from time_segments
+    union all
+    select 'total', sum(minutes), sum(deaths) from time_segments
 ) q2 order by 1;
 
 with
     ),
     play_time_groups as (
         select play_time, :interval_group_secs * round(extract('epoch' from play_time)/:interval_group_secs) / 60
-            as interval_group
+            as life_len
         from play_times order by play_time
     )
-select interval_group, count(*), min(play_time), avg(play_time), max(play_time)
+select '   total' as life_len, count(*), min(play_time), avg(play_time), max(play_time)
 from play_time_groups
-group by interval_group order by 1
+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
+order by 1
+
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.