ClickHouse SQL – hàm thời gian, quantile, arrayJoin, WITH FILL

SELECT time, source, name,
       (in_octets - prev_octets) * 8 / dateDiff('second', prev_time, time) AS bps
FROM
(
    SELECT time, source, name, in_octets,
           lagInFrame(in_octets) OVER w AS prev_octets,
           lagInFrame(time) OVER w AS prev_time
    FROM telemetry.interfaces
    WHERE source = 'pe1-hni' AND time > now() - INTERVAL 1 HOUR
    WINDOW w AS (PARTITION BY source, name ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
)
WHERE prev_time > toDateTime(0)        -- bỏ dòng đầu mỗi interface (không có mẫu trước)
  AND in_octets >= prev_octets         -- bỏ điểm counter reset
ORDER BY name, time;

SELECT toStartOfInterval(toDateTime(timestamp), INTERVAL 1 MINUTE) AS t, count() AS n
FROM logs.syslog
WHERE timestamp > now() - INTERVAL 1 HOUR AND source = 'pe1-hni'
GROUP BY t
ORDER BY t WITH FILL
  FROM toStartOfInterval(now() - INTERVAL 1 HOUR, INTERVAL 1 MINUTE)
  TO now()
  STEP INTERVAL 1 MINUTE;

SELECT arrayJoin(extractAll(message, '\\d+\\.\\d+\\.\\d+\\.\\d+')) AS ip, count() AS n
FROM logs.syslog
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY ip ORDER BY n DESC LIMIT 10;