mv unions

WITH table_protocoled AS (SELECT attack_id, TIMESTAMP, protocol, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
    WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     GROUP BY attack_id, TIMESTAMP, protocol
     ORDER BY TIMESTAMP, protocol)
SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/proto/peak' AS GROUP, protocol AS group_value,
       MAX(bps) AS bits, MAX(pps) AS packets
    FROM table_protocoled
GROUP BY attack_id, TIMESTAMP, protocol
ORDER BY TIMESTAMP, protocol

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/proto' AS GROUP, protocol AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM table_protocoled
GROUP BY attack_id, TIMESTAMP, protocol
ORDER BY TIMESTAMP, protocol

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/src-asn' AS GROUP, srcAsn AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, srcAsn, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     GROUP BY attack_id, TIMESTAMP, srcAsn
     ORDER BY TIMESTAMP, srcAsn)
GROUP BY attack_id, TIMESTAMP, srcAsn
ORDER BY TIMESTAMP, srcAsn

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/src-ip' AS GROUP, srcIp AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, srcIp, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     GROUP BY attack_id, TIMESTAMP, srcIp
     ORDER BY TIMESTAMP, srcIp)
GROUP BY attack_id, TIMESTAMP, srcIp
ORDER BY TIMESTAMP, srcIp

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/src-ip/peak' AS GROUP, srcIp AS group_value,
       MAX(bps) AS bits, MAX(pps) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, srcIp, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     GROUP BY attack_id, TIMESTAMP, srcIp
     ORDER BY TIMESTAMP, srcIp)
GROUP BY attack_id, TIMESTAMP, srcIp
ORDER BY TIMESTAMP, srcIp

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/tcp/src-port' AS GROUP, sourcePort AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, sourcePort, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' AND protocol = 6
     GROUP BY attack_id, TIMESTAMP, sourcePort
     ORDER BY TIMESTAMP, sourcePort)
GROUP BY attack_id, TIMESTAMP, sourcePort
ORDER BY TIMESTAMP, sourcePort

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/udp/src-port' AS GROUP, sourcePort AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, sourcePort, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' AND protocol = 17
     GROUP BY attack_id, TIMESTAMP, sourcePort
     ORDER BY TIMESTAMP, sourcePort)
GROUP BY attack_id, TIMESTAMP, sourcePort
ORDER BY TIMESTAMP, sourcePort

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/udp/dst-port' AS GROUP, destinationPort AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, destinationPort, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' AND protocol = 17
     GROUP BY attack_id, TIMESTAMP, destinationPort
     ORDER BY TIMESTAMP, destinationPort)
GROUP BY attack_id, TIMESTAMP, destinationPort
ORDER BY TIMESTAMP, destinationPort

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-in/tcp/dst-port' AS GROUP, destinationPort AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, destinationPort, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' AND protocol = 6
     GROUP BY attack_id, TIMESTAMP, destinationPort
     ORDER BY TIMESTAMP, destinationPort)
GROUP BY attack_id, TIMESTAMP, destinationPort
ORDER BY TIMESTAMP, destinationPort

UNION ALL

SELECT attack_id, toStartOfInterval(TIMESTAMP, INTERVAL 10 SECOND) AS TIMESTAMP, 'ext-int/total' AS GROUP, 0 AS group_value,
       toUInt64(FLOOR(SUM(bps) / 10)) AS bits, toUInt64(FLOOR(SUM(pps) / 10)) AS packets
    FROM (
     SELECT attack_id, TIMESTAMP, SUM(packetPayloadFullLength * 8 * sampleRatio) AS bps, SUM(sampleRatio) AS pps
     FROM cloud_stat_dev.raw_traffic
     WHERE attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' AND protocol = 6
     GROUP BY attack_id, TIMESTAMP
     ORDER BY TIMESTAMP)
GROUP BY attack_id, TIMESTAMP
ORDER BY TIMESTAMP