BigQuery SQL：保留两位小数的百分比

Question

在 BigQuery 标准 SQL 中，我需要将除法运算的结果以百分数形式显示，并保留 2 位小数，例如 21.23%。

我只能通过以下方式实现：

1. 对结果进行ROUND 四舍五入，保留2位小数
2. CAST为字符串
3. FORMAT 添加百分号字符

FORMAT('%s%%', CAST(ROUND((val_1 / val_2) * 100, 2) AS STRING)) 感觉这是一种变通方法... 是否有更好的方法呢？主要是希望输出结果为浮点数，而非字符串。

谢谢！

- florian

---

你的方法似乎是我能想到的最好的方式 - 通常情况下，不会在数据库中格式化百分比 - 你不能将数据连接到报表并在那里进行格式化吗？ - Ben P

你说得对，那可能是最好的解决方案。 - florian

Answer 1

你的方法是可行的。然而，我建议你创建一个TEMP FUNCTION。如果你将要使用它多次，它会让你的代码看起来更好，更容易阅读。

语法如下：

#standardSQL
CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
);
SELECT 
  NUMFORMAT(888888.999), 
  NUMFORMAT(123456.99),
  NUMFORMAT(74185.009),
  NUMFORMAT(-10.789)

请注意，我假设您想要格式化为百分位的数据是 FLOAT64 类型。如果不是 FLOAT64 类型，您可以在函数中更改类型。输出结果如下图所示： 如果您的百分位数太高，您可能还想格式化数字的整数部分。语法如下：

#standardSQL
CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  #CONCAT(REPLACE(REPLACE(REGEXP_REPLACE(FORMAT("%'.2f", number), r'([\d,]+).(\d+)', r'\1=\2'), ',', '.'), '=', ','),'%')
  #CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
  CONCAT(format("%'.2f", cast(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}') as float64)), '%')
  );
SELECT 
  NUMFORMAT(888888.999), 
  NUMFORMAT(123456.99),
  NUMFORMAT(74185.009),
  NUMFORMAT(-10.789)

输出结果将是：

- Alexandre Moraes

Answer 2

round() 不满足您的需求吗？

SELECT ROUND((val_1 / val_2), 2)

- Gordon Linoff

---

当您想要将数字与“%”字符连接起来时，它不起作用。 - florian

@florian...百分号对我来说似乎是多余的。但另一方面，我更喜欢0到1之间的比率。 - Gordon Linoff

Answer 3

正如@nick winridge指出的那样，少于2个非零小数位存在问题。这是我添加的一个小技巧来解决这个问题。

CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(((number + 0.000001) as string), r'\d*\.\d{2}'), ' %')
);

- owlstar-lord

---

这是一个好的解决方案，但是在“cast”之后你有三个左括号，这意味着代码会失败 - 你只需要两个。 - meenaparam

Answer 4

我知道这是一个旧帖子，但是如果传递给函数的参数在小数点后第二位之后没有非零数字，那么亚历山大·莫雷斯的答案将不起作用。

CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
);
SELECT 
  NUMFORMAT(33.33212121), 
  NUMFORMAT(100.00000),
  NUMFORMAT(100.00),
  NUMFORMAT(100),
  NUMFORMAT(100.00111)

返回结果：如果小于2个非零小数位，则返回null值。

- nick windridge