# 排名函数

以下表中的数据为例

# rank()

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

按照某一字段的排序结果添加跳跃、间断的排名

# row_number()

ROW_NUMBER() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

从1开始分配序号

# dense_rank()

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

按照某一字段排序结果添加连续、不间断的排名

# 对比

SELECT score, rank() over(ORDER BY score DESC) as Rank FROM table1

rank()                row_number()                  dense_rank()
+------+---------+    +-------+------+---------+    +------+---------+
| score|   Rank  |    |row_num| score|   Rank  |    | score|   Rank  |
+------+---------+    +------ +------+---------+    +------+---------+
|  100 |       1 |    |  1    |  100 |       1 |    |  100 |       1 |
|  100 |       1 |    |  2    |  100 |       2 |    |  100 |       1 |
|  95  |       3 |    |  3    |  95  |       3 |    |  95  |       2 |
|  95  |       3 |    |  4    |  95  |       1 |    |  95  |       2 |
|  95  |       3 |    |  5    |  95  |       1 |    |  95  |       2 |
|  90  |       6 |    |  6    |  90  |       1 |    |  90  |       3 |
|  89  |       7 |    |  7    |  89  |       1 |    |  89  |       4 |
+------+---------+    +-------+------+---------+    +------+---------+

# null判断函数

# isnull(expr)

判断expr是否为null：

• 是，返回1
• 否，返回0

# ifnull(expr1, expr2)

判断expr1是否为null：

• 是，返回expr2
• 否，返回expr1

# nullif(expr1, expr2)

判断expr1 = expr2是否成立：

• 是，返回null
• 否，返回expr1

# if(expr, value1, value2)

判断expr是否成立：

• 是，返回value1
• 否，返回value2

类似三元运算符expr ? value1 : value2或Python中的a = value1 if expr else value2

# coalesce(value1, value2, ..., valueN)

返回多个值中第一个非null值

# N/A处理

MySQL中，null表示空值，而N/A表示不存在这样的值

null与任何值作比较(= < >)都返回null，而N/A可以进行=比较

# 嵌套一层查询

原查询返回结果N/A

SELECT name FROM table1 WHERE id = 3

在外层嵌套一个SELECT，可以将结果转换为null

SELECT (
  SELECT name FROM table WHERE id = 3
)

# 用max()或min()等函数转换

如果在null判断函数（如ifnull()）中判断N/A，可以使用max()或min()函数将null转换为N/A

SELECT ifnull(max(name), 'Example') FROM table1

# null筛选

由于null与任何值的对比都返回null，所以在查询条件WHERE中无论使用=还是!=都会将null过滤掉

可以使用以下几个运算符：

• is null：列值为null返回true
• is not null：列值不为null返回true
• <=>：安全等于，既可以判断普通数值相等，也可以判断是否为null

# 重复值处理

+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

# GROUP BY+HAVING

SELECT DISTINCT email
FROM Person
GROUP BY email
HAVING COUNT(1) > 1

思路比较易于理解，按对应字段分组，找出数量大于1的

但效率比较低

# 自连接

SELECT DISTINCT t1.email
FROM Person t1 JOIN Person t2 ON t1.email = t2.email
WHERE t1.id != t2.id

若有重复值，自连接后会出现id不同的行

自连接由于会使用索引，效率比GROUP BY+HAVING高

# 找出连续出现N次的值

+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |    Result:
| 4  | 2   |    +-----------------+
| 5  | 1   |    | ConsecutiveNums |
| 6  | 2   |    +-----------------+
| 7  | 2   |    | 1               |
+----+-----+    +-----------------+

首先id有可能是不连续的，先通过row_number()重新分配连续id值(serial)

SELECT id, num, row_number() over(ORDER BY id) as 'serial' FROM Logs

