超大规模交易数据可视化求助（BigQuery+Jupyter场景）

针对1.27亿行BigQuery数据、Jupyter内存有限的问题，提供以下三种可行方案，兼顾性能与异常值保留：

方案一：BigQuery端计算统计量+抽样异常值（推荐） #

在云端完成核心统计计算，仅拉取箱线必需的分位数数据+少量异常值样本，大幅降低本地内存占用，同时完整保留异常值信息。

第一步：BigQuery SQL查询（计算统计量+抽样异常值） #

WITH hourly_stats AS (
  SELECT
    EXTRACT(HOUR FROM timestamp_column) AS hour,
    PERCENTILE_CONT(priceNum, 0.25) OVER (PARTITION BY EXTRACT(HOUR FROM timestamp_column)) AS q1,
    PERCENTILE_CONT(priceNum, 0.5) OVER (PARTITION BY EXTRACT(HOUR FROM timestamp_column)) AS median,
    PERCENTILE_CONT(priceNum, 0.75) OVER (PARTITION BY EXTRACT(HOUR FROM timestamp_column)) AS q3,
    MIN(priceNum) OVER (PARTITION BY EXTRACT(HOUR FROM timestamp_column)) AS min_val,
    MAX(priceNum) OVER (PARTITION BY EXTRACT(HOUR FROM timestamp_column)) AS max_val,
    priceNum
  FROM
    `你的项目ID.你的数据集.你的表名`
),
hourly_iqr AS (
  SELECT
    hour,
    q1,
    median,
    q3,
    min_val,
    max_val,
    q3 - q1 AS iqr,
    priceNum
  FROM hourly_stats
),
outliers AS (
  SELECT
    hour,
    priceNum
  FROM hourly_iqr
  -- 筛选异常值（超出1.5倍IQR范围）
  WHERE priceNum < q1 - 1.5*iqr OR priceNum > q3 + 1.5*iqr
  -- 每个小时最多抽1000条异常值，避免数据量过大
  QUALIFY ROW_NUMBER() OVER (PARTITION BY hour ORDER BY RAND()) <= 1000
),
summary_stats AS (
  SELECT DISTINCT
    hour,
    q1,
    median,
    q3,
    min_val,
    max_val,
    q1 - 1.5*(q3 - q1) AS whislo,
    q3 + 1.5*(q3 - q1) AS whishi
  FROM hourly_iqr
)
SELECT
  s.*,
  o.priceNum AS outlier_price
FROM summary_stats s
LEFT JOIN outliers o ON s.hour = o.hour

第二步：本地Plotly绘制箱线图 #

将上述查询结果导入DataFrame后，用统计量生成箱线，再叠加异常值散点：

import pandas as pd
import plotly.graph_objects as go
import numpy as np
from google.cloud import bigquery

client = bigquery.Client()
# 执行SQL并获取结果
query = """-- 上面的SQL语句--"""
df = client.query(query).to_dataframe()

N = 24
color_list = [f'hsl({h},50%,50%)' for h in np.linspace(0, 360, N)]

fig = go.Figure()

# 添加箱线（基于统计量）
for hour in range(N):
    stats = df[df['hour'] == hour].iloc[0]
    fig.add_trace(go.Box(
        x=[hour],
        q1=[stats['q1']],
        median=[stats['median']],
        q3=[stats['q3']],
        lowerfence=[stats['whislo']],
        upperfence=[stats['whishi']],
        marker_color=color_list[hour],
        name=f'小时 {hour}',
        boxpoints=False
    ))

# 添加异常值散点
for hour in range(N):
    outliers = df[df['hour'] == hour]['outlier_price'].dropna().tolist()
    if outliers:
        fig.add_trace(go.Scatter(
            x=[hour]*len(outliers),
            y=outliers,
            mode='markers',
            marker=dict(color=color_list[hour], size=4),
            showlegend=False
        ))

fig.update_layout(
    xaxis=dict(showgrid=True, zeroline=True, showticklabels=True),
    yaxis=dict(zeroline=True, gridcolor='white'),
    paper_bgcolor='rgb(233,233,233)',
    plot_bgcolor='rgb(233,233,233)',
    autosize=False,
    width=1500,
    height=1000,
    title="全年价格波动（含异常值）",
)

fig.show()

方案二：分批次加载单小时数据 #

分24次从BigQuery拉取单个小时的数据，每次仅处理1/24的数据集，避免内存过载。若单小时数据仍过大，可在SQL中分层抽样（正常数据抽样本，异常值全保留）。

代码示例 #

import pandas as pd
import plotly.graph_objects as go
import numpy as np
from google.cloud import bigquery

client = bigquery.Client()
N = 24
color_list = [f'hsl({h},50%,50%)' for h in np.linspace(0, 360, N)]

fig = go.Figure()

for hour in range(N):
    # 分层抽样SQL：正常数据抽10%，异常值全保留
    query = f"""
    WITH hourly_data AS (
      SELECT
        priceNum,
        {hour} AS hour,
        PERCENTILE_CONT(priceNum, 0.25) OVER () AS q1,
        PERCENTILE_CONT(priceNum, 0.75) OVER () AS q3
      FROM `你的项目ID.你的数据集.你的表名`
      WHERE EXTRACT(HOUR FROM timestamp_column) = {hour}
    )
    SELECT priceNum, hour
    FROM hourly_data
    WHERE 
      (priceNum BETWEEN q1 - 1.5*(q3-q1) AND q3 + 1.5*(q3-q1) AND RAND() < 0.1)
      OR (priceNum < q1 - 1.5*(q3-q1) OR priceNum > q3 + 1.5*(q3-q1))
    """
    df_hour = client.query(query).to_dataframe()
    
    # 添加该小时的箱线图
    fig.add_trace(go.Box(
        x=df_hour['hour'],
        y=df_hour['priceNum'],
        marker_color=color_list[hour],
        name=f'小时 {hour}'
    ))

fig.update_layout(
    xaxis=dict(showgrid=True, zeroline=True, showticklabels=True),
    yaxis=dict(zeroline=True, gridcolor='white'),
    paper_bgcolor='rgb(233,233,233)',
    plot_bgcolor='rgb(233,233,233)',
    autosize=False,
    width=1500,
    height=1000,
    title="全年价格波动",
)

fig.show()

方案三：云端可视化工具直接生成 #

完全跳过本地Jupyter，用BigQuery控制台自带的可视化功能，或连接Looker Studio（免费）直接基于BigQuery数据源生成箱线图，所有计算与渲染均在云端完成，无需担心本地内存限制。

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