Analysis of `SQL` writing method of Wutong database, `mysql` and `oracle` on exchange server number (recommended)

1. Background description

Mobile operators have a large number of server devices in their data centers, and their performance may be affected by adjacent servers, and the layout of servers within their data centers needs to be optimized. In order to find the optimal layout plan, it is necessary to evaluate the impact of different layouts on the overall system performance. A simple test method is to simulate the location of the switch server, i.e., to exchange each pair of consecutive server numbers to see if such changes can lead to performance improvements.

2. Problem description

To achieve this, write aSQLQuery to help complete the server number exchange operation, pressid Ascending orderReturn to the result table. Specifically:

There is a table in the database that records the numbers of all servers.
Server numbers are arranged in a certain order, for example incrementing from 1.
Each pair of consecutive server numbers need to be exchanged. For example, if the server numbers are 1, 2, 3, 4, 5 in turn, then after exchange it becomes 2, 1, 4, 3, 5.
If the total number of servers is odd, the last server number remains the same.

This time, three different databases are used for analysis and use cases, namely Wutong Database.mysql ，oracle 。

3. Table structure description

Wutong database table construction statement

Simple server distribution table main fields.

create table servers (
    server_id int primary key,
    position int
);

mysqlTable creation statement

Simple server distribution table main fields.

create table servers (
    server_id int primary key,
    position int
);

3.oracleTable creation statement

Simple server distribution table main fields.

create table servers (
    server_id number primary key,
    position number
);

4. Table data insertion

Wutong database,mysql、oracleofinsertThe insertion statement is basically the same, and only the following is written in the Wutong databaseinsertStatements to unify the representation.

insert into servers values (1,1);
insert into servers values (2,2);
insert into servers values (3,3);
insert into servers values (4,4);
insert into servers values (5,5);
insert into servers values (6,6);
insert into servers values (7,7);

5. Decomposition of SQL implementation ideas

1. Determine the conditions that need to be exchanged, that is, we need to determine which server numbers need to be exchanged, which can be achieved by judging whether server_id is an odd or even number.

2. Define a new location. For even server_id, the new location will be the location of the server number before it; for odd server_id (except the last), the new location will be the location of the server number behind it.

3. To deal with boundary situations, if server_id is the largest (that is, the total number is an odd number), then the location of the server number does not need to be changed.

6. SQL implementation

Wutong Database andmysqlThe implementation syntax is basically consistent, and the writing method of Wutong database is demonstrated as an example:

select 
    case when server_id % 2 = 0 
         then server_id - 1
         when server_id = (select count(distinct server_id) from servers) and server_id % 2 = 1 
         then server_id
         else server_id + 1
     end as server_id,
    position
from servers
order by server_id;

oracleImplementationSQLas follows:

select 
	server_id,
	decode(mod(server_id,2), 1, lead(position, 1, position) over(order by server_id), lag(position, 1) over(order by server_id)) as position
from servers

7. Explain the functions of each part of SQL

Wutong Database andMySQLImplementing CASE statements:

server_id % 2 = 0: when server_id When it is an even number，Subtract it 1。
server_id = (select count(distinct server_id) from servers) and server_id % 2 = 1: when server_id When it is an odd number and is the maximum value，Stay unchanged。
otherwise，Add it 1。

ORDER BY clause:

order by server_id: according to server_id Sort。

Oracle ImplementationDECODE statement:

mod(server_id, 2): calculate server_id Divide by 2 The remainder of，Used to judge server_id Is it an odd number
lead(position, 1, position) over (order by server_id): When the remainder is odd，Get the line behind the current line position value，If it does not exist，Returns the current line position value。
lag(position, 1) over (order by server_id): When the remainder is even，Get the line in front of the current line position value，If it does not exist，Returns the current line position value。

OVER clause:

over (order by server_id): according to server_id Sort。

This is the article about Wutong database, mysql` and `oracle` about SQL writing analysis of exchange server numbers. For more information about Wutong database exchange server numbers, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!