sql - 如何计算每个条目占总和的百分比？

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

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

标签： sqlamazon-redshift

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;