排除qubole中具有特定值的记录

w41d8nur 于 2021-05-27 发布在 Hadoop

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

使用qubole
我有
表a（json分析中的列…）

ID  Recommendation    Decision
1     GOOD            GOOD
2     BAD             BAD
2     GOOD            BAD
3     GOOD            BAD
4     BAD             GOOD
4     GOOD            BAD

我只需要选择建议好，但决策不好的ID。因此输出应该是3。
我试过：

SELECT a.ID  
FROM (
select json_parsed['ID'] as ID
,json_parsed["Decision"] as Decision
,json_parsed["Recommendation"] as Recommendation
from  A  
where create_date >= '2020-11-18') a
Left JOin
(select json_parsed['ID'] as ID
,json_parsed["Decision"] as Decision
,json_parsed["Recommendation"] as Recommendation
from  A
where create_date >= '2020-11-18') as b on a.ID = b.ID and b.Recommendation = "GOOD"
Where
b.Recommendation is NULL

sql hadoop Hive hiveql qubole

来源：https://stackoverflow.com/questions/64982345/exclude-records-with-certain-values-in-qubole

1条答案

按热度按时间

ifsvaxew1#

使用分析函数。
演示：

with your_table as (--use your table instead of this sample
select stack(6,
1,'GOOD','GOOD',
2,'BAD','BAD'  ,
2,'GOOD','BAD' ,
3,'GOOD','BAD' ,
4,'BAD','GOOD' ,
4,'GOOD','BAD') as (ID,Recommendation,Decision)
)

select ID,Recommendation,Decision
from
(
select d.*, 
       count(*) over(partition by id) as cnt,
       count(case when Recommendation  = 'GOOD' then 1 end) over(partition by id) cnt_Recommendation_good, 
       count(case when Decision  = 'BAD' then 1 end) over(partition by id) cnt_Decision_BAD
  from 
  your_table d
) s
where cnt_Recommendation_good=cnt
  and cnt_Decision_BAD = cnt

结果：

id  recommendation  decision
3   GOOD            BAD

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

我来回答

排除qubole中具有特定值的记录

1条答案

相关问题

热门标签

最新问答