Hi @leandrojmp ,
I have a requirement where I need to apply order on a filed in Elasticsearch which is formed dynamically on the fly and it is not part of original index.
My Sample document looks like
{
"deviceType": "desktop",
"personalizedSignal": "sdf",
"componentPosition": "top",
"count": 10,
"environment": "sdfsdf",
"pageType": "hp",
"eventName": "click",
"analyticsID": "sdfsdf",
"timestamp": 1691589600000
}
{
"deviceType": "desktop",
"personalizedSignal": "sdf",
"componentPosition": "top",
"count": 10,
"environment": "sdfsdf",
"pageType": "hp",
"eventName": "impression",
"analyticsID": "sdfsdf",
"timestamp": 1691589600000
}```
I am running sqllite statement from Superset where I need to display and order by a column which is determined on the fly
Query
GET _sql?format=txt
{
"query": """
SELECT analyticsID,
impression,
click,
(1.0 * click/impression) as CTR
from
(select analyticsID,
SUM(case
when eventName='impression' then (count)
end) impression,
SUM(case
when eventName='click' then (count)
end) click
from "rollup-dco-dummy-index"
group by analyticsID) order by CTR DESC
"""
}
I am getting an error like below
{
"error": {
"root_cause": [
{
"type": "verification_exception",
"reason": """Found 1 problem
line 15:41: Cannot order by non-grouped column [CTR], expected [analyticsID] or an aggregate function"""
}
],
"type": "verification_exception",
"reason": """Found 1 problem
line 15:41: Cannot order by non-grouped column [CTR], expected [analyticsID] or an aggregate function"""
},
"status": 400
}