mysql - SQL - 如何计算 x/y 的标准差 (stddev)？

Question

示例:计算点击率 (CTR)

CAST(SUM(num_clicks) as DOUBLE) / CAST(SUM(num_impression) as DOUBLE) as CTR

如何计算点击率的标准差？ stddev(CTR) 不起作用，因为它将是一个嵌套聚合函数...另外，我无法先计算 CTR，然后在其上运行 stddev。

Answer 1

您可以先计算点击率，然后使用子查询对其运行 STDDEV_POP。

SELECT STDDEV_POP(ctr) AS ctr_stddev
FROM (
    SELECT CAST(SUM(num_clicks) as DOUBLE) / CAST(SUM(num_impression) as DOUBLE) AS ctr
    FROM yourTable
    WHERE ...
    GROUP BY ad_id) AS temp