Untitled
unknown
plain_text
a year ago
960 B
4
Indexable
WITH daily_p99_cross_region AS ( SELECT date_trunc('day', date_parse(date, '%Y-%m-%d')) AS day, sdp, approx_percentile(bytes, 0.99) AS p99_value FROM your_table_name WHERE NOT regexp_like(sdp, '_\\w+_\\w+') -- Exclude rows where letters after the second underscore are the same GROUP BY day, sdp ), monthly_average_cross_region AS ( SELECT date_format(day, '%Y-%m') AS year_month, sdp, AVG(p99_value) AS avg_p99_value FROM daily_p99_cross_region GROUP BY year_month, sdp ), sdp_total_avg_cross_region AS ( SELECT sdp, SUM(avg_p99_value) AS total_avg_p99 FROM monthly_average_cross_region GROUP BY sdp ) SELECT flowtype, COALESCE(SUM(total_avg_p99), 0) AS total_avg_p99_by_flowtype FROM your_table_name t LEFT JOIN sdp_total_avg_cross_region s ON t.sdp = s.sdp GROUP BY flowtype;
Editor is loading...
Leave a Comment