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

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

小灰灰 2023-10-02 22:59 90阅读 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

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

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