MySQL percentage display and top percentage display methods

Posted by dmcentire on Mon, 03 Jan 2022 12:57:47 +0100

A friend asked me to write it a few days ago. I recorded it casually. I felt it was not difficult. I just encountered some problems when writing. The optimization is not very convenient. There are good optimization methods, welcome to share! (database at the end of the article)

requirement

1) Query the proportion of sales amount of all products in all times, sort them in descending order according to the proportion, and filter the products with the cumulative proportion in the top 80%. The result outputs the cumulative proportion of sales amount of ranked product names.

2) Query the sales situation of each country in all times. If the total sales amount is greater than 10000, it is considered as qualified,
Otherwise, it is unqualified, and the result will be output to the national sales amount and performance.

3) Query the sales situation in China and the UK every month. If the total sales amount in August 2020 is greater than 10000, it will be regarded as qualified, otherwise it will be unqualified. If the total sales amount in September 2020 is greater than 12000, it will be regarded as qualified, otherwise it will be unqualified. The results will output the sales performance in China and the UK in the month.

Implementation code

SELECT a.productID product ID,(a.sale_amount * b.price) sales amount,CONCAT((a.sale_amount * b.price / (select SUM(aa.sale_amount * bb.price) m from 2002a aa left join 2002b bb on aa.productID = bb.productID)) * 100,"%") percent 
FROM (select @rownum:=0) r,2002a a,2002b b 
WHERE (@rownum:=@rownum+1)<=(select round(count(distinct a.productid)*0.8) from 2002a a, 2002b b where a.productID = b.productID) 
AND a.productID = b.productID GROUP BY a.productID ORDER BY (a.sale_amount * b.price) DESC;

SELECT country country,SUM(price*sale_amount) sales amount,if(SUM(price*sale_amount)>10000,'qualified','unqualified') Performance 
FROM 2002a a,2002b b,2002c c WHERE a.productID=b.productID AND a.customID=c.customID GROUP BY country;

SELECT date_format(zTime,'%Y-%m') month,SUM(price*sale_amount) sales amount,
if((date_format(zTime,'%Y-%m')='2020-08' AND SUM(price*sale_amount)>10000) OR (date_format(zTime,'%Y-%m')='2020-09' AND SUM(price*sale_amount)>13000) AND country='China','qualified','unqualified') China sales performance,
if((date_format(zTime,'%Y-%m')='2020-08' OR SUM(price*sale_amount)>10000) AND (date_format(zTime,'%Y-%m')='2020-09' AND SUM(price*sale_amount)>13000) AND country='britain','qualified','unqualified') UK sales performance 
FROM 2002a a,2002b b,2002c c 
WHERE a.productID=b.productID AND a.customID=c.customID AND country IN('China','britain') AND (date_format(zTime,'%Y-%m')='2020-09' OR date_format(zTime,'%Y-%m')='2020-08') GROUP BY date_format(zTime,'%Y-%m');

① Methods to achieve the top 80% of query result display in:

Percent achieved display:

First, know the two functions concat(), left(), TRUNCATE(A,B)

CONCAT(str1,str2,...) Concatenate strings and return strings from parameter links. Returns NULL if any parameter is null. Multiple can be spliced.

LEFT(str,length) intercepts the string from the left Description: left (intercepted field, intercepted length)

TRUNCATE(A,B) returns the number a rounded to B digits after the decimal point. If the value of B is 0, the result has no decimal point or part. You can set B as a negative number. If you want to truncate (return to zero) all the low-order values from the B position from the left of the decimal point of A, The rounding direction of all numbers is close to zero

