我在 Impala (cloudera)工作,我有两张table,客户和安排。customer表包含以下列:
customercrs | customertype|
------------+-------------+
1000 | NP |
100000 | NP |
100001 | NP |
100002 | GROUP |
100023 | GROUP |
100024 | INDIRECT |
排列表:
customercrs | arrangementid|
------------+--------------+
1000 | 11000000361 |
100000 | 11000000370 |
100000 | 11000000434 |
100000 | 11000000426 |
100001 | 11000000418 |
100001 | 11000000400 |
100001 | 11000000396 |
100001 | 11000000388 |
100002 | 11000000591 |
100002 | 11000000582 |
100023 | 11000000574 |
100024 | 11000000566 |
100024 | 11000000558 |
我想计算每个客户类型的安排百分比。比如:
customertype | percentage |
-------------+-------------+
NP | 62% |
GROUP | 23% |
INDIRECT | 15% |
我尝试了下面的sql查询,但没有成功。你知道吗?
select customertype, count(*)/(select count(*) from arrangements)
from customers as a, arrangements_sample as b
where a.customercrs = b.customercrs
group by a.customertype
谢谢!!!
3条答案
按热度按时间yfwxisqw1#
尝试加入subselect,我使用max作为组函数,但是min或avg也可以。。。
q3qa4bjr2#
我会将窗口函数与显式函数一起使用
JOIN
,但是,您的解决方案似乎很好(对于impala以外的其他dbms)q1qsirdb3#
您需要每个customertype参与总的排列计数。所以尝试下面的查询。
选择main.customertype,cast((cast(main.participation as decimal(10,2))/main.total)*100 as decimal(10,2))作为参与源(选择customertype,count(1)作为参与源,(选择count(1)from arrangements)as total from arrangements a inner join customers b on b.customercrs=a.customercrs group by b.customertype)作为主