存储最大交易金额并在回顾时段中保留第二高交易金额

Question

我有以下查询(HiveQL)，它计算客户从进行交易之日起的最大交易金额，并保留交易进行日期之前的最大交易金额。

查询

PROC SQL;
  CREATE TABLE TXN_AMT AS
    SELECT *, 
          MAX(TXN_AMT) OVER (PARTITION BY CUST ORDER BY TXN_DATE DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MAX
          MAX(TXN_AMT) OVER (PARTITION BY CUST ORDER BY TXN_DATE DESC ROWS BETWEEN 1 AND UNBOUNDED FOLLOWING) as Max2
   FROM dataset; 
QUIT;

输出数据示例

cust txn_date txn_amt max max2   
1    29/3/20   100    100  70  
1    28/3/20   50     70   20  
1    2/3/20    20     70   20  
1    15/2/20   70     70   - (no previous txn to take max2)  

问题

我需要在一个有数百万行的数据集上运行这个查询。不幸的是，这个查询不能针对360k行的数据集运行。

尝试的解决方案

我尝试直接在Hive & Spark上运行查询，但没有成功-运行时间太长。我曾尝试使用lag()函数重写代码，但没有获得很大成功。

有没有人知道如何重写上面的查询，以便以最佳性能生成所需的输出？查询需要使用Python或HiveQL。

谢谢

Answer 1

我对性能问题的猜测是，您有一些拥有大量事务的离群值客户。我的猜测是，Hive将在单个处理器上运行窗口函数的order by组件。

您可以检查是否有任何具有聚合的异常值：

select cust, count(*)
from dataset
group by cust
order by count(*) desc;

如果您随后从查询中过滤掉离群值，您可能会发现它的效果要好得多。