Hard query with aggregation
I am using Oracle SQL and I need help with a hard query.
I have two tables ( table_A
):
GroupID (int)
ClientID (int)
Age (int)
( table_B
):
GroupID (int)
Budget (int)
My request should be fulfilled for each client group:
- Calculate the average age of the group
- Calculate the average budget of each client with age as average age (age can be over or under 1). For example: Average_Age - 1 <Average_Age <Average_Age + 1.
-
Output value
: (budget) / (average budget)
For example
table_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
table_B
:
GroupID | Budget
A | 100
B | 200
C | 300
D | 400
None of the values in the tables are fixed. Example of calculating the output value of group A:
- Average age in group A: (27 + 27 + 21) / 3 = 24.6
- Customer IDs aged 23 to 25: 66, 77, 111, 333. Their average budget: (200 + 300 + 400 * 2) / 4 = 325
- The output value should be: 100/325 = 0.307
The output table should be
GroupID | Output Value
A | 0.307
B | ....
C | ....
D | ....
Any guidance on how to do this? I have tried so many ways. I feel lost.
+3
source to share
2 answers
Prepare test data (fixed typo by name and type):
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;
The request itself:
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
;
Result:
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
+1
source to share
select Q1.GroupID, Q2.Budget/Q1.Budget_avg as Output_value from
(
select GroupID,ClientID,avg(budget) as Budget_avg from table_A A,table_B B
where A.GroupID =B.GroupID
group by GroupID,ClientID
) Q1,(select GroupID,Budget from table_B) Q2
where Q1.GroupID = Q2.GroupID
Can you try this and let me know if it works for you?
0
source to share