取占比累计超过80%的几条记录

应用场景: 取占比超80%的几个重要企业信息.

解决思路: 1 取占比,2 降序排列, 3 占比 累加总和, 4 占比超过80%的部分.

USE ods_test;
DROP TABLE ods_test.comp_info;
CREATE TABLE ods_test.`comp_info` (
  `compname` VARCHAR(50) DEFAULT NULL,
  `custname` VARCHAR(50) DEFAULT NULL,
  `countnum` INT DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT  INTO ods_test.`comp_info`(`compname`,`custname`,`countnum`) VALUES ('a','a1','100.00'),('a','a2','500.00'),('a','a3','500.00'),('a','a4','800.00'),('a','a5','100.00'),('a','a6','300.00')
,('b','b1','500.00'),('b','b2','600.00'),('b','b3','700.00'),('b','b4','800.00'),('b','b5','400.00'),('b','b6','300.00'),('b','b7','400.00');

DROP TABLE ods_test.comp_info_test;
CREATE TABLE ods_test.`comp_info_test`
SELECT a.compname,a.custname,a.countnum, a.countnum/b.sum_countnum pecent
FROM ods_test.comp_info a
LEFT JOIN
(SELECT compname,SUM(countnum) sum_countnum FROM ods_test.comp_info GROUP BY compname) b
ON a.compname=b.compname;

SET @row := 0;
SET @curComp := '';
SET @curpecent := 0;
SET @addpecent := 0;
SET @cmpcomp := '';
SET @cmp80 := 1.000;
SET @cmpcust := '';

SELECT t2.compname,GROUP_CONCAT(t2.final_custname) FROM
(
    SELECT t.compname,t.custname,FORMAT(t.pecent,3)
            ,FORMAT(t.addpecent,3)
            ,CASE WHEN custname IS NULL THEN NULL
            WHEN @cmpcomp = compname AND @cmp80 < 0.8 THEN custname
            WHEN @cmpcomp <> compname AND @cmp80 = 1.000 THEN custname
            END final_custname
            ,@cmpcomp :=compname
            ,@cmpcust := custname
            ,@cmp80 := addpecent
    FROM (
            SELECT compname,custname,pecent
                    ,CASE WHEN pecent IS NULL THEN NULL
                    WHEN @curComp = compname AND @curpecent > pecent THEN @row := @row + 1
                    WHEN @curComp = compname  AND @curpecent = pecent THEN @row := @row
                    WHEN @curComp <> compname THEN @row := 1
                    END rank
                    ,CASE WHEN pecent IS NULL THEN NULL
                    WHEN @curComp = compname  THEN @addpecent := @addpecent + pecent
                    WHEN @curComp <> compname THEN @addpecent := pecent
                    END addpecent
                    ,@curComp := compname
                    ,@curpecent := pecent
            FROM ods_test.comp_info_test
            ORDER BY compname,pecent DESC
    ) t
) t2 WHERE t2.final_custname IS NOT NULL
GROUP BY t2.compname;