postgresql 一种趋势图数据的查询方式

查询的是数据的条数，每分钟、每小时、每天的趋势图都较容易，如果自定义为10分钟如何处理

1、将查询时间从起始时间开始，每十分钟记录一次，按十分钟分组拼接

SELECT a.year_month_day, COALESCE ( b.myconut, 0 ) AS count
FROM
(
    SELECT to_char ( b, 'YYYY-MM-DD HH24:mi' ) AS year_month_day
    FROM generate_series ( to_timestamp ( #{startTime}, 'YYYY-MM-DD hh24:mi' ), to_timestamp ( #{endTime}, 'YYYY-MM-DD hh24:mi' ), '10 minute' ) AS b
    GROUP BY year_month_day
) AS a
LEFT JOIN
(
    SELECT concat (to_char(gather_time, 'yyyy-mm-dd HH24'), ':',
    FLOOR(date_part( 'minute', gather_time )/10),
    extract(minute from to_timestamp ( #{startTime}, 'YYYY-MM-DD hh24:mi' ))::int%10) AS year_month_day, count( source_ip ) AS myconut
    FROM lcs_statistics_source
    WHERE gather_time BETWEEN #{startTime} AND #{endTime}
        GROUP BY year_month_day
) AS b ON a.year_month_day = b.year_month_day
ORDER BY a.year_month_day ASC

函数

generate_series 以步长为节点拆分时间

to_timestamp string转date
to_char date转string

FLOOR 向下取整

date_part 取时间值

2、以每10分钟的整数时间返回

SELECT a.year_month_day, COALESCE ( b.myconut, 0 ) AS count
FROM
(
    SELECT to_char ( b, 'YYYY-MM-DD HH24:mi' ) AS year_month_day
    FROM generate_series (
    to_timestamp ( concat(substr(#{startTime},0,16) ,'0'),'YYYY-MM-DD hh24:mi' ),
    to_timestamp ( #{endTime}, 'YYYY-MM-DD hh24:mi' ), '10 minute' ) AS b
    GROUP BY year_month_day
) AS a
LEFT JOIN
(
    SELECT concat (to_char(gather_time, 'yyyy-mm-dd HH24'), ':',
    FLOOR(date_part( 'minute', gather_time )/10),
    '0') AS year_month_day, count( source_ip ) AS myconut
    FROM lcs_statistics_source
    WHERE gather_time BETWEEN #{startTime} AND #{endTime}
        GROUP BY year_month_day
) AS b ON a.year_month_day = b.year_month_day
ORDER BY a.year_month_day ASC