Impala SQL中的分区引发错误

问题描述

我正在尝试使用TOAD来计算Impala几个月来的连续亏损总额

I am trying to calculate the running total of loss by the months on Impala using TOAD

以下查询引发错误-选择聚合输出未生成的列表表达式(缺少group by子句)

select
segment,
year(open_dt) as open_year,
months,
sum(balance)
sum(loss) over (PARTITION by segment,year(open_dt) order by months) as NCL
from

tableperf
where
year(open_dt) between 2015 and 2018

group by 1,2,3

推荐答案

您正在混合聚合和窗口函数.我想您可能想要:

You are mixing aggregation and window functions. I think you might want:

select segment, year(open_dt) as open_year, months,
       sum(balance)
       sum(sum(loss)) over (PARTITION by segment, year(open_dt) order by months) as NCL
from tableperf
where year(open_dt) between 2015 and 2018
group by 1, 2, 3;

这将计算每年的累计损失.注意sum(sum(loss))的使用.内部的sum()是聚合函数.外部sum()是窗口函数.

This calculates the cumulative loss within each year. Note the use of sum(sum(loss)). The inner sum() is an aggregation function. The outer sum() is a window function.

这篇关于Impala SQL中的分区引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！