sql：如何使用具有多个列的分区计算百分比增加

ckocjqey 于 2021-07-13 发布在 Hive

关注(0)|答案(1)|浏览(368)

我有6个列，分别命名为total\u stock\u value、value\u unrestricted、fiscal\u year、fiscal\u period、plant、storage\u location。我想用total\u stock\u value、value\u unrestricted列的百分比增加值创建一个新列。

Sample data : 
TOTAL_STOCK_VALUE  VALUE_UNRESTRICTED  fiscal_year  fiscal_period  plant  storage_location
336.0                228.0                 2019         10          ABC         AR40
418.0                209.0                 2019         10          ABC         IN80
162.0                0.000                 2020         04          CTF         KK29
86412.0              53060.0               2020         04          ARZ         HD91

Desired Output :
TOTAL_STOCK_VALUE  VALUE_UNRESTRICTED  fiscal_year  fiscal_period  plant  storage_location  New
336.0                228.0                 2019         10          ABC         AR40       32.14%
418.0                209.0                 2019         10          ABC         IN80        50%
162.0                0.000                 2020         04          CTF         KK29        100%
86412.0              53060.0               2020         04          ARZ         HD91       38.59%

已用查询：

select
    TOTAL_STOCK_VALUE,
    VALUE_UNRESTRICTED,
    fiscal_year,
    fiscal_period,
    plant,
    storage_location,
    ((TOTAL_STOCK_VALUE - VALUE_UNRESTRICTED) / TOTAL_STOCK_VALUE) * 100 over(partition by fiscal_year,fiscal_period,plant,storage_location) as New
from
    MyTable

上面的查询没有给出所需的结果，因此引发了一个错误。任何帮助都将不胜感激。

sql Hive impala hue cloudera

来源：https://stackoverflow.com/questions/67138429/sql-how-to-calculate-percentage-increase-using-partition-by-with-multiple-colum

1条答案

按热度按时间

tyu7yeag1#

根据你想要的结果，你似乎只想这样做：

select
    TOTAL_STOCK_VALUE,
    VALUE_UNRESTRICTED,
    fiscal_year,
    fiscal_period,
    plant,
    storage_location,
    (1 - (VALUE_UNRESTRICTED / TOTAL_STOCK_VALUE)) * 100.0 as New
from
    MyTable

赞(0）回复(0）举报 2021-07-13

我来回答

sql：如何使用具有多个列的分区计算百分比增加

1条答案

相关问题

热门标签

最新问答