We are trying to aggregate and sort records by ratios (CTR = clicks/impressions). For some obscure reasons the technology we are using does not allow us to do this. We can group and sort on additive values (clicks or impressions) but we cannot calculate the CTR on the fly and sort on it.
I was hoping we can somehow move into the additive space by sorting aggregated additive values derived from clicks and impressions and in the end achieve the same order as if we would sort on CTR.
Example input data:
| ID | GroupId | clicks | views | order-value |
|----|---------|--------|-------|-------------|
| 1 | 1 | 5 | 10 | x1 |
| 2 | 1 | 3 | 10 | x2 |
| 3 | 1 | 1 | 10 | x3 |
| 4 | 2 | 4 | 10 | x4 |
| 5 | 2 | 6 | 10 | x5 |
Grouped (summed) by GroupID and ordered by ctr:
| GroupId | clicks | views | ctr | agg-order-value |
|---------|--------|-------|-----|-----------------|
| 2 | 10 | 20 | 0.5 | sum(x4, x5) |
| 1 | 9 | 30 | 0.3 | sum(x1, x2, x3) |
Is there any other way to get the same aggregated order but without actually needing to calculate the CTR ratio?
Perhaps we can introduce new additive order-values (x1-x5) to the input data table (derived from clicks and impressions) that could help us achieve the same sorting? It would have to have a property that the sum of those order-values would need to preserve the same order as the calculated ctr.
I am hoping we can solve our technology limitation by some clever math idea, not really sure if it's possible.