mv unions 2

WITH table_traffic as (select attack_id, timestamp, protocol, destinationPort, sourcePort, 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, protocol, destinationPort, sourcePort, srcIp
     order by timestamp),
table_protocoled as (select attack_id, timestamp, protocol, sum(bps) as bps, sum(pps) as pps
     from table_traffic
    where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     group by attack_id, timestamp, protocol
     order by timestamp, protocol),
table_destination_tcp_ported as (select attack_id, timestamp, destinationPort, sum(bps) as bps, sum(pps) as pps
     from table_traffic
     where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' and protocol = 6
     group by attack_id, timestamp, destinationPort
     order by timestamp, destinationPort),
table_destination_udp_ported as (select attack_id, timestamp, destinationPort, sum(bps) as bps, sum(pps) as pps
     from table_traffic
     where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' and protocol = 17
     group by attack_id, timestamp, destinationPort
     order by timestamp, destinationPort),
table_source_tcp_ported as (select attack_id, timestamp, sourcePort, sum(bps) as bps, sum(pps) as pps
     from table_traffic
     where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' and protocol = 6
     group by attack_id, timestamp, sourcePort
     order by timestamp, sourcePort),
table_source_udp_ported as (select attack_id, timestamp, sourcePort, sum(bps) as bps, sum(pps) as pps
     from table_traffic
     where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67' and protocol = 17
     group by attack_id, timestamp, sourcePort
     order by timestamp, sourcePort),
table_source_iped as (select attack_id, timestamp, srcIp, sum(bps) as bps, sum(pps) as pps
     from table_traffic
     where attack_id = 'fab4ea04-e1d7-4628-9c9b-3f5ebcb2da67'
     group by attack_id, timestamp, srcIp
     order by timestamp, srcIp)

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 table_source_iped
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 table_source_iped
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 table_source_tcp_ported
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 table_source_udp_ported
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 table_destination_udp_ported
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 table_destination_tcp_ported
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 table_traffic
group by attack_id, timestamp
order by timestamp