BigQuery-使用CONCAT时CASE WHEN错误中混合数据类型

Question

因此，当满足条件#1时，我正试图实现这样的"2000*"。但是，BigQuery会返回数据类型错误。我假设将SUM强制转换为字符串应该已经解析了数据混合类型。发生了什么？

以下是错误：No matching signature for operator CASE; all THEN/ELSE arguments must be coercible to a common type but found: NUMERIC, STRING; actual argument types (WHEN THEN) ELSE: (BOOL STRING) (BOOL STRING) (BOOL STRING) NUMERIC at [3:3]

这是我的代码：

#standardSQL
SELECT
CASE
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.05 AND SUM(discount_amount)/SUM(grand_total) < 0.1) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' *')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.1
AND SUM(discount_amount)/SUM(grand_total) < 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' **')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' ***')
ELSE
SUM(grand_total*rate)
END
AS totalRevenue
FROM
`focused-bridge-180706.mysql.glamira_sales_order` s
JOIN
`focused-bridge-180706.mysql.glamira_directory_currency_rate` r
ON
s.order_currency_code = r.currency_from
WHERE
currency_to = 'EUR'
AND status IN ('complete',
'processing',
'payment_accepted')
AND DATE(created_at) = (CURRENT_DATE('Europe/Berlin') - 1)
AND store_name LIKE '%AE'

Answer 1

在本部分中：

CASE
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.05 AND SUM(discount_amount)/SUM(grand_total) < 0.1) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' *')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.1
AND SUM(discount_amount)/SUM(grand_total) < 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' **')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' ***')
ELSE
SUM(grand_total*rate)
END

SUM(grand_total*rate)是数字，其中CONCAT(CAST(SUM(grand_total*rate) AS string), ' **')是字符串，它们需要是相同的类型。

这里有一个修复：

#standardSQL
SELECT
CASE
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.05 AND SUM(discount_amount)/SUM(grand_total) < 0.1) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' *')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.1
AND SUM(discount_amount)/SUM(grand_total) < 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' **')
WHEN (SUM(discount_amount)/SUM(grand_total) >= 0.12) THEN CONCAT(CAST(SUM(grand_total*rate) AS string), ' ***')
ELSE
CAST(SUM(grand_total*rate) AS string)
END
AS totalRevenue
FROM
`focused-bridge-180706.mysql.glamira_sales_order` s
JOIN
`focused-bridge-180706.mysql.glamira_directory_currency_rate` r
ON
s.order_currency_code = r.currency_from
WHERE
currency_to = 'EUR'
AND status IN ('complete',
'processing',
'payment_accepted')
AND DATE(created_at) = (CURRENT_DATE('Europe/Berlin') - 1)
AND store_name LIKE '%AE'