同时使用max和sum

xmakbtuz 于 2021-05-29 发布在 Hadoop

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

我有一个情况，我必须总和和最大数量处理多个条目每天。我在cloudera配置单元中有一个输入表：

----------------------------
    date1    |  date2    |  qty
    ----------------------------
    20180101 | 20180101 |   50
    ----------------------------
    20180101 | 20180101  |  15
    ----------------------------
    20180101 | 20180102  | 1
    ----------------------------
    20180101 | 20180103  | 3
    ----------------------------
    20180101 | 20180104  | 2
    ----------------------------
Final answer I need is :

    ----------------------------
    date1      |    qty
     ----------------------------
    20180101   |    56
    ----------------------------

Logic: For date1 cal 20180101   
= max(20180101 i.e date2) + sum ( all other date2)   
= max(50,15) + sum(1,3,2) 
= 56

**Will the lag / lead work here, can anyone help me with a hint of the 

solution? 

Thanks in advance.**

hadoop Hive cloudera

来源：https://stackoverflow.com/questions/52082239/use-max-sum-at-same-time

1条答案

按热度按时间

yyyllmsg1#

您可以尝试下面的查询和检查

select
date1,
Sum(qty_new)
from
(select date1, date2, max(qty) qty_new from 
table group by date1, date2) a
group by date1

赞(0）回复(0）举报 2021-05-29

我来回答

同时使用max和sum

1条答案

相关问题

热门标签

最新问答