Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
2.4 kB
1
Indexable
Never

WITH eventlog_0 AS (select sparkline_bucket,sourcetype,sparkline_value,sparkline_id from eventlog),
eventlog_1 AS (
	WITH all_buckets AS (
	    select
		generate_series as start_time
	    from
		generate_series (2, 8, 2)
	),
	distinct_group_values as (
	    select distinct sourcetype from eventlog_0 WHERE sparkline_id = 1
	),
	all_buckets_group_values as (
	    select a.start_time, b.sourcetype from all_buckets a CROSS JOIN distinct_group_values b
	),
	temp_table1 AS (
	    SELECT a.start_time, a.sourcetype, COALESCE(e.sparkline_value, 0) AS sparkline_value
	    FROM all_buckets_group_values a
	    LEFT JOIN eventlog_0 e ON a.start_time = e.sparkline_bucket AND a.sourcetype=e.sourcetype AND e.sparkline_id == 1
	    ORDER BY a.sourcetype, a.start_time
	)
	select sourcetype, array_agg(sparkline_value) as sparkline_value,1 as sparkline_id from temp_table1 group by sourcetype
            ),
eventlog_2 AS (SELECT sourcetype,sparkline_value,sparkline_id FROM eventlog_1),
eventlog_3 AS (select sparkline_bucket,sourcetype,sparkline_value,sparkline_id from eventlog),
eventlog_4 AS (
	WITH all_buckets AS (
	    select
		generate_series as start_time
	    from
		generate_series (3, 9, 3)
	),
	distinct_group_values as (
	    select distinct sourcetype from eventlog_3 WHERE sparkline_id = 2
	),
	all_buckets_group_values as (
	    select a.start_time, b.sourcetype from all_buckets a CROSS JOIN distinct_group_values b
	),
	temp_table2 AS (
	    SELECT a.start_time, a.sourcetype, COALESCE(e.sparkline_value, 0) AS sparkline_value
	    FROM all_buckets_group_values a
	    LEFT JOIN eventlog_3 e ON a.start_time = e.sparkline_bucket AND a.sourcetype=e.sourcetype AND e.sparkline_id == 2
	    ORDER BY a.sourcetype, a.start_time
	)
	select sourcetype, array_agg(sparkline_value) as sparkline_value,2 as sparkline_id from temp_table2 group by sourcetype
            ),
eventlog_5 AS (SELECT sourcetype,sparkline_value,sparkline_id FROM eventlog_4),
eventlog_6 AS (SELECT * FROM eventlog_2 UNION ALL (SELECT * FROM eventlog_5)),
eventlog_7 AS (
  SELECT
    sourcetype,
    GROUP_CONCAT(sparkline_value) FILTER (WHERE sparkline_id = 1) AS sparkline_bytes_in,
    GROUP_CONCAT(sparkline_value) FILTER (WHERE sparkline_id = 2) AS sparkline_bytes_out
  FROM eventlog_6
  GROUP BY sourcetype
)
SELECT sourcetype, sparkline_bytes_in, sparkline_bytes_out FROM eventlog_7 LIMIT 1000;
Leave a Comment