amazon-redshift - Redshift - 找到加权平均值

Question

我正在尝试使用以下数据计算加权平均值

project_name, project_type, sales
project_a, type_1, 2
project_a, type_2, 4
project_a, type_3, 6
project_b, type_1, 4

预期输出：

project_name, project_type, sales, wgt_average
project_a, type_1, 2, 0.17
project_a, type_2, 4, 0.33
project_a, type_3, 6, 0.5
project_b, type_1, 4, 1

下面给出的是我的 SQL：

select project_name, project_type, count(project_type) * sum(sales) / nullif(count(sales),0)
from table 

上面给我的 SQL 按每个 project_name / project_type 计算。如何获得 project_name、project_type 组合的加权平均值

标签： amazon-redshift

Answer 1

我们可以SUM在这里用作分析函数：

SELECT
    project_name,
    project_type,
    sales,
    1.0*sales / SUM(sales) OVER (PARTITION BY project_name) As wgt_average
FROM yourTable
ORDER BY
    project_name,
    project_type;

演示