如何计算每个条目占总金额的百分比

Question

我有一个按标题存储请求的表。我可以用以下内容查询：

SELECT lower(btrim(environment)) as environment,
lower(btrim(title)) as title,
SUM(count) as requests
FROM api_data_analytics
WHERE timestamp BETWEEN '2019-10-01 00:00:00.000000' AND '2019-11-01 00:00:00.000000'
AND environment = 'production'
GROUP BY 1, 2;

这产生：

env:        title:     requests:
production    a1       18194440913
production    b1       2425465014
production    c1       265733967
production    d1       92586792
production    e1       57150246

我希望能够执行一个查询，为我提供每个标题的总请求的%，类似于：

env:        title:     pct_total:
production    a1       85.0
production    b1       13.2
production    c1       1.4
production    d1       0.3
production    e1       0.1

获取这些数据最简单的方法是什么？

Answer 1

使用窗口函数：

SELECT lower(btrim(environment)) as environment,
lower(btrim(title)) as title,
SUM(count) as requests,
SUM(count) * 100.0 / SUM(SUM(count)) OVER () as percent_total
FROM api_data_analytics
WHERE timestamp BETWEEN '2019-10-01 00:00:00.000000' AND '2019-11-01 00:00:00.000000' AND
environment = 'production'
GROUP BY 1, 2;