Showing my current issue using mysql median query with grafana 6.7.3.
This issue exists every time we change the time range for data acquisition. Refer to the image below.
This however shows the correct data, but we have some manual work to do in order to get the expected data.
First, we have to edit the graph. After transitioning to the details graph page, the query used is displayed below.
We have to click on the query and click outside of the query just to tell grafana to re-query (fetch again).
And this solves the issue with the data but we wanted it to work without performing the above mentioned steps.
I changed the visualization from Graph to table and it produces the same result. However we can see in the table that every time we change the time range,
Grafana gets only 1 row of the fetched data.
*Sorry cannot upload more than 2 images.
Again performing the same procedure we did solving the issue with graph as visualization, the correct data, number of rows was retrieved.
*Sorry cannot upload more than 2 images.
Refer below for the query used to get the median data.
Query to get median data grouped by day:
SELECT
DATE(period) as time,
AVG(middle_values) AS value,
'performance_score' as metric
FROM (
SELECT t1.performance_score AS 'middle_values', t1.row_num, t1.period, t2.count
FROM (
SELECT
@last_period:=@period AS 'last_period',
@period:=DATE_FORMAT(exec_datetime, '%Y-%m-%d') AS 'period',
IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,
x.FID
FROM ps_crux_data_v6 AS x, (SELECT @row:=0) AS r
WHERE x.performance_score is not NULL
AND $__timeFilter(exec_datetime) AND url_id IN (select url_id from ps_url_list where service_id=$Service and if($Page>0,page_id=$Page,true)) and device_type=$DeviceType
-- where criteria goes here
ORDER BY DATE_FORMAT(exec_datetime, '%Y-%m-%d'), x.FID
) AS t1
LEFT JOIN (
SELECT COUNT(*) as 'count', DATE_FORMAT(exec_datetime, '%Y-%m-%d') AS 'period'
FROM ps_crux_data_v6 x
WHERE x.performance_score is not NULL
AND $__timeFilter(exec_datetime) AND url_id IN (select url_id from ps_url_list where service_id=$Service and if($Page>0,page_id=$Page,true)) and device_type=$DeviceType
-- same where criteria goes here
GROUP BY DATE_FORMAT(exec_datetime, '%Y-%m-%d')
) AS t2
ON t1.period = t2.period
) AS t3
WHERE
row_num >= (count/2)
AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;
However using the predefined functions in mysql specifically avg() since this was the only mean function we used in this graph as a substitute, there was no issue.
With the above stated issue, we considered using another database with predefined median function.
This may be an issue with grafana (6.7.3) or this may be on our query.
Update 2020/06/19 : Also tested with grafana v7.0.3 and issue still persists.