如何在Spark SQL中一步计算分组价格乘积文本表达式的数值？

初始数据表：

|date       | first_cat  | second_cat | price_change|
|:--------- | :--------- |: --------  |  ----------:|
|30/05/2022 | old        | test_2     |         0.94|
|31/08/2022 | old        | test_3     |         1.24|
|30/05/2022 | old        | test_2     |         0.90|
|31/08/2022 | old        | test_3     |         1.44|
|30/05/2022 | new        | test_1     |         1.94|
|30/06/2022 | new        | test_4     |         0.54|
|31/07/2022 | new        | test_5     |         1.94|
|30/06/2022 | new        | test_4     |         0.96|

需求是按date、first_cat和second_cat分组，计算price_change的乘积。目前使用以下SQL只能得到文本形式的乘积表达式：

SELECT
    date,
    first_cat,
    second_cat,
    array_join(collect_list(price_change), "*") as price_aggr
FROM my_table
GROUP BY
    date,
    first_cat,
    second_cat

需要直接得到数值结果，预期输出：

|date       | first_cat  | second_cat | price_aggr  |
|:--------- | :--------- |: --------  |  ----------:|
|30/05/2022 | old        | test_2     |        0.846|
|31/08/2022 | old        | test_3     |       1.7856|
|30/05/2022 | new        | test_1     |         1.94|
|30/06/2022 | new        | test_4     |       0.5184|
|31/07/2022 | new        | test_5     |         1.94|

要求仅用Spark SQL实现，避免转换为Pandas和使用UDF。

利用对数求和再取指数的数学性质实现分组乘积：乘积的对数等于对数的和，对求和结果取指数即可还原原数值的乘积。

基础版SQL #

适用于price_change无0、无负数的场景：

SELECT
    date,
    first_cat,
    second_cat,
    exp(sum(ln(price_change))) as price_aggr
FROM my_table
GROUP BY
    date,
    first_cat,
    second_cat

兼容0值的SQL #

如果数据中存在price_change=0的情况，可添加判断逻辑：

SELECT
    date,
    first_cat,
    second_cat,
    CASE
        WHEN sum(CASE WHEN price_change = 0 THEN 1 ELSE 0 END) > 0 THEN 0
        ELSE exp(sum(ln(price_change)))
    END as price_aggr
FROM my_table
GROUP BY
    date,
    first_cat,
    second_cat

内容的提问来源于stack exchange，提问作者Harrv7