Consider the data like below:
The first two columns that are not hilighted in yellow don't play any role in here. But are kept in the table.
Step1:
We then group it by brand, country, driver, name and level and then sum up the metric_value then it produces the following result.
Step2:
Now, consider the value of the name column in the first row, then pair it with other names that produces the below.
Step3:
For each distinct combination of names, find out the correlation of their original values for matching time column values.
(I have used the arbitrary correlation values for the explanation purpose, the actual value may vary)
Refer to the above screenshot only for the made-up correlation for each distinct combination of names.
Step4:
Pick the first best one. If there is tie, pick anyone of the best.
Now, form a pair like below and combine the values of the metric_value column for matching time which when summed up gives 30 in the below example.
Step5:
Now, with this new combination, we need to form the distinct combination with others like below.
And then find out the correlation for them. I have put a value of 93 arbitrarily for D1_L1V1::D1_L1V2 and D1_L1V3
Now, this pair becomes the best correllated one. And hence form the pair again like below and put it with other original names so that it can be used to form the
distinct combinations
Step6:
Since 95 is the best correlation, I am forming another pair like D1_L1V1::D1_L1V2::D1_L1V3 and also adding their metric_values
Step7:
Now, form the distinct combinations with the remaining ones and then find out the correlation.
Step8:
Finally, I can have the below pairs, depending on the best correlation.
I tried and I could just get the first pair only. But since it needs to be done till we find the last paired names we so that no further correlation is possible, I am requesting help in here. Please let me know your thoughts on this.
So far, my approach:
with dt as
(
select 'SB1' as sub_brand, 'G1' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V1' as "name", 'L1' as "level", 'T1' as tm, 2 as metric_value from dual union all
select 'SB1' as sub_brand, 'G2' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V1' as "name", 'L1' as "level", 'T2' as tm, 4 as metric_value from dual union all
select 'SB1' as sub_brand, 'G3' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V1' as "name", 'L1' as "level", 'T3' as tm, 4 as metric_value from dual union all
select 'SB1' as sub_brand, 'G4' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V2' as "name", 'L1' as "level", 'T1' as tm, 1 as metric_value from dual union all
select 'SB1' as sub_brand, 'G5' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V2' as "name", 'L1' as "level", 'T2' as tm, 5 as metric_value from dual union all
select 'SB1' as sub_brand, 'G6' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V2' as "name", 'L1' as "level", 'T3' as tm, 4 as metric_value from dual union all
select 'SB1' as sub_brand, 'G7' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V2' as "name", 'L1' as "level", 'T4' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G8' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V3' as "name", 'L1' as "level", 'T1' as tm, 5 as metric_value from dual union all
select 'SB1' as sub_brand, 'G9' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V3' as "name", 'L1' as "level", 'T2' as tm, 5 as metric_value from dual union all
select 'SB1' as sub_brand, 'G10' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V3' as "name", 'L1' as "level", 'T3' as tm, 6 as metric_value from dual union all
select 'SB1' as sub_brand, 'G11' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V3' as "name", 'L1' as "level", 'T4' as tm, 8 as metric_value from dual union all
select 'SB1' as sub_brand, 'G12' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V3' as "name", 'L1' as "level", 'T5' as tm, 6 as metric_value from dual union all
select 'SB1' as sub_brand, 'G13' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V4' as "name", 'L1' as "level", 'T1' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G14' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V4' as "name", 'L1' as "level", 'T2' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G15' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V4' as "name", 'L1' as "level", 'T3' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G16' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V4' as "name", 'L1' as "level", 'T4' as tm, 5 as metric_value from dual union all
select 'SB1' as sub_brand, 'G17' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V4' as "name", 'L1' as "level", 'T5' as tm, 5 as metric_value from dual union all
select 'SB1' as sub_brand, 'G18' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V5' as "name", 'L1' as "level", 'T1' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G19' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V5' as "name", 'L1' as "level", 'T2' as tm, 20 as metric_value from dual union all
select 'SB1' as sub_brand, 'G20' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V5' as "name", 'L1' as "level", 'T3' as tm, 10 as metric_value from dual union all
select 'SB1' as sub_brand, 'G21' as geo, 'B1' as brand, 'C1' as country, 'D1' as driver, 'D1_L1V5' as "name", 'L1' as "level", 'T4' as tm, 10 as metric_value from dual
)
,paired_corr as (
select brand,country,driver, n1||':'||n2 as pair,"level", s1+s2 as pair_sum
from (
SELECT c1.brand, c1.country, c1.driver, c1."name" AS n1, c2."name" AS n2, c1."level",
(100* CORR(c1.metric_value, c2.metric_value)) AS correlation,
dense_rank() over(partition by c1.brand, c1.country, c1.driver, c1."level" order by (100* CORR(c1.metric_value, c2.metric_value)) desc nulls last) AS corr_rnk,
listagg(c1.metric_value, ', ') as val1,
listagg(c2.metric_value, ', ') as val2,
sum(c1.metric_value) as s1,
sum(c2.metric_value) as s2
FROM dt c1
JOIN dt c2
ON c1.brand = c2.brand
AND c1.country = c2.country
AND c1.driver = c2.driver
AND c1."level" = c2."level"
and c1.tm = c2.tm
WHERE c1."name" < c2."name"
GROUP BY c1.brand, c1.country, c1.driver, c1."name", c2."name", c1."level"
)
where corr_rnk = 1
)
select * from paired_corr
Oracle Version: Oracle 12c or above.