如何在BigQuery中生成累积分布图所需数据并解决相关问题

我需要生成一张累积分布图，展示给定数值下小于等于该值的数据占比。在Python中结合matplotlib与pandas时，可通过pandas（或numpy）的quantile函数实现，代码如下：

import numpy as np 
import matplotlib.pyplot as plt 

def plot_quantile(series, start=0., end=0.99):
    y = np.linspace(start,end, 500)
    x = series.quantile(y)
    plt.plot(x, y*100)
    plt.xlabel("Duration in minutes")
    plt.ylabel("% of drives less than x minutes") 
    plt.grid()
    
plot_quantile(df.duration)

这段代码基于NYC出租车数据集绘制行程时长的累积分布。

目前我用BigQuery的SQL查询生成对应数据，当前语句为：

select
    approx_quantiles(duration, 100) as duration_quantile
from base_table

该查询返回101个数据点（覆盖最小值到最大值），但存在两个问题：

• 无法直接对应每个数值的分位数（比如哪一个是P50），缺少绘图所需的分位占比数值；
• 无法截断顶部极大异常值，导致图表可读性差。

解决方案 #

针对这两个问题，可通过以下SQL语句优化：

with quantiles as (
    -- 生成0到99分位的100个数据点，同时过滤掉99分位以上的异常值
    select approx_quantiles(duration, 99) as duration_quantile
    from base_table
    where duration <= (select approx_quantiles(duration, 100)[offset(99)] from base_table)
)
select
    q as duration,
    -- 计算每个分位数对应的累积占比（百分比）
    (index * 100) / array_length(duration_quantile) as percentile
from quantiles, unnest(duration_quantile) q with offset index

说明：

1. 匹配分位数与占比：通过unnest(duration_quantile) q with offset index获取每个分位数值的索引，再通过索引计算对应的累积占比（例如索引50对应50%，即P50），直接得到绘图所需的x（时长）和y（占比）字段。
2. 截断异常值：通过where duration <= (select approx_quantiles(duration, 100)[offset(99)] from base_table)过滤掉99分位以上的极大值，避免图表被异常值拉伸，提升可读性。

如果需要更精细的分位粒度（比如和Python代码中500个数据点一致），只需调整approx_quantiles的第二个参数为499，生成500个分位数据点：

with quantiles as (
    select approx_quantiles(duration, 499) as duration_quantile
    from base_table
    where duration <= (select approx_quantiles(duration, 100)[offset(99)] from base_table)
)
select
    q as duration,
    (index * 100) / array_length(duration_quantile) as percentile
from quantiles, unnest(duration_quantile) q with offset index

调整后的查询结果可直接用于绘制累积分布图，完美匹配Python实现的效果。

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