返回NaN（Not a Number）的原因及解决办法

一、示例

下面是一行示例代码，在运行时可能会出现返回NaN的问题。

concat(toString(round(ifnull(l.busy_dur / l.sign_in_dur, 0) *100, 2)), '%')

下面我们分析一下原因：

这个公式计算的是 busy_dur 除以 sign_in_dur 的百分比。如果 sign_in_dur 为零，那么 busy_dur / l.sign_in_dur 将导致除以零的操作，结果为 NaN（Not a Number）。

在 SQL 中，除以零通常会返回 NULL 或 NaN，具体取决于数据库系统的实现。

在这个情况下，ifnull 函数将 NULL 转换为 0，但如果没有正确处理 sign_in_dur 为零的情况，仍然可能导致 NaN 的出现。

二、解决办法

为了解决这个问题，可以使用 nullif 函数来确保分母不为零。

nullif 函数会在两个参数相等时返回 NULL，否则返回第一个参数。通过这种方式，可以避免除以零的情况

修改后的SQL片段：

concat(toString(round(ifnull(l.busy_dur /nullif(l.sign_in_dur, 0), 0) *100, 2)), '%')

        在这个修改后的公式中，nullif(l.sign_in_dur, 0) 会将 sign_in_dur 为零的情况转换为 NULL，从而避免了除以零的操作。ifnull 函数将 NULL 转换为 0，确保最终结果为 "0.00%" 而不是 "nan%"。

三、具体例子

假设我们有以下数据：

job_number	sign_in_dur	busy_dur
1	0	10
2	5	2
3	10	5

在原始公式中，计算 busy_rate 的结果如下：

对于 job_number 为 1 的记录，busy_dur / sign_in_dur 将导致除以零，结果为 NaN。

对于 job_number 为 2 的记录，busy_dur / sign_in_dur 为 2 / 5 = 0.4，结果为 "40.00%"。

对于 job_number 为 3 的记录，busy_dur / sign_in_dur 为 5 / 10 = 0.5，结果为 "50.00%"。

在修改后的公式中，计算 busy_rate 的结果如下：

对于 job_number 为 1 的记录，nullif(sign_in_dur, 0) 将 sign_in_dur 转换为 NULL，ifnull(l.busy_dur / NULL, 0) 将结果转换为 0，最终结果为 "0.00%"。

对于 job_number 为 2 的记录，busy_dur / sign_in_dur 为 2 / 5 = 0.4，结果为 "40.00%"。

对于 job_number 为 3 的记录，busy_dur / sign_in_dur 为 5 / 10 = 0.5，结果为 "50.00%"。

通过这种方式，可以确保所有记录的 busy_rate 都是有效的百分比值，而不会出现 nan%。

四、注意事项

1、数据库兼容性

nullif 函数在大多数 SQL 数据库中都可用，但具体语法可能会有所不同。确保所使用的数据库支持 nullif 函数。

2、性能考虑

使用 nullif 和 ifnull 函数可能会对查询性能产生轻微影响，尤其是在大数据集上。确保在生产环境中进行性能测试。

3、数据验证

在应用这些修改之前，确保对现有数据进行验证，确认修改后的查询结果符合预期。