如何为SQL中的每个特定列应用窗口

Question

我想为每个特定单元为每个用户完成最后一个事件_of_measure:

我有这张桌子：

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
1           20200801120101  "some description"     "unit1"
1           20200801120501  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"
2           20200801120301  "some description 3"   "unit1"
2           20200801120501  "some description 4"   "unit1"

预期输出为：

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
1           20200801120101  "some description"     "unit1"
2           20200801120301  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"

我尝试过的：

select * 
from 
(select 
person_id, event_time, event_derscription, unit_of_measure, 
rank() over (partition by unit_of_measure order by event_time desc) as RN 
from 
test.person_events 
where 
partition_name = 20200801 
group by 
person_id, event_time, event_description, unit_of_measure) 
where 
RN = 1;  // I try to use group by person_id to get the result for each person_id but it did not work 

我上面代码的输出是：

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
2           20200801120301  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"

我犯了什么错误吗？

Answer 1

我认为您想要的查询是：

select person_id, event_time, event_derscription, unit_of_measure
from (select pe,
row_number() over (partition BY unit_of_measure, person_id order by event_time desc) as seqnum
from test.person_events pe
where partition_name = 20200801 
) pe
where seqnum = 1; 

注：

• 问题的主要解决方案是在partition by中包含person_id
• 我认为不需要group by。你的问题中没有提到为什么它是可取的
• 要获取一行，请使用row_number()而不是rank()。即使你没有重复，它也传达了你想要一排的意图