工作中通过ai改来改去最后实现的,非常好用
databaseVal举例:1 HOUR
interval:1 WEEK
最新,这里用到了ROW_NUMBER,就是编号,OVER就是分组,分组是通过一小时聚合,聚合后会有编号每一个组的,从1开始到该组结束,取每组的第一条就是最新的
SELECT report_time AS timeInterval, cpu_usage AS cpuUsage, mem_usage AS memUsage FROM (SELECT report_time, cpu_usage, mem_usage, ROW_NUMBER() OVER ( PARTITION BY toStartOfInterval(report_time, INTERVAL #{databaseVal}) ORDER BY report_time DESC ) as rn FROM CLOUD_NETWORK_VISUAL.T_AGENT_PERFORMANCE WHERE agent_id = #{agentId} AND report_time >= NOW() - INTERVAL #{interval}) t WHERE rn = 1 ORDER BY report_time
最大值
SELECT toStartOfInterval(report_time, INTERVAL #{databaseVal}) AS timeInterval, max(cpu_usage) FILTER (WHERE cpu_usage IS NOT NULL) AS cpuUsage, max(mem_usage) FILTER (WHERE mem_usage IS NOT NULL) AS memUsage FROM CLOUD_NETWORK_VISUAL.T_AGENT_PERFORMANCE WHERE agent_id = #{agentId} AND report_time >= NOW() - INTERVAL #{interval} GROUP BY timeInterval ORDER BY timeInterval
标签:timeInterval,INTERVAL,SQL,report,最小值,time,usage,WHERE,clickhouse From: https://www.cnblogs.com/immersed-in-the-deep-sea/p/18534971