谁来帮忙解决一下这个统计问题~

[s_wyp]

130万数据
select
gridid,
count(gridid)c1,
sum(case when rknum>10 then 1 when roomuser=158 and rknum>0 then 1 when (roomuser in(156,157) and rknum=0 ) then 1 else 0 end)c2
from(
select roomid,gridid,roomuser,
(select count(1)from r_residents where roomid=s.roomid and islogout=1 and isvalid=1 and not exists(select roomid from U_RKFW_HS where roomid=s.roomid and HSSTYLE='HRKJC'))rknum
from s_rooms s where s.isvalid=1 and s.gridid like p_gridid||'%')group by gridid;
----------------------------------------------------------------------------------------------
select t.gridid,
count(hrk) c1,
sum(case when hrk>5 then 1 else 0 end)c2 from s_rooms t right join
(
SELECT a.roomid,a.hrk
FROM ( select s.roomid,count(s.fhost) over(partition by s.fhost) as hrk from r_residents s
where s.isvalid=1 and s.islogout=1 and not exists(select roomid from u_rkfw_hs where roomid=s.roomid and HSSTYLE='RHRKGD')) a,
(SELECT MIN(b.ROWID) row_id
FROM ( select s.roomid,count(s.fhost) over(partition by s.fhost) as hrk from r_residents s
where s.isvalid=1 and s.islogout=1and not exists(select roomid from u_rkfw_hs where roomid=s.roomid and HSSTYLE='RHRKGD')) b
GROUP BY b.roomid) b
WHERE a.ROWID = b.ROW_id )s on s.roomid= t.roomid
where isvalid=1 and gridid like p_gridid||'%'


--求高手帮忙优化~