Is it possible to aggregate the following dataset:
| property_name | metric_value |
|---|---|
| A | 20 |
| B | 40 |
| C | 40 |
| null | 100 |
Into:
| property_name | metric_value | total |
|---|---|---|
| A | 20 | 100 |
| B | 40 | 100 |
| C | 40 | 100 |
My goal is to calculate another metric based on the formula: metric_value / total
which I am having troubles with doing as my query is based on groupBy (this is must have, but I can squeeze in another subquery if needed).
The null -> 100 is used using subtotalSpecs in my top level query.
The query I've built so far looks like this:
{
"dataSource":
{
"query":
{
"aggregations": [{"fieldName": "duration","name": "duration","type": "longSum"}],
"dataSource":
{
"condition": "customer_id == \"r.customer_id\"",
"joinType": "LEFT",
"left": "sessions_datasource",
"right":
{
"query":
{
"dataSource": "demographics_datasource",
"dimensions": ["customer_id","weight"],
"granularity": "all",
"intervals": ["P1M/2022-05-02"],
"queryType": "groupBy"
},
"type": "query"
},
"rightPrefix": "r.",
"type": "join"
},
"dimensions": [
"property_name",
{
"dimension": "r.customer_id",
"outputName": "customer_id",
"outputType": "STRING",
"type": "default"
},
{
"dimension": "r.weight",
"outputName": "weight",
"outputType": "LONG",
"type": "default"
}
],
"granularity": "all",
"intervals": ["P1M/2022-05-02"],
"queryType": "groupBy"
},
"type": "query"
},
"dimensions": ["property_name","viewing_hours"],
"granularity": "all",
"intervals": ["P1M/2022-05-02"],
"queryType": "groupBy",
"subtotalsSpec": [
["property_name"],
[]
],
"virtualColumns": [{"expression": "weight * duration","name": "viewing_hours","outputType": "LONG","type": "expression"}]
}
Another idea I might have would be to create a separate query which will calculate the total value and then ingest that as a constant aggregator with pre-calculated value, but if possible I'd like to handle this in a single query