Combine it (I didn't use left in the above code): concat (left (value 1 / value 2 *100,5),'%') as complaint rate

Example:

SELECT id,CONCAT(TRUNCATE(passScore / (danScore+panScore+duoScore) *100,2),'%') as Score to total score ratio 
FROM aqsc_kaoshi_record;

How many percent of the data before mysql query (80% here)

mysql does not support top and rowid, and using limit does not work. So use the following method:

SELECT a.* 
FROM (SELECT @rownum:=0) r,2002a a 
WHERE (@rownum:=@rownum+1)<=(select round(count(*)*0.1) from 2002a);

rownum here is just a variable name, or it can be used in other ways

The top 20% cases after sorting the grade of the student table from large to small:

SELECT @rownum:=@rownum+1,student.* 
FROM (select @rownum:=0) row ,(select * from student order by student.grade desc) student ##sort
WHERE @rownum<(select round(count(*)/4) from student)

In addition to if, examples of judgment display are as follows:

select 
       sum(case when sex = 'male' then 1 else 0 end)   /* This is the number of boys */
       sum(case when sex = 'female' then 1 else 0 end)   /* This is the number of girls */
from student

database

The following is the complete database code:

/*
Navicat MySQL Data Transfer

Source Server         : First
Source Server Version : 80011
Source Host           : localhost:3306
Source Database       : fr_test_sql

Target Server Type    : MYSQL
Target Server Version : 80011
File Encoding         : 65001

Date: 2021-12-18 16:06:19
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `2002a`
-- ----------------------------
DROP TABLE IF EXISTS `2002a`;
CREATE TABLE `2002a` (
  `orderID` varchar(255) NOT NULL,
  `zTime` date NOT NULL,
  `productID` varchar(255) NOT NULL,
  `sale_amount` int(11) NOT NULL,
  `customID` varchar(255) NOT NULL,
  PRIMARY KEY (`orderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 2002a
-- ----------------------------
INSERT INTO `2002a` VALUES ('O001', '2020-09-10', 'P010', '96', 'C008');
INSERT INTO `2002a` VALUES ('O002', '2020-08-29', 'P008', '38', 'C007');
INSERT INTO `2002a` VALUES ('O003', '2020-08-10', 'P007', '97', 'C008');
INSERT INTO `2002a` VALUES ('O004', '2020-09-27', 'P005', '62', 'C006');
INSERT INTO `2002a` VALUES ('O005', '2020-08-17', 'P007', '37', 'C009');
INSERT INTO `2002a` VALUES ('O006', '2020-09-06', 'P006', '3', 'C005');
INSERT INTO `2002a` VALUES ('O007', '2020-08-30', 'P009', '86', 'C007');
INSERT INTO `2002a` VALUES ('O008', '2020-09-04', 'P001', '34', 'C007');
INSERT INTO `2002a` VALUES ('O009', '2020-09-09', 'P003', '99', 'C004');
INSERT INTO `2002a` VALUES ('O010', '2020-09-06', 'P002', '65', 'C010');
INSERT INTO `2002a` VALUES ('O011', '2020-08-08', 'P005', '11', 'C002');
INSERT INTO `2002a` VALUES ('O012', '2020-09-20', 'P002', '3', 'C008');
INSERT INTO `2002a` VALUES ('O013', '2020-08-15', 'P004', '9', 'C004');
INSERT INTO `2002a` VALUES ('O014', '2020-08-28', 'P007', '99', 'C010');
INSERT INTO `2002a` VALUES ('O015', '2020-08-23', 'P003', '3', 'C005');
INSERT INTO `2002a` VALUES ('O016', '2020-08-08', 'P006', '51', 'C008');
INSERT INTO `2002a` VALUES ('O017', '2020-09-04', 'P009', '99', 'C002');
INSERT INTO `2002a` VALUES ('O018', '2020-08-12', 'P007', '86', 'C003');
INSERT INTO `2002a` VALUES ('O019', '2020-09-22', 'P001', '73', 'C005');
INSERT INTO `2002a` VALUES ('O020', '2020-08-03', 'P009', '22', 'C006');
INSERT INTO `2002a` VALUES ('O021', '2020-08-22', 'P007', '54', 'C006');
INSERT INTO `2002a` VALUES ('O022', '2020-09-29', 'P005', '59', 'C005');
INSERT INTO `2002a` VALUES ('O023', '2020-08-15', 'P003', '45', 'C006');
INSERT INTO `2002a` VALUES ('O024', '2020-09-12', 'P001', '10', 'C004');
INSERT INTO `2002a` VALUES ('O025', '2020-08-23', 'P004', '56', 'C008');
INSERT INTO `2002a` VALUES ('O026', '2020-09-17', 'P003', '57', 'C004');
INSERT INTO `2002a` VALUES ('O027', '2020-08-23', 'P002', '73', 'C003');
INSERT INTO `2002a` VALUES ('O028', '2020-09-22', 'P003', '50', 'C008');
INSERT INTO `2002a` VALUES ('O029', '2020-09-22', 'P003', '70', 'C007');
INSERT INTO `2002a` VALUES ('O030', '2020-08-13', 'P006', '15', 'C002');

-- ----------------------------
-- Table structure for `2002b`
-- ----------------------------
DROP TABLE IF EXISTS `2002b`;
CREATE TABLE `2002b` (
  `productID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `productName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` decimal(10,0) NOT NULL,
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 2002b
-- ----------------------------
INSERT INTO `2002b` VALUES ('P001', 'product A', '29');
INSERT INTO `2002b` VALUES ('P002', 'product B', '50');
INSERT INTO `2002b` VALUES ('P003', 'product C', '42');
INSERT INTO `2002b` VALUES ('P004', 'product D', '59');
INSERT INTO `2002b` VALUES ('P005', 'product E', '49');
INSERT INTO `2002b` VALUES ('P006', 'product F', '10');
INSERT INTO `2002b` VALUES ('P007', 'product G', '23');
INSERT INTO `2002b` VALUES ('P008', 'product H', '24');
INSERT INTO `2002b` VALUES ('P009', 'product I', '50');
INSERT INTO `2002b` VALUES ('P010', 'product J', '64');

-- ----------------------------
-- Table structure for `2002c`
-- ----------------------------
DROP TABLE IF EXISTS `2002c`;
CREATE TABLE `2002c` (
  `customID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `customName` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`customID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 2002c
-- ----------------------------
INSERT INTO `2002c` VALUES ('C001', 'customer A', 'China');
INSERT INTO `2002c` VALUES ('C002', 'customer B', 'France');
INSERT INTO `2002c` VALUES ('C003', 'customer C', 'China');
INSERT INTO `2002c` VALUES ('C004', 'customer D', 'britain');
INSERT INTO `2002c` VALUES ('C005', 'customer E', 'U.S.A');
INSERT INTO `2002c` VALUES ('C006', 'customer F', 'China');
INSERT INTO `2002c` VALUES ('C007', 'customer G', 'France');
INSERT INTO `2002c` VALUES ('C008', 'customer H', 'britain');
INSERT INTO `2002c` VALUES ('C009', 'customer I', 'U.S.A');
INSERT INTO `2002c` VALUES ('C010', 'customer H', 'britain');

-- ----------------------------
-- Table structure for `2003_a`
-- ----------------------------
DROP TABLE IF EXISTS `2003_a`;
CREATE TABLE `2003_a` (
  `CLASSNO` varchar(255) DEFAULT NULL,
  `STUDENTNO` varchar(255) DEFAULT NULL,
  `GRADE` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 2003_a
-- ----------------------------
INSERT INTO `2003_a` VALUES ('CLASS1', '1001', '86');
INSERT INTO `2003_a` VALUES ('CLASS1', '1002', '60');
INSERT INTO `2003_a` VALUES ('CLASS1', '1003', '85');
INSERT INTO `2003_a` VALUES ('CLASS1', '1004', '73');
INSERT INTO `2003_a` VALUES ('CLASS1', '1005', '95');
INSERT INTO `2003_a` VALUES ('CLASS1', '1006', '61');
INSERT INTO `2003_a` VALUES ('CLASS1', '1007', '77');
INSERT INTO `2003_a` VALUES ('CLASS1', '1008', '71');
INSERT INTO `2003_a` VALUES ('CLASS1', '1009', '61');
INSERT INTO `2003_a` VALUES ('CLASS1', '1010', '78');
INSERT INTO `2003_a` VALUES ('CLASS2', '2001', '81');
INSERT INTO `2003_a` VALUES ('CLASS2', '2002', '54');
INSERT INTO `2003_a` VALUES ('CLASS2', '2003', '57');
INSERT INTO `2003_a` VALUES ('CLASS2', '2004', '75');
INSERT INTO `2003_a` VALUES ('CLASS2', '2005', '98');
INSERT INTO `2003_a` VALUES ('CLASS2', '2006', '75');
INSERT INTO `2003_a` VALUES ('CLASS2', '2007', '76');
INSERT INTO `2003_a` VALUES ('CLASS2', '2008', '58');
INSERT INTO `2003_a` VALUES ('CLASS2', '2009', '73');
INSERT INTO `2003_a` VALUES ('CLASS2', '2010', '55');
INSERT INTO `2003_a` VALUES ('CLASS3', '3001', '42');
INSERT INTO `2003_a` VALUES ('CLASS3', '3002', '90');
INSERT INTO `2003_a` VALUES ('CLASS3', '3003', '81');
INSERT INTO `2003_a` VALUES ('CLASS3', '3004', '97');
INSERT INTO `2003_a` VALUES ('CLASS3', '3005', '68');
INSERT INTO `2003_a` VALUES ('CLASS3', '3006', '72');
INSERT INTO `2003_a` VALUES ('CLASS3', '3007', '81');
INSERT INTO `2003_a` VALUES ('CLASS3', '3008', '79');
INSERT INTO `2003_a` VALUES ('CLASS3', '3009', '87');
INSERT INTO `2003_a` VALUES ('CLASS3', '3010', '59');

-- ----------------------------
-- Table structure for `2004_a`
-- ----------------------------
DROP TABLE IF EXISTS `2004_a`;
CREATE TABLE `2004_a` (
  `TYEAR` varchar(255) DEFAULT NULL,
  `TMONTH` varchar(255) DEFAULT NULL,
  `SALE_MONEY` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 2004_a
-- ----------------------------
INSERT INTO `2004_a` VALUES ('2019', '10', '1279');
INSERT INTO `2004_a` VALUES ('2019', '11', '2316');
INSERT INTO `2004_a` VALUES ('2019', '12', '2090');
INSERT INTO `2004_a` VALUES ('2020', '01', '1086');
INSERT INTO `2004_a` VALUES ('2020', '02', '2046');
INSERT INTO `2004_a` VALUES ('2020', '03', '0');
INSERT INTO `2004_a` VALUES ('2020', '04', '2959');
INSERT INTO `2004_a` VALUES ('2020', '05', '1314');
INSERT INTO `2004_a` VALUES ('2020', '06', '2751');
INSERT INTO `2004_a` VALUES ('2020', '07', '1492');
INSERT INTO `2004_a` VALUES ('2020', '08', '1414');
INSERT INTO `2004_a` VALUES ('2020', '09', '2895');
INSERT INTO `2004_a` VALUES ('2020', '10', '2999');
INSERT INTO `2004_a` VALUES ('2020', '11', '1982');
INSERT INTO `2004_a` VALUES ('2020', '12', '2793');
INSERT INTO `2004_a` VALUES ('2021', '01', '2156');
INSERT INTO `2004_a` VALUES ('2021', '02', '1733');
INSERT INTO `2004_a` VALUES ('2021', '03', '2184');

-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'number',
  `user_access` varchar(20) NOT NULL DEFAULT '' COMMENT 'account number',
  `user_token` varchar(20) NOT NULL DEFAULT '123456' COMMENT 'password',
  `user_nick` varchar(20) NOT NULL DEFAULT 'small shrimp' COMMENT 'nickname',
  `user_gender` bit(1) NOT NULL DEFAULT b'1' COMMENT '1 Male, 0 female',
  `user_hobbies` varchar(20) NOT NULL COMMENT 'hobby',
  `user_type` int(1) NOT NULL DEFAULT '1' COMMENT 'type',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_user_access` (`user_access`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', 'cqswxy', '111111', 'Chongqing Business', '', 'programming,game', '3');
INSERT INTO `t_user` VALUES ('2', 'zjczjc', '222222', 'Star Chi ', '', 'programming,study', '2');
INSERT INTO `t_user` VALUES ('3', 'cetoox', '333333', 'The speed of light is zero', '', 'game,study', '1');
INSERT INTO `t_user` VALUES ('4', 'XXX', '23', 'XXX', '', 'XXXX', '1');
INSERT INTO `t_user` VALUES ('6', 'dasda', '123456', 'small shrimp', '', 'asd', '5');

-- ----------------------------
-- Table structure for `t_user_type`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_type`;
CREATE TABLE `t_user_type` (
  `user_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_type_name` varchar(2) NOT NULL,
  PRIMARY KEY (`user_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user_type
-- ----------------------------
INSERT INTO `t_user_type` VALUES ('1', 'green hand');
INSERT INTO `t_user_type` VALUES ('2', 'killer');
INSERT INTO `t_user_type` VALUES ('3', 'legend');
INSERT INTO `t_user_type` VALUES ('4', 'ordinary');

reference resources:

mysql queries the first few percent of the data (take 25% as an example) - CSDN [i'm a brother i]

Topics: MySQL

Programmer Think

MySQL percentage display and top percentage display methods

requirement

Implementation code

database

Hot Topics