SparkSQL数值模式详解

简介

函数如 to_number 和 to_char 确实支持在字符串类型和十进制（数值）类型之间进行转换。这些函数接受格式字符串作为参数，这些格式字符串指示了如何在这两种类型之间映射。

• to_number：这个函数通常用于将字符串转换成数值类型。你需要提供一个格式字符串来指定如何解释字符串中的字符，以便正确地转换为数值。格式字符串定义了诸如小数点的位置、千位分隔符的存在与否等细节。
• to_char：相反，这个函数用于将数值类型的数据转换成字符串形式。同样地，它也接受一个格式字符串来定义数值应该以何种格式输出。

语法

数值模式支持下面语法：

 { ' [ MI | S ] [ $ ] 
      [ 0 | 9 | G | , ] [...] 
      [ . | D ] 
      [ 0 | 9 ] [...] 
      [ $ ] [ PR | MI | S ] ' }

语法元素

每个数字格式字符串可以包含以下元素（不区分大小写）：

• 0 or 9
  指定一个介于0和9之间的预期数字。
  格式字符串中的0或9序列与大小相同或更小的数字序列相匹配。如果0/9序列以0开头并且在小数点之前，则需要精确匹配位数：解析时，它只匹配相同大小的数字序列；格式化时，结果字符串会在数字序列中添加带零的左填充，以达到相同的大小。否则，在解析时，0/9序列会匹配大小相同或更小的任何数字序列，并在格式化时在结果字符串中用空格（如果在小数点之前）或零（如果在整数点之后）填充数字序列。请注意，如果大小大于0/9序列，则格式化时数字序列将变为“#”序列。

• . or D
  指定小数点的位置。此字符只能指定一次。
  解析时，输入字符串不需要包含小数点。

• , or G
  指定分组（千）分隔符的位置。
  每个分组分隔符的左右两侧必须有0或9。解析时，输入字符串必须与与数字大小相关的分组分隔符匹配。

• $
  此字符 $ 用于指定货币符号的位置。此字符在格式字符串中只能出现一次。

• S
  此字符用于指定可选的正号 ‘+’ 或负号 ‘-’ 的位置。此字符在格式字符串中只能出现一次。

• MI
  此字符用于指定可选的负号 ‘-’ 的位置。此字符在格式字符串中只能出现一次。

• PR
  将负输入值映射到相应字符串中的尖括号（<1>）。
  正输入值不接受换行尖括号。

函数类型和错误处理

• to_number函数接受输入字符串和格式字符串参数。它要求输入字符串与提供的格式匹配，否则会引发错误。然后，该函数返回相应的Decimal值。
• try_to_nount函数接受输入字符串和格式字符串参数。它的工作原理与to_number函数相同，除了如果输入字符串与给定的数字格式不匹配，它将返回NULL而不是引发错误。
• to_char函数接受输入小数和格式字符串参数。然后，函数返回相应的字符串值。
• 如果给定的格式字符串无效，所有函数都将失败。

示例

以下示例使用to_number、try_to_nonumber和to_char SQL函数。
请注意，在大多数示例中使用的格式字符串都期望：

• 开头有一个可选的符号（正负号），
• 接着是一个美元符号 ($),
• 然后是一个三到六位数的数字，
• 千位分隔符，
• 小数点后最多两位数字。

1. to_number

-- The negative number with currency symbol maps to characters in the format string.
> SELECT to_number('-$12,345.67', 'S$999,099.99');
  -12345.67
 
-- The '$' sign is not optional.
> SELECT to_number('5', '$9');
  Error: the input string does not match the given number format
 
-- The plus sign is optional, and so are fractional digits.
> SELECT to_number('$345', 'S$999,099.99');
  345.00
 
-- The format requires at least three digits.
> SELECT to_number('$45', 'S$999,099.99');
  Error: the input string does not match the given number format
 
-- The format requires at least three digits.
> SELECT to_number('$045', 'S$999,099.99');
  45.00
 
-- MI indicates an optional minus sign at the beginning or end of the input string.
> SELECT to_number('1234-', '999999MI');
  -1234
 
-- PR indicates optional wrapping angel brakets.
> SELECT to_number('9', '999PR')
  9

2. try_to_number

-- The '$' sign is not optional.
> SELECT try_to_number('5', '$9');
  NULL
 
-- The format requires at least three digits.
> SELECT try_to_number('$45', 'S$999,099.99');
  NULL

3. to_char

> SELECT to_char(decimal(454), '999');
  "454"

-- '99' can format digit sequence with a smaller size.
> SELECT to_char(decimal(1), '99.9');
  " 1.0"

-- '000' left-pads 0 for digit sequence with a smaller size.
> SELECT to_char(decimal(45.1), '000.00');
  "045.10"

> SELECT to_char(decimal(12454), '99,999');
  "12,454"

-- digit sequence with a larger size leads to '#' sequence.
> SELECT to_char(decimal(78.12), '$9.99');
  "$#.##"

-- 'S' can be at the end.
> SELECT to_char(decimal(-12454.8), '99,999.9S');
  "12,454.8-"

> SELECT to_char(decimal(12454.8), 'L99,999.9');
  Error: cannot resolve 'to_char(Decimal(12454.8), 'L99,999.9')' due to data type mismatch:
  Unexpected character 'L' found in the format string 'L99,999.9'; the structure of the format
  string must match: [MI|S] [$] [0|9|G|,]* [.|D] [0|9]* [$] [PR|MI|S]; line 1 pos 25

总结

在处理财务数据或其他需要精确格式化和解析数字的应用场景中，SparkSQL 提供了一系列强大的工具来帮助开发者以标准或自定义的方式格式化和解析数字。本文将介绍如何使用 to_number, try_to_number 和 to_char 函数来处理各种数字模式，并且提供一些具体的例子来展示这些功能的实际应用。

1. 数字格式化和解析的重要性

在数据分析中，正确地格式化和解析数字对于确保数据的一致性和准确性至关重要。例如，在处理货币金额时，我们通常希望保留两位小数，并且使用千位分隔符来提高可读性。此外，还需要能够正确处理带有符号的数字以及处理非标准的货币格式。

2. 使用 to_number 和 try_to_number

to_number 和 try_to_number 函数允许你根据给定的模式将字符串转换为数字。to_number 在遇到无法解析的字符串时会抛出异常，而 try_to_number 则会返回 NULL。

3. 使用 to_char

to_char 函数用于将数字转换为按照指定模式格式化的字符串。

4. 结论

通过使用 to_number, try_to_number 和 to_char 函数，我们可以有效地处理各种数字格式。这不仅提高了数据处理的效率，也确保了最终分析结果的准确性。掌握这些工具的使用方法对于任何进行数据处理和分析的人来说都是至关重要的技能。

---

以上就是关于 SparkSQL 中 Number Patterns for Formatting and Parsing 的基本介绍。希望这篇博客能够帮助你在实际项目中更好地应用这些功能。

参考文献

https://spark.apache.org/docs/latest/sql-ref-number-pattern.html

https://spark.apache.org/docs/latest/api/sql/ （可以查看方法是哪个spark版本引入，以及如何使用）