sql - 带聚合的硬查询

Question

我正在使用 Oracle SQL，我需要有关硬查询的帮助。

我有两张 table (table_A):

GroupID (int)
ClientID (int)
Age (int)

(table_B):

GroupID (int)
Budget (int)

我的查询应该针对每组客户:

• 计算该组的平均年龄
• 计算平均预算每个客户的年龄都像平均年龄(年龄可以更大或更小1).例如:Average_Age - 1 < Average_Age < Average_Age + 1。
• 输出值:(预算)/(平均预算)

例如:表_A:

GroupID | ClientID  | Age
    A   |   11      | 26
    A   |   22      | 27
    A   |   33      | 21
    B   |   44      | 22
    B   |   55      | 29
    B   |   66      | 25
    C   |   77      | 23
    C   |   88      | 22
    C   |   99      | 20
    D   |   111     | 24
    D   |   222     | 26
    D   |   333     | 25

表_B:

GroupID | Budget
   A    |  100
   B    |  200
   C    |  300
   D    |  400

表中的值都不是固定的。A组输出值计算示例:

• A组平均年龄:(27+27+21)/3 = 24.6
• 年龄在 23-25 岁之间的客户 ID 是:66、77、111、333。他们的平均预算是:(200+300+400*2)/4 = 325
• 输出值应该是:100/325 = 0.307

输出表应该是

 GroupID    |  Output Value
    A       |     0.307
    B       |     ....
    C       |     ....
    D       |     ....

有什么建议如何做到这一点？我已经尝试了很多方法。我迷路了。

Answer 1

准备测试数据(修复名称和类型的错别字):

drop table table_a;

create table table_a(
GroupID  varchar2(10),
ClientID int,
Age      int
);

drop table table_b;

create table table_b(
GroupID varchar2(10),
Budget  int
);


insert into table_a values('A',   11  , 26);
insert into table_a values('A',   22  , 27);
insert into table_a values('A',   33  , 21);
insert into table_a values('B',   44  , 22);
insert into table_a values('B',   55  , 29);
insert into table_a values('B',   66  , 25);
insert into table_a values('C',   77  , 23);
insert into table_a values('C',   88  , 22);
insert into table_a values('C',   99  , 20);
insert into table_a values('D',   111 , 24);
insert into table_a values('D',   222 , 26);
insert into table_a values('D',   333 , 25);

insert into table_b values('A',   100);
insert into table_b values('B',   200);
insert into table_b values('C',   300);
insert into table_b values('D',   400);

commit;

查询本身:

select a1.GroupId, a2.groupid, a2.clientid, b1.budget/avg(b2.budget) over(partition by a1.groupid) as avg_budget
 from (select GroupId, trunc(avg(age)) as avg_age
        from table_a
       group by GroupId
     ) a1
 inner join table_a a2
    on a2.age between a1.avg_age - 1 and a1.avg_age + 1
 inner join table_b b1
    on b1.groupid = a1.groupid    
 inner join table_b b2
    on b2.groupid = a2.groupid    
order by a1.GroupId, a2.clientid
;            

结果:

GROUPID GROUPID_1   CLIENTID    AVG_BUDGET
A   B   66  0.307692307692308
A   C   77  0.307692307692308
A   D   111 0.307692307692308
A   D   333 0.307692307692308
B   A   11  0.666666666666667
B   B   66  0.666666666666667
B   D   111 0.666666666666667
B   D   222 0.666666666666667
B   D   333 0.666666666666667
C   A   33  1.33333333333333
C   B   44  1.33333333333333
C   C   88  1.33333333333333
C   C   99  1.33333333333333
D   A   11  1.33333333333333
D   B   66  1.33333333333333
D   D   111 1.33333333333333
D   D   222 1.33333333333333
D   D   333 1.33333333333333