MySQL: window function

 

 

fuze表:

 

 

  

 mysqldump --add-drop-database --add-drop-table --add-drop-trigger --add-locks --compress --create-options --comments --compact --complete-insert --compress --source-data=2 --single-transaction ssm fuze);

-- CHANGE MASTER TO MASTER_LOG_FILE='cruces-bin.000046', MASTER_LOG_POS=3559;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fuze` (
  `year` int DEFAULT NULL,
  `country` varchar(55) DEFAULT NULL,
  `product` varchar(55) DEFAULT NULL,
  `profit` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `fuze` (`year`, `country`, `product`, `profit`) VALUES (2001,'USA','Calculator',50),(2000,'Finland','Phone',100),(2000,'India','Calculator',75),(2000,'USA','Calculator',75),(2000,'India','Calculator',75),(2000,'USA','Computer',1500),(2001,'USA','Computer',1500),(2000,'India','Computer',1200),(2001,'USA','Computer',1200),(2001,'Finland','Phone',10),(2001,'USA','TV',150),(2001,'USA','TV',100),(2000,'Finland','Computer',1500);

 

 

 

 

 

  

 

 

 

 

 

 

 

 

 

create table juju (t tinyint);
 insert into juju values (1), (1), (2), (3), (3), (4), (4), (5), (6), (7);

 

CUME_DIST():

 

 

 

 

 

 

 

 

 

 

rank() dense_rank():     dense_rank() 最终值 <= rank()

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE TABLE `bun` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product` varchar(55) DEFAULT NULL,
  `sales` int DEFAULT NULL,
  `month` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into bun values
(1, '猪肉大葱包子', 600, '2021-11-15'),
(2, '猪肉大葱包子', 1600, '2021-10-15'),
(3, '猪肉大葱包子', 1000, '2021-09-15'),
(4, '猪肉大葱包子', 800, '2021-08-15'),
(5, '猪肉大葱包子', 1600, '2021-07-15'),
(6, '猪肉大葱包子', 1000, '2021-06-15'),
(7, '面馅儿包子', 700, '2021-11-15'),
(8, '面馅儿包子', 200, '2021-10-15'),
(9, '面馅儿包子', 300, '2021-09-15'),
(10, '面馅儿包子', 0, '2021-08-15'),
(11, '面馅儿包子', 100, '2021-07-15'),
(12, '面馅儿包子', 200, '2021-06-15');