表结构如下:table1
id name pcount
1 电脑 100000
2 机箱 20
3 音箱 0
4 显示器 23298989其中pcount为数字型.
现在要求出table1中pcount累计占比前80%的记录,要如何写,如果能用一条SQL最好。
我想过了,最土的办法就是写存储过程一行记录一行记录累加然后除总total pcount,但这样比较麻烦,求简单点的解决办法。
id name pcount
1 电脑 100000
2 机箱 20
3 音箱 0
4 显示器 23298989其中pcount为数字型.
现在要求出table1中pcount累计占比前80%的记录,要如何写,如果能用一条SQL最好。
我想过了,最土的办法就是写存储过程一行记录一行记录累加然后除总total pcount,但这样比较麻烦,求简单点的解决办法。
SELECT id,NAME,pcount,t2.num,pcount/t2.num AS per FROM table1 AS t1,(SELECT SUM(pcount)AS num FROM tmp2) AS t2 WHERE pcount/t2.num>0.8
select pcount from table,
(select sum(pcount) as total from table) as tb
where cast(pcount as float)/total>0.8
我直接贴上我用函数实现的功能,你们看看能否用一条SQL搞定:
-- Function: ggq.timestamp_to_datestr(bigint)-- DROP FUNCTION ggq.timestamp_to_datestr(bigint);CREATE OR REPLACE FUNCTION counttop80traffic(startdate bigint, enddate bigint)
RETURNS BIGINT AS
$BODY$
--计算流量占比前80%的记录数
DECLARE
result VARCHAR;
totaltraffic BIGINT;
acctraffic BIGINT;
reccount BIGINT;
rec RECORD;
BEGIN
select sum(traffic) into totaltraffic from table where time >= startdate and time <= enddate;
RAISE INFO '总流量:%', totaltraffic;
acctraffic := 0;
reccount := 0;
FOR rec in select amount, traffic from table
where time >= startdate and time <= enddate order by traffic desc LOOP
acctraffic := acctraffic + rec.traffic; reccount := reccount + 1;
IF (acctraffic * 1.0/totaltraffic >= 0.8) THEN
RAISE INFO '目前已经满足条件退出:%', acctraffic;
EXIT;
END IF;
END LOOP;
RAISE INFO '累计量达:%, 记录数:%', acctraffic, reccount; RETURN reccount;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;