The FROM (
The SELECT temp. Init_shop_name AS front_name, temp. Relate_shop_name AS back_name
, and COUNT (*)/vipNum. Total * 100 AS support
, and COUNT (*)/temp2. Num * 100 AS confidence
The FROM (
SELECT SSDR vip_code, SSDR. Initial_shop_code AS init_shop_code, shop. The name AS init_shop_name, comm. Code AS init_comm_code, comm. Name AS init_comm_name
, cate. Code AS init_comm_sub_code, cate. Name AS init_comm_sub_name, shop2. Code AS relate_shop_code, shop2. Name AS relate_shop_name, comm2. Code AS relate_comm_code
, comm2. Name AS relate_comm_name, cate2. Code AS relate_comm_sub_code, cate2. Name AS relate_comm_sub_name
The FROM sum_sale_daily_relation SSDR
INNER JOIN r_shop_category shop_cate ON shop_cate. Shop_code=SSDR. Initial_shop_code
INNER JOIN dict_category cate ON cate. Code=shop_cate. Category_code
INNER JOIN dict_commercial comm ON comm. Code=cate.com mercial_code
INNER JOIN origin_rent_shop shop ON shop. Code=SSDR. Initial_shop_code
INNER JOIN r_shop_category shop_cate2 ON shop_cate2. Shop_code=SSDR. Relation_shop_code
INNER JOIN dict_category cate2 ON cate2. Code=shop_cate2. Category_code
INNER JOIN dict_commercial comm2 ON comm2. Code=cate2.com mercial_code
INNER JOIN origin_rent_shop shop2 ON shop2. Code=SSDR. Relation_shop_code
WHERE's the 2017-09-19 00:00:00. 0 '& lt;=SSDR. Date
AND SSDR. Date & lt; '2017-12-19 00:00:00. 0'
GROUP BY SSDR vip_code, SSDR initial_shop_code, SSDR. Relation_shop_code
Temp)
INNER JOIN (
SELECT COUNT (DISTINCT vip_code) AS the total
The FROM sum_vip_consume_daily
WHERE's the 2017-09-19 00:00:00. 0 '& lt;=the date
AND the date & lt; '2017-12-19 00:00:00. 0'
) vipNum
INNER JOIN (
The SELECT shop_cate shop_code AS shopCode, COUNT (*) AS num
The FROM sum_sale_daily_relation SSDR
INNER JOIN r_shop_category shop_cate ON shop_cate. Shop_code=SSDR. Initial_shop_code
INNER JOIN dict_category cate ON cate. Code=shop_cate. Category_code
WHERE's the 2017-09-19 00:00:00. 0 '& lt;=SSDR. Date
AND SSDR. Date & lt; '2017-12-19 00:00:00. 0'
GROUP BY shop_cate. Shop_code
) temp2
ON temp2. ShopCode=temp. Init_shop_code
GROUP BY temp. Init_shop_code, temp. Relate_shop_code
) temp3
The ORDER BY the confidence DESC
LIMIT 0, 40
This is the original poster write SQL, including sum_sale_daily_relation, sum_vip_consume_daily is big data table, millions of data, and how to optimize the index of how to build,
Now the two tables index
The CREATE TABLE ` sum_sale_daily_relation ` (
` id ` bigint (20) unsigned NOT NULL AUTO_INCREMENT,
` date ` date NOT NULL COMMENT 'on time',
` vip_code ` varchar (32) NOT NULL,
` initial_shop_code ` varchar (32) NOT NULL COMMENT 'initial merchant code,
` relation_shop_code ` varchar (32) NOT NULL COMMENT 'correlation merchant code,
` create_time ` datetime DEFAULT CURRENT_TIMESTAMP,
` last_update ` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Unsigned ` delete_flag ` tinyint (1) the DEFAULT '0',
` delete_time ` datetime DEFAULT NULL,
PRIMARY KEY (` id `),
The KEY ` key_sum_sale_daily_relation_date_vip_code ` (` date `, ` vip_code `),
The KEY ` idx_initialshopcode ` (` initial_shop_code `)
) ENGINE=InnoDB AUTO_INCREMENT=3145681 DEFAULT CHARSET=utf8 COMMENT='sales association, statistics;
The CREATE TABLE ` sum_vip_consume_daily ` (
` id ` bigint (20) unsigned NOT NULL AUTO_INCREMENT,
` vip_code ` varchar (32) NOT NULL COMMENT 'membership number,
` date ` date NOT NULL COMMENT 'on time',
` num ` int (11) unsigned DEFAULT NULL COMMENT 'consumption number,
` amount ` float (14, 4) NOT NULL COMMENT 'turnover, return negative',
` create_time ` datetime DEFAULT CURRENT_TIMESTAMP,
` last_update ` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Unsigned ` delete_flag ` tinyint (1) the DEFAULT '0',
` delete_time ` datetime DEFAULT NULL,
PRIMARY KEY (` id `),
The KEY ` idx_date ` (` date `)
) ENGINE=InnoDB AUTO_INCREMENT=3999564 DEFAULT CHARSET=utf8 COMMENT='member consumption, statistics;
CodePudding user response:
Suggest one by one in order of your even watch on debugging determine where the slowest