sql - 旋转计算雪花

Question

我在下面有输出

其中sum(minutes)用作comp_type和ram_type组的聚合

comp_type | ram_type | Minutes
    PC           4         50
    PC           6         65
    PC           8         100
    PC           16        160
    Mac          4         20
    Mac          6         45
    Mac          8         74
    Mac          16        88

我怎样才能将其转至我的输出低于总金额占每个 comp_type 总数的百分比的位置？

ram_type   PC   Mac
4         .13   .09
6         .17   .20
8         .27   .33
16        .43   .39

谢谢

标签： sqlsnowflake-cloud-data-platform

Answer 1

我只会使用条件聚合：

select ram_type,
       max(case when comp_type = 'PC' then minutes end) / ram_type as pc,
       max(case when comp_type = 'Mac' then minutes end) / ram_type as mac
from t
group by ram_type;

编辑：

我可能误解了计算。如果您希望分钟数占总数的比例：

select ram_type,
       (sum(case when comp_type = 'PC' then minutes end) /
        sum(sum(case when comp_type = 'PC' then minutes end)) over ()
       ) as pc,
       (sum(case when comp_type = 'Mac' then minutes end) /
        sum(sum(case when comp_type = 'Mac' then minutes end)) over ()
       ) as mac
from t
group by ram_type;