获取每个类别的最新状态 [英] Get most recent status for each category
问题描述
我遇到了一个有趣的问题.我有一个名为email_track
的表,用于跟踪每个类别(如邀请,新闻稿)的电子邮件状态
I came across this interesting problem. I have a table named email_track
to track email status for each category say (invitation, newsletter)
这是我的表格数据的外观,
This is how my table data looks,
通过以下查询,我可以获得每个to_email
的最新记录,
With these following queries I'm able to get most recent record for each to_email
,
with `et2` as (
select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, ROW_NUMBER() OVER (partition by `to_email` order by `id` desc) as `rn`
from `email_track` `et1`
)
select * from `et2` where `rn` = 1;
select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, `et2`.`id`
from `email_track` `et1`
left join `email_track` `et2` on (`et1`.`to_email` = `et2`.`to_email` and `et1`.`id` < `et2`.`id`)
where `et2`.`id` is null;
我期望的是电子邮件john@example.com
,我应该获得两条记录,一条用于类别邀请,另一条用于新闻通讯.现在,由于我们按to_email
进行分区,因此不会得到该结果.
What I'm expecting is for email john@example.com
I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by to_email
推荐答案
我应该获得两条记录,一条用于类别邀请,另一条用于新闻通讯.现在,由于我们按
to_email
进行分区,因此不会得到该结果.
I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by
to_email
.
在窗口函数的partition by
子句中添加category
应该足以为您提供所需的结果:
Adding the category
to the partition by
clause of the window function should be enough to give your the result that you want:
with et2 as (
select et1.category, et1.to_email, et1.subject, et1.status,
row_number() over(partition by to_email, category order by id desc) as rn
from email_track et1
)
select * from et2 where rn = 1;
这篇关于获取每个类别的最新状态的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!