This page provides examples of how common Expression Editor functions are represented in the QueryAPI Data Transfer Object (QueryApiDTO).
SUM
This example shows how to get SUM of Bonus by Channel from the Transaction Datamart.
JSON Code
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [ // Sum of Bonus
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
}
],
"dimensions": [ // Group by Channel
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
}
]
}
}
}
Highlighted Structure
Highlighted structure
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [ // Sum of Bonus
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
}
],
"dimensions": [ // Group by Channel
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
}
]
}
}
}
MIN, MAX
This example shows how to get the MIN(ListPrice) and MAX(ListPrice) by Region.
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "ListPrice",
"expression": {
"kind": "columnReference",
"column": "ListPrice",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "m1",
"expression": {
"kind": "function",
"name": "min",
"arguments": [
{
"kind": "columnReference",
"column": "ListPrice",
"source": "previousStage"
}
]
}
},
{
"kind": "selectable",
"alias": "m2",
"expression": {
"kind": "function",
"name": "max",
"arguments": [
{
"kind": "columnReference",
"column": "ListPrice",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
ADD
This example shows how to create a column with value = SUM(Bonus) + SUM(Base) group by Channel.
QueryAPI Data Transfer Object
QueryApiDTO
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Base",
"expression": {
"kind": "columnReference",
"column": "Base",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "plus",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
},
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Base",
"source": "previousStage"
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
}
]
}
}
}
SUM(columnAfromPreviousStep + ColumnBfromPreviousStep)
This example shows how to create SUM(Bonus) by Channel and Region as m1, SUM(Base) by Channel and Region as m2. Then Select SUM(m1 + m2).
JSON Code
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Base",
"expression": {
"kind": "columnReference",
"column": "Base",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
},
{
"alias": "m2",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Base",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "function",
"name": "plus",
"arguments": [
{
"kind": "columnReference",
"column": "m1",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "m2",
"source": "previousStage"
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
Highlighted Structure
Highlighted structure
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Base",
"expression": {
"kind": "columnReference",
"column": "Base",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
},
{
"alias": "m2",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Base",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "function",
"name": "plus",
"arguments": [
{
"kind": "columnReference",
"column": "m1",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "m2",
"source": "previousStage"
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
MINUS
This example shows how to create column with value = SUM(ListPrice) - SUM(Base) group by Channel.
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Base",
"expression": {
"kind": "columnReference",
"column": "Base",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "ListPrice",
"expression": {
"kind": "columnReference",
"column": "ListPrice",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "minus",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "ListPrice",
"source": "previousStage"
}
]
},
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Base",
"source": "previousStage"
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
}
]
}
}
}
MULTIPLY
This example shows how to create column with value = SUM(Bonus) * SUM(Quantity) group by Region.
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Quantity",
"expression": {
"kind": "columnReference",
"column": "Quantity",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "multiply",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
},
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Quantity",
"source": "previousStage"
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
DIVISION
This example shows how to create a division with NULLIF. In case SUM(Quantity) is 0, then it is converted to NULL;
value = SUM(Bonus) / NULLIF(SUM(Quantity), 0)
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Quantity",
"expression": {
"kind": "columnReference",
"column": "Quantity",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "div",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
},
{
"kind": "function",
"name": "nullIf",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Quantity",
"source": "previousStage"
}
]
},
{
"kind": "literal",
"type": "INTEGER",
"value": 0
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
IFNULL
This example shows how to select the Channel, if its value is null then show [N/A].
QueryAPI Data Transfer Object
QueryApiDTO
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "function",
"name": "coalesce",
"arguments": [
{
"kind": "columnReference",
"column": "Channel",
"source": "table"
},
{
"kind": "literal",
"type": "string",
"value": "[N/A]"
}
]
}
}
]
},
{
"kind": "distinct"
}
]
}
}
}
NULLIF
This example shows how to divide ListPrice by Quantity. In case Quantity is 0 then convert it to null, so divide by null will return null instead of throwing exception divide by 0.
QueryAPI Data Transfer Object
QueryApiDTO
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "ListPrice/Quantity",
"expression": {
"kind": "function",
"name": "div",
"arguments": [
{
"kind": "columnReference",
"column": "ListPrice",
"source": "table"
},
{
"kind": "function",
"name": "nullIf",
"arguments": [
{
"kind": "columnReference",
"column": "Quantity",
"source": "table"
},
{
"kind": "literal",
"type": "integer",
"value": 0
}
]
}
]
}
}
]
},
{
"kind": "distinct"
}
]
}
}
}
IF
This example shows how to create a division with IF. If SUM(Quantity) is less than 50000, then convert it to 50000.
value = SUM(Bonus) / IF(SUM(Quantity) > 50000, SUM(Quantity), 50000)
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Quantity",
"expression": {
"kind": "columnReference",
"column": "Quantity",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"alias": "m1",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "div",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
},
{
"kind": "function",
"name": "caseWhen",
"arguments": [
{
"kind": "function",
"name": "greaterThan",
"arguments": [
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Quantity",
"source": "previousStage"
}
]
},
{
"kind": "literal",
"type": "REAL",
"value": 50000
}
]
},
{
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Quantity",
"source": "previousStage"
}
]
},
{
"kind": "literal",
"type": "REAL",
"value": 50000
}
]
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
]
}
]
}
}
}
CUMCONTRIB
This example shows how to use window function to calculate Cumulative Contribution of Bonus by Channel.
-
Select SUM(Bonus) by Channel.
-
Use window function to calculate the Cumulative of each Channel value and div by Total value:
SUM(SUM(bonus)(n) + SUM(bonus)(n-1)) + … + SUM(bonus)(0)) / TOTAL(SUM(Bonus))
JSON Code
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "SUMBonus",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
},
{
"kind": "selectColumns",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "SUMBonus",
"expression": {
"kind": "columnReference",
"column": "SUMBonus",
"source": "previousStage"
}
},
{
"alias": "CUMCONTRIBBonus",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "div",
"arguments": [
{
"kind": "windowFunction",
"frame": {
"type": "ROWS",
"start": {
"type": "unbounded",
"direction": "PRECEDING"
},
"end": {
"type": "currentRow"
},
"exclusion": "NONE"
},
"partitions": [],
"orders": [
{
"kind": "order",
"direction": "asc_nulls_first",
"expression": {
"kind": "columnReference",
"source": "previousStage",
"column": "Channel"
}
}
],
"function": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"source": "previousStage",
"column": "SUMBonus"
}
]
}
},
{
"kind": "windowFunction",
"frame": {
"type": "ROWS",
"start": {
"type": "unbounded",
"direction": "PRECEDING"
},
"end": {
"type": "unbounded",
"direction": "FOLLOWING"
},
"exclusion": "NONE"
},
"partitions": [],
"function": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"source": "previousStage",
"column": "SUMBonus"
}
]
}
}
]
}
}
]
}
]
}
}
}
Highlighted Structure
Highlighted structure
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"datamartUniqueName": "datamart_transaction",
"currency": "EUR"
},
"columns": [
{
"kind": "selectable",
"alias": "Bonus",
"expression": {
"kind": "columnReference",
"column": "Bonus",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"alias": "SUMBonus",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "Bonus",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
]
},
{
"kind": "selectColumns",
"columns": [
{
"kind": "selectable",
"alias": "Channel",
"expression": {
"kind": "columnReference",
"column": "Channel",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "SUMBonus",
"expression": {
"kind": "columnReference",
"column": "SUMBonus",
"source": "previousStage"
}
},
{
"alias": "CUMCONTRIBBonus",
"kind": "selectable",
"expression": {
"kind": "function",
"name": "div",
"arguments": [
{
"kind": "windowFunction",
"frame": {
"type": "ROWS",
"start": {
"type": "unbounded",
"direction": "PRECEDING"
},
"end": {
"type": "currentRow"
},
"exclusion": "NONE"
},
"partitions": [],
"orders": [
{
"kind": "order",
"direction": "asc_nulls_first",
"expression": {
"kind": "columnReference",
"source": "previousStage",
"column": "Channel"
}
}
],
"function": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"source": "previousStage",
"column": "SUMBonus"
}
]
}
},
{
"kind": "windowFunction",
"frame": {
"type": "ROWS",
"start": {
"type": "unbounded",
"direction": "PRECEDING"
},
"end": {
"type": "unbounded",
"direction": "FOLLOWING"
},
"exclusion": "NONE"
},
"partitions": [],
"function": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"source": "previousStage",
"column": "SUMBonus"
}
]
}
}
]
}
}
]
}
]
}
}
}
PERCENTRANK
This example shows how to use window function to calculate Percent Rank of SUM(ListPrice) for each Country in each Region
QueryAPI Data Transfer Object
JSON
{
"data": {
"query": {
"kind": "pipeline",
"stages": [
{
"kind": "source",
"table": {
"kind": "datamart",
"currency": "EUR",
"datamartUniqueName": "datamart_transaction"
},
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "Country",
"expression": {
"kind": "columnReference",
"column": "Country",
"source": "table"
}
},
{
"kind": "selectable",
"alias": "ListPrice",
"expression": {
"kind": "columnReference",
"column": "ListPrice",
"source": "table"
}
}
]
},
{
"kind": "aggregate",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Country",
"expression": {
"kind": "columnReference",
"column": "Country",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "SUMListPrice",
"expression": {
"kind": "function",
"name": "sum",
"arguments": [
{
"kind": "columnReference",
"column": "ListPrice",
"source": "previousStage"
}
]
}
}
],
"dimensions": [
{
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
},
{
"kind": "columnReference",
"column": "Country",
"source": "previousStage"
}
]
},
{
"kind": "selectColumns",
"columns": [
{
"kind": "selectable",
"alias": "Region",
"expression": {
"kind": "columnReference",
"column": "Region",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "Country",
"expression": {
"kind": "columnReference",
"column": "Country",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "SUMListPrice",
"expression": {
"kind": "columnReference",
"column": "SUMListPrice",
"source": "previousStage"
}
},
{
"kind": "selectable",
"alias": "PERCENTRANKListPrice",
"expression": {
"kind": "windowFunction",
"frame": {
"type": "ROWS",
"start": {
"type": "unbounded",
"direction": "PRECEDING"
},
"end": {
"type": "currentRow"
},
"exclusion": "NONE"
},
"partitions": [
{
"kind": "columnReference",
"source": "previousStage",
"column": "Region"
}
],
"orders": [
{
"kind": "order",
"direction": "asc_nulls_first",
"expression": {
"kind": "columnReference",
"source": "previousStage",
"column": "SUMListPrice"
}
}
],
"function": {
"kind": "function",
"name": "percentRank",
"arguments": []
}
}
}
]
}
]
}
}
}