hadoop - 删除列重复字符串值并提取 coulm 值的最大字符串值

Question

我想temp2用max tot_durationand填充count from temp1。我使用以下代码执行此操作：

insert into analytics_db.charith_prepaid_bcell_temp2
(charged_party,cell_id,tot_duration,tband_count)
select  A.*
FROM  analytics_db.charith_prepaid_bcell_temp1  A,
(SELECT A.charged_party,B.B,MAX(A.tot_duration) C-- MAX COUNT FOR MAX TIMA BAND CELL
     FROM  analytics_db.charith_prepaid_bcell_temp1  A,
           (SELECT charged_party,MAX(tband_count) B ---- MAX TIME BAND COUNT
             FROM  analytics_db.charith_prepaid_bcell_temp1 
             WHERE NVL(tot_duration,0) >0
             GROUP BY charged_party) B
     WHERE NVL(tot_duration,0) >0 AND A.charged_party  = B.charged_party  AND A.tband_count = B.B  
     GROUP BY A.charged_party,B.B) C
WHERE A.charged_party = C.charged_party   AND A.tot_duration= C.C AND A.tband_count = C.B

但是在temp2不同的单元格 id 和相同的地方有tot_duration重复tband_count的条目charged_party。如何修复代码以仅获取只有一个具有最大值的条目的条目cell_id？

谢谢

标签： hadoophive

Answer 1

这可以通过排名函数来完成，例如row_number.

select charged_party,cell_id,tot_duration,tband_count
from (select t.*
            ,row_number() over(partition by tot_duration,tband_count order by cell_id desc) as rnum
      from tbl t
     ) t
where rnum = 1