在Hive中的COUNT()中包含0
在Hive中的COUNT()中包含0
提问于 2022-05-29 22:01:15
我有一个用户和药物的表，并希望返回所有药物的用户计数，即使计数是0。结果将包含用于用户、med和count的列，以及9个用户x9 meds = 81行键行。
user = ['1', '2', '3', '4', '5', '6', '7', '8', '9']
med = ['acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine', 'cyclophosphamide']我试过：
SELECT user, med, COUNT(*)
FROM db.table
WHERE user IN ('1', '2', '3', '4', '5', '6', '7', '8', '9')
AND med IN ('acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine','cyclophosphamide')
GROUP BY user, med
ORDER BY user ASC但是，这只返回计数(*)> 0的用户对meds的计数。如何将其更改为返回所有计数？
回答 1
Stack Overflow用户
发布于 2022-05-30 20:19:39
据我所知，您需要获得给用户的med计数，如果没有，则显示0。这是你可以使用的qry。
SELECT user,
SUM(case when med IN ('acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine','cyclophosphamide') then 1 else 0 end ) as med_count
--this will give you count of medicine taken by user. If 0 then the count will be 0. But its difficult to show the name of medicine.
FROM db.table
WHERE user IN ('1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY user
ORDER BY user ASC编辑:您能试一下这个sql吗？它会给你一个用户的医学计数。如果地中海不在列表中，它将显示0。
SELECT user,
SUM(case when med IN ('acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine','cyclophosphamide') then 1 else 0 end ) as med_count,
--this will give you count of medicine taken by user. If not in listed med then the count will be 0.
case when med IN ('acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine','cyclophosphamide') then med else null end as med
FROM db.table
WHERE user IN ('1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY user ,case when med IN ('acyclovir' ,'azathioprine' ,'basiliximab' ,'bevacizumab' ,'carboplatin','ciprofloxacin_dexamethasone_otic' ,'cisplatin' ,'clofarabine','cyclophosphamide') then med else null end
ORDER BY user ASC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72427567
复制相关文章
相似问题
腾讯云开发者
