如何更高效地用Plotly绘制时序SQL数据各时段行数统计图

问题背景 #

现有一张包含datetime、value两个字段的时序数据表，需要绘制行数随30分钟时间段变化的统计图表，最终图表标题为 Number of packets received per 30 minute time period by date/time。
当前通过Python循环调用SQL的实现可正常运行，但逐次发起查询会产生大量重复的数据库请求开销，需要更优的实现方案，将统计逻辑下推到数据库侧完成，同时适配PostgreSQL和ClickHouse两种数据库。
原有循环实现代码如下：

import datetime as dt
import pandas as pd
import plotly.graph_objs

# 统计起始时间
start_date = dt.datetime(2022, 7, 5)
# 统计结束时间
end_date = dt.datetime(2022, 7, 8)
# 统计时间步长
delta = dt.timedelta(minutes=30)

y = []
x = []
# 循环逐段查询
while (start_date <= end_date):
    time_to = start_date + delta
    queryStr = f'''select '{start_date.strftime("%Y-%m-%d %H:%M:%S")}' as date, count(*) as count from my_packet_table 
                 WHERE datetime 
                 BETWEEN '{start_date.strftime("%Y-%m-%d %H:%M:%S")}'  
                 AND     '{time_to.strftime("%Y-%m-%d %H:%M:%S")}'  '''
    result = pd.read_sql_query(queryStr, cnx)
    start_date += delta
    if (len(result)) > 0:
        y.append(int(result.iloc[0][1]))
        x_date = dt.datetime.strptime( result.iloc[0][0], "%Y-%m-%d %H:%M:%S")
        x.append(x_date )

scatter = plotly.graph_objs.Scatter(x=x, y=y, mode = 'markers+lines')
layout = plotly.graph_objs.Layout(xaxis={'type': 'date', 
                                         'tick0': x[0], 
                                         'tickmode': 'linear', 
                                         'dtick': 86400000.0 / 12 }) # 2小时刻度间隔
fig = plotly.graph_objs.Figure(data=[scatter], layout=layout)
plotly.offline.iplot(fig)

解决方案 #

不需要在Python层循环发起请求，两类数据库都原生支持时间分桶统计能力，单次SQL即可返回全时段统计结果，性能远高于循环查询，数据量越大优势越明显。

PostgreSQL 实现 #

如果需要保留无数据时段（计数值为0）的时间点，先用generate_series生成所有30分钟时间窗口，再左关联数据表统计，避免时间点缺失：

WITH time_buckets AS (
  SELECT 
    bucket_start,
    bucket_start + INTERVAL '30 minutes' AS bucket_end
  FROM generate_series(
    '2022-07-05 00:00:00'::timestamp,
    '2022-07-08 00:00:00'::timestamp,
    INTERVAL '30 minutes'
  ) AS bucket_start
)
SELECT
  tb.bucket_start AS date,
  COUNT(mpt.datetime) AS count
FROM time_buckets tb
LEFT JOIN my_packet_table mpt
  ON mpt.datetime >= tb.bucket_start
 AND mpt.datetime < tb.bucket_end
GROUP BY tb.bucket_start
ORDER BY tb.bucket_start;

注意：时间范围判断用>=和<代替BETWEEN，避免相邻窗口边界值重复统计。

如果不需要补全0值空时段，直接用时间截断分桶即可，写法更简洁：

SELECT
  date_trunc('hour', datetime) + INTERVAL '30 minutes' * FLOOR(EXTRACT(MINUTE FROM datetime)/30) AS date,
  COUNT(*) AS count
FROM my_packet_table
WHERE datetime >= '2022-07-05 00:00:00'
  AND datetime < '2022-07-08 00:30:00'
GROUP BY date
ORDER BY date;

ClickHouse 实现 #

ClickHouse针对时序场景提供了原生时间分桶函数，统计性能极高：

SELECT
  toStartOfInterval(datetime, INTERVAL 30 MINUTE) AS date,
  COUNT(*) AS count
FROM my_packet_table
WHERE datetime >= '2022-07-05 00:00:00'
  AND datetime < '2022-07-08 00:30:00'
GROUP BY date
ORDER BY date;

如果需要补全无数据时段的0值，搭配timeSlots生成时间序列做左连接即可：

WITH time_buckets AS (
  SELECT arrayJoin(timeSlots(
    toDateTime('2022-07-05 00:00:00'),
    dateDiff('second', toDateTime('2022-07-05 00:00:00'), toDateTime('2022-07-08 00:30:00')),
    1800
  )) AS bucket_start
)
SELECT
  tb.bucket_start AS date,
  COUNT(mpt.datetime) AS count
FROM time_buckets tb
LEFT JOIN my_packet_table mpt
  ON mpt.datetime >= tb.bucket_start
 AND mpt.datetime < tb.bucket_start + INTERVAL 30 MINUTE
GROUP BY tb.bucket_start
ORDER BY tb.bucket_start;

Python代码改造 #

删除原有循环逻辑，单次执行SQL拉取全量统计结果即可，同时改用参数化查询避免SQL注入风险：

import datetime as dt
import pandas as pd
import plotly.graph_objs

start_date = dt.datetime(2022,7,5)
end_date = dt.datetime(2022,7,8)
# 替换为对应数据库的SQL语句，通过参数传入时间范围，不要用字符串拼接
query = """
-- 此处放入上述PostgreSQL/ClickHouse的查询SQL
"""
# 参数化传值
df = pd.read_sql_query(query, cnx, params={"start": start_date, "end": end_date + dt.timedelta(minutes=30)})

scatter = plotly.graph_objs.Scatter(x=df['date'], y=df['count'], mode='markers+lines')
layout = plotly.graph_objs.Layout(
    title='Number of packets received per 30 minute time period by date/time',
    xaxis={'type': 'date', 'tick0': df['date'].iloc[0], 'tickmode': 'linear', 'dtick': 86400000.0/12}
)
fig = plotly.graph_objs.Figure(data=[scatter], layout=layout)
plotly.offline.iplot(fig)

优化收益 #

• 仅需一次数据库请求，消除了循环查询产生的大量网络IO开销
• 数据库可自动利用datetime字段上的索引做范围扫描，统计效率比逐次查询高几十到上百倍
• 避免了字符串拼接SQL带来的注入风险
• 修正了原有BETWEEN写法可能导致的边界值重复统计、漏统计问题

内容的提问来源于stack exchange，提问作者Nick T