https://pinot.apache.org/ logo
Join Slack
Powered by
Hi, team. I have a SQL like this. ```select region...
# general
n
Hi, team. I have a SQL like this.
Copy code
select region, clock,
cast(sum(error_count)/sum(request_count)>=0.2 as int) as "critical_status",
cast(sum(error_count)/sum(request_count) between 0.1 and 0.2 as int) as "warning_status"
from system_health where 
clock>=1675239600000 and clock<1675240200000
group by region, clock limit 500000
if I want to define the threshold of “sum(error_count)/sum(request_count)>=0.2” or “sum(error_count)>=5" as “critical_status”, how can I get the correct sql.
Copy code
cast(sum(error_count)/sum(request_count)>=0.2 or sum(error_count)>=5 as int) as "critical_status"
it doesn’t work. Thanks!
r
e
This might be a use case for 
HAVING
clause, where 
having
is used for post-aggregation filtering:
Copy code
select region, clock,
cast(sum(error_count)/sum(request_count) as int) as "critical_status"
from system_health where 
clock>=1675239600000 and clock<1675240200000
group by region, clock

HAVING sum(request_count)>=0.2 or sum(error_count)>=5

limit 500000
I can’t quite tell the intention from the query but you may also find 
FILTER
expressions very useful, which can often be faster than case statements because they can make use of indexes:
Copy code
select region, clock,
cast(sum(error_count)/sum(request_count) FILTER (WHERE request_count >=0.2) as int) as "critical_status",
cast(sum(error_count)/sum(request_count) FILTER (WHERE request_count between 0.1 and 0.2) as int) as "warning_status"
from system_health where 
clock>=1675239600000 and clock<1675240200000
group by region, clock limit 500000
FILTER
expressions with 
GROUP BY
is only supported in Pinot version ≥0.12
r
The intent appears to return the status of the error rate by region and clock
e
Sorry I meant that I couldn’t quite tell whether the intention of the filtering shown in the OP’s query was to filter before or after aggregations
r
It's not really filtering, it's a threshold to define multiple levels of status (ie >= 0.2 is critical, and >= 0.1 is warning)
The only filtering is based on the clock, which should by used in the group by
Also case won't work because when statements can't contain aggregation function
e
Ya that’s where HAVING might work as it operates after aggregations. It may not fit the use case but it’s the only post-aggregation predicate that I myself am aware of
👍 1
r
Copy code
select region, sum(error_count) / sum(request_count) as error_rate
from system_health
where clock >= 1675239600000 and clock < 1675240200000
group by region
having error_rate >= 0.1
👍 1
n
@robert zych Case-When statement will cause error as “Aggregation functions inside WHEN Clause is not supported”.
Copy code
(case when (sum(error_count)/sum(request_count)>=0.2 or sum(error_count)>=5) then 1 else 0 end) as "critical_status"
@Evan Galpin my intention is to monitor the system health. If the error_rate “sum(error_count) / sum(request_count)” is larger than 0.2, or the sum of error “sum(error_count)” is larger than 5, the system health status is critical.
r
@Nickel Fang yes, due to this current limitation of case statements I suggested an alternative using the having clause
Open in Slack
PreviousNext
Powered by