应用场景: 取占比超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;
