This topic describes cases of log data analysis.

Case list

Trigger an alert when the error rate exceeds 40% over the last five minutes

Calculate the percentage of error 500 every minute. An alert is triggered when the error rate exceeds 40% over the last five minutes. 
status:500 | select __topic__, max_by(error_count,window_time)/1.0/sum(error_count) as error_ratio, sum(error_count) as total_error  from (
select __topic__, count(*) as error_count , __time__ - __time__ % 300  as window_time  from log group by __topic__, window_time
) 
group by __topic__  having  max_by(error_count,window_time)/1.0/sum(error_count)   > 0.4  and sum(error_count) > 500 order by total_error desc limit 100

Count traffic and configure alerts

Count traffic every minute. An alert is triggered when traffic plunges. Traffic counted in the last minute does not cover a full minute. Therefore, divide the statistical value by using (max(time) - min(time)) for normalization to count the average traffic per minute. 
* | SELECT SUM(inflow) / (max(__time__) - min(__time__))  as inflow_per_minute, date_trunc('minute',__time__)  as minute group by minute

Calculate the average latency of each data interval

Calculate the average latency of each bucket set by data interval.

* | select avg(latency) as latency , case when originSize < 5000 then 's1' when originSize < 20000 then 's2' when originSize < 500000 then 's3' when originSize < 100000000 then 's4' else 's5' end as os group by os

Return the percentages of different results

Return the count results of different departments and the percentages of these results. This query combines the subquery and window functions. sum(c) over() indicates the sum of values in all rows. 

* |  select   department, c*1.0/ sum(c) over ()  from(select  count(1) as c, department   from log group by department)

Count the number of logs that meet the query condition

URLs must be by counted by characteristics. In this situation, you can use the CASE WHEN syntax and the simpler count_if syntax. 
* | select  count_if(uri like '%login') as login_num, count_if(uri like '%register') as register_num, date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as time  group by time order by time limit 100