| id | num | serial |
| -- | --- | ------ |
| 1  | 1   | 1      |
| 2  | 1   | 2      |
| 3  | 1   | 3      |
| 4  | 2   | 4      |
| 5  | 1   | 5      |
| 6  | 2   | 6      |
| 7  | 2   | 7      |

然后再通过按num分组的row_number()，为每个数字分配在自己组内出现的序号

SELECT id, num, row_number() over(PARTITION BY num ORDER BY id) as 'groupserial' FROM Logs

| id | num | groupserial |
| -- | --- | ----------- |
| 1  | 1   | 1           |
| 2  | 1   | 2           |
| 3  | 1   | 3           |
| 5  | 1   | 4           |
| 4  | 2   | 1           |
| 6  | 2   | 2           |
| 7  | 2   | 3           |

对比两个临时表，如果某一个num连续出现时，serial - groupserial将会是一个固定值

| id | num | serial | groupserial | serial - groupserial |
| -- | --- | ------ | ----------- | -------------------- |
| 1  | 1   | 1      | 1           | 0                    |
| 2  | 1   | 2      | 2           | 0                    |
| 3  | 1   | 3      | 3           | 0                    |
| 5  | 1   | 5      | 4           | 1                    |
| 4  | 2   | 4      | 1           | 3                    |
| 6  | 2   | 6      | 2           | 4                    |
| 7  | 2   | 7      | 3           | 4                    |

利用GROUP BY+HAVING找出COUNT > 3的num可以得到结果

SELECT DISTINCT t1.num as ConsecutiveNums
FROM (
  SELECT id, num, row_number() over(ORDER BY id) - row_number() over(PARTITION BY num ORDER BY id) as 'serialsub'
  FROM Logs
) t1
GROUP BY t1.num, serialsub
HAVING COUNT(1) >= 3

# 日期处理

MySQL的date类型直接进行加减，会转换成数字类型加减

比如'2023-01-01' - 1 = '2023-01-00'，会出现错误

# DATE_ADD(date, INTERVAL expr type)

增加时间间隔，比如增加1天

DATE_ADD('2023-01-01', INTERVAL 1 day)

type可以是day, month, year, week等等

# DATE_SUB(date, INTERVAL expr type)

同上，减少时间间隔

# DATEDIFF(date1, date2)

返回date1与date2之间的间隔天数(date1 - date2)

SELECT DATEDIFF('2023-01-02', '2023-01-01') as diff  # 1
SELECT DATEDIFF('2023-01-01', '2023-01-02') as diff  # -1

SQL Server中可以指定计算的单位：DATEDIFF(type, date1, date2)

SELECT DATEDIFF(year, '2023-01-01', '2020-01-01') as diff  # 3

# COUNT()按条件统计

COUNT(column_name)统计的是column_name列中++不为null++的行数

如果只写条件，比如COUNT(age >= 18)，在age列没有null的情况下，age >= 18返回的是true或false，都会被COUNT()统计进去，所以和COUNT(*)没有区别

因此COUNT()按条件统计需要添加OR NULL，即COUNT(age >= 18 OR NULL)

# 小数保留

# ROUND(number, decimals)

四舍五入保留number的decimals位小数

SELECT ROUND(3.1415, 2)  # 3.14

类似函数还有：

• FLOOR(number)：向下取整
• CEIL(number)：向上取整
• TRUNCATE(number, decimals)：直接截断至decimals位小数

# CAST(expr AS datatype) + Decimal(m, d)

CAST()函数本身用于数据类型转换，也可以用来实现小数保留

Decimal(m, d)中，m指数字的最大位数(1~65，默认为10)，d指小数点后的位数(0~30，默认为0)，并且d <= m

例子：

• Decimal(5, 2): -999.99~999.99，小数点后2位，总共5位
• Decimal(7, 6): -9.999999~9.999999，小数点后6位，总共7位

SELECT CAST(3.1415, Decimal(10, 2))  # 3.14

CAST()+Decimal()转换小数默认为四舍五入