通过电子邮件获取最大 post_year 组和年数 [英] Getting max post_year group by email with num of years

问题描述

给定下表

电子邮件 Post_Year 国家========================[e-mail] 2006 美国[e-mail] 2007 美国[e-mail] 2008 [e-mail] 2008 香港[e-mail] 2009 [e-mail] 2010 SW

我想要

1. 所有列有 max Post_year 分组通过电子邮件，如果有多个 max Post_year，只需选择一个

2. 以及该特定电子邮件的 max(post_year)-min(post_year) 的 num_of_yrs.

<块引用>

电子邮件 Post_Year 国家 Num_Of_Yrs====================================[e-mail] 2008 HK 2 [即2008-2006][e-mail] 2010 SW 1 [即 2010-2009]

如何达到目的?

解决方案

您可以使用窗口函数 row_number 获取每封电子邮件最大年份的一行，并使用窗口函数 max 和 min 获取每封电子邮件的最大差异.

试试这个:

选择 *从 (选择 t.*,row_number() over(通过电子邮件分区按 post_year desc 排序) rn,max(post_year) over(通过电子邮件分区)-min(post_year) over(通过电子邮件分区)num_of_yrs从 your_table t) t 其中 rn = 1;

Given the table below

Email  Post_Year  country
====== ========== =======
[e-mail]  2006       US
[e-mail]  2007       US
[e-mail]  2008       HK1
[e-mail]  2008       HK
[e-mail]  2009       CN
[e-mail]  2010       SW

I want to have

1. all columns with max Post_year group by email, if there are multiple max Post_year, just choose one

2. as well as the num_of_yrs which is max(post_year)-min(post_year) of that particular email.

Email  Post_Year  country  Num_Of_Yrs
====== ========== ======= =============
[e-mail]  2008      HK       2 [which is 2008-2006]
[e-mail]  2010      SW       1 [which is 2010-2009]

How to achieve the purpose?

解决方案

You can use window function row_number to get one row with max year per email and window functions max and min to get max difference for each email.

Try this:

select *
from (
    select t.*,
        row_number() over (partition by email 
                            order by post_year desc) rn,
        max(post_year) over (partition by email) -
        min(post_year) over (partition by email) num_of_yrs
    from your_table t
    ) t where rn = 1;

这篇关于通过电子邮件获取最大 post_year 组和年数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！