在非聚合查询中时的聚合情况 [英] Aggregate case when inside non aggregate query
问题描述
我有一个非常庞大的查询,其最简单的形式如下:
I have a pretty massive query that in its simplest form looks like this:
select r.rep_id, u.user_id, u.signup_date, pi.application_date, pi.management_date, aum
from table1 r
left join table2 u on r.user_id=u.user_id
left join table3 pi on u.user_id=pi.user_id
我需要再添加一个条件,以使我获得每个代表的应用程序日期不为空的用户数(例如:rep 1具有3个用户的应用程序日期已填满),并将其分配给类别(由于3个用户,rep是一个某些状态类别).看起来像这样:
I need to add one more condition that gives me count of users with non null application date per rep (like: rep 1 has 3 users with filled application dates), and assign it into categories (since 3 users, rep is a certain status category). This looks something like this:
case when sum(case when application_date is not null then 1 else 0 end) >=10 then 'status1'
when sum(case when application_date is not null then 1 else 0 end) >=5 then 'status2'
when sum(case when application_date is not null then 1 else 0 end) >=1 then 'status3'
else 'no_status' end as category
但是,如果我只是将其添加到select语句中,则所有代表都将变为status1,因为sum()是在所有使用申请日期的顾问程序上完成的:
However, if I was to simply add it to the select statement, all reps will becomes of status1 because the sum() is done over all advisors with application dates filled:
select r.rep_id, u.user_id, u.signup_date, pi.application_date, pi.management_date, aum,
(
select case when sum(case when application_date is not null then 1 else 0 end) >=10 then 'status1'
when sum(case when application_date is not null then 1 else 0 end) >=5 then 'status2'
when sum(case when application_date is not null then 1 else 0 end) >=1 then 'status3'
else 'no_status' end as category
from table3
) as category
from table1 r
left join table2 u on r.user_id=u.user_id
left join table3 pi on u.user_id=pi.user_id
您能协助我在查询中添加更多内容吗?非常感激!
Can you assist with having the addition to my query to be across reps and not overall? Much appreciated!
推荐答案
根据您的描述,我认为您需要一个窗口函数:
Based on your description, I think you need a window function:
select r.rep_id, u.user_id, u.signup_date, pi.application_date, pi.management_date, aum,
count(pi.application_date) over (partition by r.rep_id) as newcol
from table1 r left join
table2 u
on r.user_id = u.user_id left join
table3 pi
on u.user_id = pi.user_id;
如果您愿意,可以在case
中使用count()
来获取范围.
You can use the count()
in a case
to get ranges, if that is what you prefer.
这篇关于在非聚合查询中时的聚合情况的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!