带有Group By子句的条件聚合 [英] Conditional aggregate with Group By clause
本文介绍了带有Group By子句的条件聚合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图用HiveQL来做到这一点,但我不知道如何在SQL中做到这一点。表结构如下:
id1 id2类别
123 abc 1
123 def 1
123 def 2
456 abc 1
123 abc 1
123 abc 2
...
我想写一个查询输出:
key count category1count category2count
123-abc 3 2 1
123-def 2 1 1
456-abc 1 1 0
到目前为止,我已经得到了这个: (concat(id1,' - '),id2),count(*),
count(SELECT * WHERE buyingcategory = 1 ???),
count(SELECT * WHERE buyingcategory = 2 ???)
FROM table
GROUP BY concat(concat(id1,' - '),id2)
解决方案
试试这个
SELECT concat(id1,' - ',id2)`key`,count(*),
sum(category = 1 then case 0 end)category1count,
sum(category = 2 then 1 else 0 end)category2count
FROM table1
GROUP BY concat (id1,' - ',id2)
I'm trying to do this with HiveQL but I don't know how to do this in SQL neither. Table structure as follows:
id1 id2 category 123 abc 1 123 def 1 123 def 2 456 abc 1 123 abc 1 123 abc 2 ...
I'd like to write a query that outputs:
key count category1count category2count 123-abc 3 2 1 123-def 2 1 1 456-abc 1 1 0
So far I've got this:
SELECT concat( concat(id1,'-'), id2), count(*) ,
count( SELECT * WHERE buyingcategory = 1 ??? ) ,
count( SELECT * WHERE buyingcategory = 2 ??? )
FROM table
GROUP BY concat( concat(id1,'-'), id2)
解决方案
try this
SELECT concat(id1,'-', id2) `key`, count(*) ,
sum( case when category = 1 then 1 else 0 end) category1count ,
sum( case when category = 2 then 1 else 0 end) category2count
FROM table1
GROUP BY concat(id1,'-', id2)
这篇关于带有Group By子句的条件聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文