The blog is posted at www.xbcblog.com/

The main structure of the table is as follows. The requirement is to take the maximum three view_or_engagement items for each platform of a brand within the time range

CREATE TABLE `word_cloud` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `brand` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'brand',
  `platform` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'platform',
  `view_or_engagement` int(11) DEFAULT NULL COMMENT 'Read or subscribe',
  `article` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'articles',
  `title` text COLLATE utf8mb4_unicode_ci COMMENT 'title',
  `url` text COLLATE utf8mb4_unicode_ci COMMENT 'link',
  `time` date DEFAULT NULL COMMENT 'time',
  `data_type` tinyint(1) DEFAULT NULL COMMENT 'type',
  `md5_flag` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'md5 marks',
  PRIMARY KEY (`id`),
  KEY `K` (`data_type`) USING BTREE,
  KEY `k_md5` (`md5_flag`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the code

SQL before optimization

SELECT brand,platform,view_or_engagement,title,url  FROM (
    SELECT * FROM word_cloud WHERE brand = 'Siemens' and time >= '2020-04-01' and time<'2020-04-20'
) a WHERE (
    SELECT count(*) FROM (
    SELECT * FROM word_cloud WHERE  brand = 'Siemens' and time >= '2020-04-01' and time<'2020-04-20'
    ) b WHERE b.view_or_engagement > a.view_or_engagement and a.data_type = b.data_type
    and a.platform = b.platform and a.brand = b.brand
) < 3 
ORDER BY view_or_engagement desc;Copy the code

Optimized SQL

SELECT brand,platform,view_or_engagement,title,url 
FROM( 
  SELECT brand,platform,view_or_engagement,title,url, 
   @order_rank := IF(@curr_ = platform,@order_rank + 1, 1) AS order_rank, 
   @curr_ := platform 
   FROM word_cloud 
   WHERE  brand = 'Siemens'
   and time >= '2020-04-01' and time < '2020-04-20' 
   ORDER BY platform,view_or_engagement desc  
) b WHERE order_rank <= 3Copy the code

The main use of SQL variables, refer to the article below, click to see

Towardsdatascience.com/mysql-how-t…