sql - 使用Hive查询语言将字符串列转换为3位数字刻度

Question

我有一个选择声明如下

SELECT t5.Name AS ProductName
, CAST (t5.salerevenue AS DECIMAL(20,3)) AS Revenue
, CASE WHEN t5.PreviousRevenue <> 0 THEN CAST(t5.PresentRevenue/t5.PreviousRevenue*100 AS STRING)
       ELSE 'NA' 
       END AS RevenueTrend  

我想将RevenueTrend转换为最大3分制的小数(20. 123 )
我必须将其强制转换为String，因为未满足 WHEN t5.PreviousRevenue <> 0，因此我必须将其显示为“NA”

我尝试这样做如下

CASE WHEN t5.PreviousRevenue <> 0 THEN CAST((CAST(t5.PresentRevenue/t5.PreviousRevenue*100) AS DECIMAL(20,3)) AS STRING)
     ELSE 'NA' 
     END AS RevenueTrend    

但我收到语法错误。
预计收入部分。我希望收入趋势部分也像收入一样被预测。有什么办法可以做到这一点？

Answer 1

尝试先转换为十进制，然后转换为字符串:

SELECT . . .
       (CASE WHEN t5.PreviousRevenue <> 0
             THEN CAST(CAST(t5.PresentRevenue/t5.PreviousRevenue*100 AS DECIMAL(20,3)) as string)
              ELSE 'N/A'
        END) AS RevenueTrend