时间戳

首页 > 解决方案 > 如何对每种类型的几个列值求和并计算百分比？

sql - 如何对每种类型的几个列值求和并计算百分比？

问题描述

我有以下查询，它为我提供了三列数据 - type，amount以及total当前一周使用week_number列的数据。

select type,
case
WHEN (type = 'PROC1' AND contractdomicilecode = 'UIT') THEN 450
WHEN (type = 'PROC1' AND contractdomicilecode = 'KJH') THEN 900
WHEN (type = 'PROC2' AND contractdomicilecode = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND contractdomicilecode = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND contractdomicilecode = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND contractdomicilecode = 'LKP') THEN 1900
END as amount,
total
from xyz.orders pa
join
(select clientid as clientid, max(version) as version
from xyz.orders where consumerid IN (select distinct entity_id from abc.items
where week_number = extract(week from current_date)
and item_type like '%Ionize - Data%' )
and createdfor ='BLOCK'
and holder='RELAY_FUTURES'
group by clientid) pb on
pa.clientid = pb.clientid and pa.version = pb.version;

以下是我现在通过上述查询返回的输出：

type    amount      total
---------------------------
PROC1    450         1768
PROC1    900         123
PROC1    450         456
PROC2    8840        99897
PROC2    1490        2223
PROC2    8840        9876
PROC3    1900        23456
PROC3    1600        12498
PROC3    1600        28756

问题陈述

现在我需要type使用以下公式计算每个百分比：

最后我应该看到这个输出：

type    sum_amount   sum_total   percentage
----------------------------------------------
PROC1    1800         2347          0.3038
PROC2    10330      111996          9.841
PROC3    5100        64710          11.688
OVERALL  17230      179053          9.3919

这可以在红移中做到吗？

标签： sqlamazon-web-servicesamazon-redshift

解决方案

您可以将其表述为：

with t as (
      <your query here>
     )
select type, sum(amount) as amount, sum(total) as total,
       (sum(total) - sum(amount)) * 1.0 / sum(amount) as ratio
from t
group by type, amount, total
union all
select 'Overall', sum(amount), sum(total),
       (sum(total) - sum(amount)) * 1.0 / sum(amount) as ratio
from t;

实际上，这会将“总体”列放在最后。但是，除非您添加ORDER BY.

推荐阅读