创建多个自定义聚合功能 [英] creating multiple custom aggregation functions

问题描述

我有一个名为Account的表。

I have this table named Account.

此表的简化视图如下：

acct_num | ssn       | branch | open_date    |close_date  | has_product1 |  has_product2      
----------------------------------------------------------------------------------------
0123456  | 123456789 | 01     | 01/01/2000   | NULL       | 1            |  0
0123457  | 123456789 | 02     | 03/05/2004   | NULL       | 0            |  1
1234405  | 322145678 | 04     | 04/16/2016   | 05/01/2016 | 1            |  1
...

注意ssn 123456789的方式有2个帐户。

Notice how the ssn 123456789 has 2 accounts.

我需要创建一个新数据集，该数据集将表按 acct_num 分组，并显示基于计算的新列在每个组中的行上。

I need to create a new data set that groups the table by acct_num and displays new columns that are calculations based on the rows in each group.

这些计算本质上是多种多样的。

These calculations are diverse in nature though.

我需要的表（在此简化示例中）如下：

The table I need (in this simplified example) is as follows:

ssn       |  home_branch    | date_of_first_membership   |   eligibility_indicator
-----------------------------------------------------------------------------------

很显然 ssn 很容易，但是其余的这些现在不在我的范围内。

Obviously ssn is easy, but the rest are beyond me at the moment.

• home分支是的值 open_date 最早且非空的 close_date 的行中的分支。

• home branch is the value of branch from the row that has the earliest open_date and a non-null close_date.

开放日期只是最小的开放日期值

eligibility_status 为1（如果至少有1个打开的帐户 has_product1 和至少1个（可能是不同的）打开帐户 has_product2

eligibility_status is a 1 if at least 1 open account has_product1 and at least 1 (possibly different) open account has_product2

因此，我从上面的示例中期望的结果是：

So the result set that I am expecting from the example above is:

ssn       | home_branch     | date_of_first_membership   | eligibility_indicator
-----------------------------------------------------------------------------------
123456789 | 01              | 01/01/2000                 | 1
322145678 | 04              | 04/16/2016                 | 0

编辑：

这些评论指出了一个矛盾。为了解决这个矛盾，我现在想过滤掉所有没有开设账户的ssn。

the comments pointed out a contradiction. To resolve this contradiction, I now want to filter out all ssn's that don't have any open accounts.

因此，新的预期结果集是：

So, the new expected result set is:

ssn       | home_branch     | date_of_first_membership   | eligibility_indicator
-----------------------------------------------------------------------------------
123456789 | 01              | 01/01/2000                 | 1

推荐答案

您可以使用条件聚合来实现。第一次计算需要一点技巧-获取没有关闭日期的行的最小日期：

You can do this with conditional aggregation. The first calculation needs a bit of a trick -- getting the minimum date for a row with no close date:

select ssn,
       max(case when open_date = min_open_date then branch end) as home_branch,
       min(open_date) as date_of_first_membership,
       (case when max(has_product1) > 0 and max(has_product2) > 0
             then 1 else 0
        end) as eligibility_indicator
from (select a.*,
             min(case when close_date is null then open_date end)  over (partition by ssn ) as min_opendate
      from account a
     ) a
group by ssn;

这篇关于创建多个自定义聚合功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！