一个account表,要查询单位开通了手机短号的用户数,总用户数和开通比例,最后显示的数据结果表如下:
单位 开通人数 总人数 比例。
account表有如字段:id(用户号),accountid(账号),orgid(单位id),
当用户开通了短号后就会生成一条id相同,accountid不同的数据,现在要统计开通了的比例,请教sql要怎么写?
select a.orgid
(select count(id) from account
where id in(select id from account where orgid=a.orgid
having count(id)>1 group by id)) as opentotal,
count(*) as total from account a;
还有就是比例怎么算出来,我用trunc(opentotal/total,2),出现opentotal,total无效的情况,急啊!
单位 开通人数 总人数 比例。
account表有如字段:id(用户号),accountid(账号),orgid(单位id),
当用户开通了短号后就会生成一条id相同,accountid不同的数据,现在要统计开通了的比例,请教sql要怎么写?
select a.orgid
(select count(id) from account
where id in(select id from account where orgid=a.orgid
having count(id)>1 group by id)) as opentotal,
count(*) as total from account a;
还有就是比例怎么算出来,我用trunc(opentotal/total,2),出现opentotal,total无效的情况,急啊!
--参考:
select orgid,open_people,sum_people,
trunc(open_people/sum_people,2) proportion
from (
select orgid,count(id) open_people,
count(orgid) over(partition by orgid order by id) sum_people
from accounts
group by orgid)
Oracle/PLSQL: Trunc Function (with numbers)
ID ACCOUNTID ORGAID
---------- ---------- ----------
001 AC001 DW001
001 AC002 DW001
002 AC003 DW001
002 AC004 DW001
003 AC005 DW001
004 AC006 DW002
005 AC007 DW002
005 AC008 DW002
006 AC009 DW002
9 rows selected
SQL>
SQL> WITH tab AS
2 (SELECT t.orgaid, t.id, COUNT(*) cnt FROM account t GROUP BY t.orgaid, t.id)
3 SELECT c.orgaid, d.opentotal, c.total, trunc(d.opentotal / c.total, 2) ratio
4 FROM (SELECT a.orgaid, COUNT(*) total FROM tab a GROUP BY a.orgaid) c,
5 (SELECT a.orgaid, COUNT(*) opentotal
6 FROM tab a
7 WHERE a.cnt > 1
8 GROUP BY a.orgaid) d
9 WHERE c.orgaid = d.orgaid;
ORGAID OPENTOTAL TOTAL RATIO
---------- ---------- ---------- ----------
DW001 2 3 0.66
DW002 1 3 0.33
SQL>