sparklines.sql

WITH minutes AS
  (SELECT CASE
              WHEN job_name = '' THEN '<idle>'
              WHEN job_name like 'com.snapsort.logloader.%' then substr(job_name, length('com.snapsort.logloader.') + 1) 
              ELSE job_name 
          END AS job_name,
          job_id,
          date_trunc('minute', ts) as ts,
          count(DISTINCT HOST) AS servers,
          sum(LOAD) AS LOAD,
          avg(load)::numeric(10,1)::text as avg_load,
          sum(rx) as rx,
          sum(tx) as tx
   FROM mesos
   GROUP BY 1,
            2,3),
inputs as (
select job_name, job_id, min(ts) as start_time, count(distinct ts) as duration, max(servers) as servers, sum(load) as load, sum(rx) / 1024 / 1024 as rx_mb, sum(tx) / 1024 / 1024 as tx_mb,
sum(load) / sum(servers) as avg_load,
string_agg(avg_load, ',' order by ts) as load_values
from minutes
group by 1, 2
)
SELECT job_name AS "job_name::filter",
       CASE
           WHEN job_name LIKE '%Adtags%' THEN 'No history'
           ELSE '<a href="https://xxx/history/' || job_id || '"</a>History</a>'
       END AS link,
       start_time,
       servers,
       duration,
       avg_load,
       rx_mb,
       tx_mb,
       '<a href="https://xxx/queries/3388/source?p_job_id=' || job_id || '#4176"><img src="https://image-charts.com/chart?chs=200x50&cht=lc:nda&chxr=0,0,4,1&chco=0077CC&chd=t:4,' || load_values || '"></a>' as load_spark
FROM inputs
ORDER BY 1,
         2 DESC