sql - BigQuery 标准 SQL : Setting a value to positive or negative infinity

Question

我注意到 docs for BigQuery Standard SQL Mathematical functions我们可以使用 IS_INF() 测试一个数字是否“无穷大”功能是这样的:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT 100 AS val
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

输出:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | 100 | False       |
+---+-----+-------------+

但是否有可能 明确使用某个常量值或关键字将值设置为正无穷大或负无穷大？

也许是这样的:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT +inf AS val -- <-- THIS doesnt work
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

为了提供与此类似的所需输出:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | inf | True        |
+---+-----+-------------+

我尽可能地搜索了文档，并在这方面使用了谷歌，但无法找到答案。

Answer 1

您可以使用 cast() 创建该值:

select is_inf(cast('Infinity' as float64))

或者:

select is_inf(cast('inf' as float64))

埋在 documentation是:

There is no literal representation of NaN or infinity, but the following case-insensitive strings can be explicitly cast to float:

• "NaN"
• "inf" or "+inf"
• "-inf"