SQL连续出现三次的数字—发生BIGINT UNSIGNED value is out of range解决方法

小灰灰 2023-10-02 22:59 7阅读 0赞

#### 题目背景 ####

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
    | Id | Num |
    +----+-----+
    | 1  |  1  |
    | 2  |  1  |
    | 3  |  1  |
    | 4  |  2  |
    | 5  |  1  |
    | 6  |  2  |
    | 7  |  2  |
    +----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
    | ConsecutiveNums |
    +-----------------+
    | 1               |
    +-----------------+

#### 解决方法：开窗函数 ####

select distinct num as ConsecutiveNums from
    (
    select num,id-rnk as chazhi from
    (
    select id, num, row_number()over(partition by num order by id) as rnk from logs
    ) t
    ) p
    group by num,chazhi
    having count(*) >= 3

会发生如下错误：BIGINT UNSIGNED value is out of range in ‘(`t`.`id` \- `t`.`rnk`)’

#### 错误解释 ####

参考链接  
链接: [https://dev.mysql.com/doc/refman/5.6/en/out-of-range-and-overflow.html][https_dev.mysql.com_doc_refman_5.6_en_out-of-range-and-overflow.html]

关于超出范围和溢出处理：

##### 1.当计算有符号数值可能发生的错误 #####

数值表达式计算期间的溢出会导致错误。例如，最大的有符号BIGINT值为 9223372036854775807，因此以下表达式生成 错误：

mysql> SELECT 9223372036854775807 + 1;
    ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

**要使操作在这种情况下成功，请将值为无符号;**

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
    +-------------------------------------------+
    | CAST(9223372036854775807 AS UNSIGNED) + 1 |
    +-------------------------------------------+
    |                       9223372036854775808 |
    +-------------------------------------------+

是否发生溢出取决于操作数的范围，因此 处理上述表达式的另一种方法是使用 精确值算法，因为DECIMAL值具有更大的 比整数的范围：

mysql> SELECT 9223372036854775807.0 + 1;
    +---------------------------+
    | 9223372036854775807.0 + 1 |
    +---------------------------+
    |     9223372036854775808.0 |
    +---------------------------+

##### 2.当计算数值无符号可能发生的错误 #####

整数值之间的减法（其中一个有类型）产生无符号结果 违约。如果结果本来是负面的，则 错误结果：UNSIGNED  
**此时情况与题目出现错误一致**

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
    ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

###### 要使操作在这种情况下成功，请使用有符号进行相减 ######

select distinct num as ConsecutiveNums from
    (
    select num,id-cast(rnk as signed) as chazhi from
    (
    select id, num, row_number()over(PARTITION by num order by id) as rnk from logs
    ) t
    ) p
    group by num,chazhi
    having count(*) >= 3

[https_dev.mysql.com_doc_refman_5.6_en_out-of-range-and-overflow.html]: https://dev.mysql.com/doc/refman/5.6/en/out-of-range-and-overflow.html