Environmental preparation

Database version: MySQL 5.7.20-log

Create table SQL

DROP TABLE IF EXISTS `t_ware_sale_statistics`;
CREATE TABLE `t_ware_sale_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `business_id` bigint(20) NOT NULL COMMENT 'Business organization code',
  `ware_inside_code` bigint(20) NOT NULL COMMENT 'Commodity self coding',
  `weight_sale_cnt_day` double(16,4) DEFAULT NULL COMMENT 'Average daily sales',
  `last_thirty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales volume in the last 30 days',
  `last_sixty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales volume in the last 60 days',
  `last_ninety_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales volume in the last 90 days',
  `same_period_sale_qty_thirty` double(16,4) DEFAULT NULL COMMENT '30 day sales in the same period last year',
  `same_period_sale_qty_sixty` double(16,4) DEFAULT NULL COMMENT '60 day sales in the same period last year',
  `same_period_sale_qty_ninety` double(16,4) DEFAULT NULL COMMENT '90 day sales in the same period last year',
  `create_user` bigint(20) DEFAULT NULL COMMENT 'Founder',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `modify_user` bigint(20) DEFAULT NULL COMMENT 'Final modifier',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Final modification time',
  `is_delete` tinyint(2) DEFAULT '2' COMMENT 'Delete, 1: Yes, 2: no',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_business_ware` (`business_id`,`ware_inside_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Commodity sales statistics';

Initialization data

769063 pieces of data were prepared

Demand background

When selling goods under a business organization, the same business organization can sell different goods, and the same goods can be sold in different business organizations, that is to say, the relationship between business organization and goods is many to many

Suppose there are n organizations and there are several commodities under each organization. How can we query the sales of their commodities under these stores?

The specific points are similar as follows

How to find out the sales of goods 1000, 1001, 1003, 1002, 1003, 1004 and 1006, 1008 and 1009 under 100001 and 100003

It is equivalent to the query of double-layer list (set of commodity list in business organization list); Business organization list and commodity list are not fixed, but dynamic

Then the question is: how to query the sales of some goods under multiple business organizations

As soon as I describe the problem, it may be more vague. Just understand the meaning!

Circular query

It is easy to think of this. At the code level, the list of business institutions is circulated. Each business institution checks the database once. The pseudo code is as follows:

The specific SQL is similar to the following

SQL can walk index

The implementation is simple and easy to understand. SQL can also go through the index. Everything seems to be perfect

However, the reality is: the department development specification constraints, can not cycle to check the database

Oh, I can only give up this way and find another way

OR splicing

Through the dynamic SQL function of MyBatis, SQL splicing is carried out, similar to the following

The specific SQL is similar to the following

SQL can also be indexed

The implementation is simple and easy to understand. SQL can also index and query the database only once. It seems feasible

The only pity is that it costs a little OR. If there are more business institutions, the SQL will be longer

As one of the candidates, let's move on

Mixed query filtering

It also uses Mybatis's dynamic SQL to create a business_id ， list together, ware_inside_code together, similar to the following

The specific SQL is similar to the following

SQL can also be indexed

The implementation is simple and easy to understand. SQL can also index and query the database only once. It seems feasible

But: the result set found is greater than or equal to the result set we want. You are good, you are good!

Therefore, we also need to filter the found result set to filter out the result set we want

As one of the candidates, let's move on

Line to line comparison

The function of row to row comparison is added IN SQL-92. IN this way, the parameters of the comparison predicates =, <, > and IN predicates are not only scalar values, but also value lists

Of course, you still need to use Mybatis's dynamic SQL, similar to the following

The specific SQL is similar to the following

SQL can also be indexed

The implementation is simple. SQL can also go through the index, and only query the database once. It feels feasible

It's just: it's a little hard to understand, because we usually use so little, so this way of writing looks strange

In addition, row to row comparison is the SQL specification, not the specification of a relational database. That is to say, all relational databases should support this writing method

summary

1. Finally, the method of line to line comparison is selected to realize the requirements

Don't ask me why, asking is forcing Ge Gao!

2. There are often many ways to realize a requirement. We need to consider the business and various constraints and choose the most appropriate one

3. Row to row comparison was introduced into SQL-92, which was formulated in 1992

Line to line comparison is not a new feature, but a basic function that has existed for a long time!

reference resources

• Advanced SQL tutorial
• Magic SQL MySQL execution plan → EXPLAIN, let's understand the execution process of SQL!
• Magic SQL performance optimization → let SQL fly
• The magic SQL passed by → did you really use the index