2175. The Change in Global Rankings

SQL架构

Table: TeamPoints

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| team_id     | int     |
| name        | varchar |
| points      | int     |
+-------------+---------+
team_id is the primary key for this table.
Each row of this table contains the ID of a national team, the name of the country it represents, and the points it has in the global rankings. No two teams will represent the same country.

Table: PointsChange

+---------------+------+
| Column Name   | Type |
+---------------+------+
| team_id       | int  |
| points_change | int  |
+---------------+------+
team_id is the primary key for this table.
Each row of this table contains the ID of a national team and the change in its points in the global rankings.
points_change can be:
- 0: indicates no change in points.
- positive: indicates an increase in points.
- negative: indicates a decrease in points.
Each team_id that appears in TeamPoints will also appear in this table.

The global ranking of a national team is its rank after sorting all the teams by their points in descending order. If two teams have the same points, we break the tie by sorting them by their name in lexicographical order.

The points of each national team should be updated based on its corresponding points_change value.

Write an SQL query to calculate the change in the global rankings after updating each team's points.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
TeamPoints table:
+---------+-------------+--------+
| team_id | name        | points |
+---------+-------------+--------+
| 3       | Algeria     | 1431   |
| 1       | Senegal     | 2132   |
| 2       | New Zealand | 1402   |
| 4       | Croatia     | 1817   |
+---------+-------------+--------+
PointsChange table:
+---------+---------------+
| team_id | points_change |
+---------+---------------+
| 3       | 399           |
| 2       | 0             |
| 4       | 13            |
| 1       | -22           |
+---------+---------------+
Output: 
+---------+-------------+-----------+
| team_id | name        | rank_diff |
+---------+-------------+-----------+
| 1       | Senegal     | 0         |
| 4       | Croatia     | -1        |
| 3       | Algeria     | 1         |
| 2       | New Zealand | 0         |
+---------+-------------+-----------+
Explanation: 
The global rankings were as follows:
+---------+-------------+--------+------+
| team_id | name        | points | rank |
+---------+-------------+--------+------+
| 1       | Senegal     | 2132   | 1    |
| 4       | Croatia     | 1817   | 2    |
| 3       | Algeria     | 1431   | 3    |
| 2       | New Zealand | 1402   | 4    |
+---------+-------------+--------+------+
After updating the points of each team, the rankings became the following:
+---------+-------------+--------+------+
| team_id | name        | points | rank |
+---------+-------------+--------+------+
| 1       | Senegal     | 2110   | 1    |
| 3       | Algeria     | 1830   | 2    |
| 4       | Croatia     | 1830   | 3    |
| 2       | New Zealand | 1402   | 4    |
+---------+-------------+--------+------+
Since after updating the points Algeria and Croatia have the same points, they are ranked according to their lexicographic order.
Senegal lost 22 points but their rank did not change.
Croatia gained 13 points but their rank decreased by one.
Algeria gained 399 points and their rank increased by one.
New Zealand did not gain or lose points and their rank did not change.

# Write your MySQL query statement below
with t1 as (select
team_id,name,points,row_number() over(order by points desc,name) rn1 #对每个队伍 按 points降序,相等按name 字典顺序标号 找到 每个队伍现在的排名
from
TeamPoints
),t2 as (
select
team_id,name,points + points_change points,rn1 #现在每队的分数
from
t1 left join PointsChange p
using(team_id)
)
select
# rn1 要转型 否者会报错{ BIGINT UNSIGNED value is out of range in '(`t2`.`rn1` - `row_number() OVER (ORDER BY t2.points desc,t2.``name`` ) `)'}
#解决办法  全部转型为 signed   或者 把其中 一个 转型为 decimal
-- team_id,name,cast(rn1 as SIGNED)   - cast((row_number() over(order by points desc,name)) as SIGNED) rank_diff

team_id,name,rn1  - cast((row_number() over(order by points desc,name)) as decimal) rank_diff
from
t2

开两个窗:

# Write your MySQL query statement below
SELECT team_id,name,
       (CAST(rank1 AS signed) - CAST(rank2 AS signed)) AS rank_diff
FROM(
    SELECT t.team_id,name,
           RANK() OVER(ORDER BY points DESC,name) AS rank1,
           RANK() OVER(ORDER BY points + points_change DESC,name) AS rank2
    FROM TeamPoints t 
    LEFT JOIN PointsChange p ON p.team_id = t.team_id
) temp 

笔记:

 

MySQL处理两个整数（INT）相减的时候，如果其中有一个是UNSIGNED INT类型的，那么结果就被当做是UNSIGNED的。

如果相减的结果是负数：
在MySQL 5.5.5之前，结果变成了最大的整数（18446744073709551615）
从MySQL 5.5.5开始，这种情况会返回一个错误：BIGINT UNSIGNED value is out of range...