mysqlgroup-bymedian
Is there a way to optimize a group by median query?
I wrote a query that finds the median of values for each month. Doing this was difficult enough because MySQL doesn't have a built in median function so I really had to think outside the box with my intermediate SQL skills. But now the problem is that it takes really long (1 or 2 mins sometimes) to run the query. Is there a way to optimize this query? Or perhaps I should write a python script that finds the median and pushes it to the database using a connector?
Here is the query:
SET @row_num_pos := 0;
SET @median_group_pos := '';
SET @row_num_neg := 0;
SET @median_group_neg := '';
SELECT
p.month_num AS 'month_num',
CASE
WHEN p.month_num = 1 THEN 'Jan'
WHEN p.month_num = 2 THEN 'Feb'
WHEN p.month_num = 3 THEN 'Mar'
WHEN p.month_num = 4 THEN 'Apr'
WHEN p.month_num = 5 THEN 'May'
WHEN p.month_num = 6 THEN 'Jun'
WHEN p.month_num = 7 THEN 'Jul'
WHEN p.month_num = 8 THEN 'Aug'
WHEN p.month_num = 9 THEN 'Sep'
WHEN p.month_num = 10 THEN 'Oct'
WHEN p.month_num = 11 THEN 'Nov'
WHEN p.month_num = 12 THEN 'Dec'
END AS 'Timeline',
p.ck_pos_median AS 'CK+ Median',
n.ck_neg_median AS 'CK- Median'
FROM
(SELECT
s.median_month_pos AS 'month_num',
ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
FROM
(SELECT
@row_num_pos:=CASE
WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
ELSE 1
END AS 'count_of_group',
@median_group_pos:=q.month_num AS 'median_month_pos',
q.month_num,
q.ck_pos,
(SELECT
COUNT(*)
FROM
Biocept_DB.result_management_report
WHERE
ck_pos IS NOT NULL
AND MONTH(order_date) = q.month_num) AS total_month
FROM
(SELECT
MONTH(order_date) AS 'month_num', ck_pos
FROM
Biocept_DB.result_management_report
WHERE
ck_pos IS NOT NULL
ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
WHERE
s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
GROUP BY s.median_month_pos) AS p
JOIN
(SELECT
s.median_month_neg AS 'month_num',
ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
FROM
(SELECT
@row_num_neg:=CASE
WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
ELSE 1
END AS 'count_of_group',
@median_group_neg:=q.month_num AS 'median_month_neg',
q.month_num,
q.ck_neg,
(SELECT
COUNT(*)
FROM
Biocept_DB.result_management_report
WHERE
ck_neg IS NOT NULL
AND MONTH(order_date) = q.month_num) AS total_month
FROM
(SELECT
MONTH(order_date) AS 'month_num', ck_neg
FROM
Biocept_DB.result_management_report
WHERE
ck_neg IS NOT NULL
ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
WHERE
s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
ORDER BY p.month_num;
SET @row_num_pos := NULL;
SET @median_group_pos := NULL;
SET @row_num_neg := NULL;
SET @median_group_neg := NULL;
Here is the table it generates:
Solution
I have modified your query a little bit. I hope that the calculation is correct. With you sample date the results are the same.
I my environment your query take 6.22 seconds and my only 20 ms. so it looks 300 times faster.
please test my query and let me know if it works for you. is the speed not good we can optimize a little more with virtual columns.
Please don't forget to set group_concat_max_len to a good value for you.
SET SESSION group_concat_max_len = 1000000;
Query
SELECT r.Timeline AS `month_number`
, SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
AS Timeline
, ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
) / 2 AS 'ck_pos_median'
, ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
) / 2 AS 'ck_neg_median'
FROM (
SELECT MONTH(`order_date`) AS 'Timeline'
, SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
, GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
, SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
, GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
FROM result_management_report
where (ck_pos is not null or ck_neg is not null) = 1
GROUP BY Timeline
) r;
Table definition
Sample new query
mysql> SELECT r.Timeline AS `month_number`
-> , SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
-> AS Timeline
-> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
-> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
-> ) / 2 AS 'ck_pos_median'
-> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
-> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
-> ) / 2 AS 'ck_neg_median'
-> FROM (
-> SELECT MONTH(`order_date`) AS 'Timeline'
-> , SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
-> , GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
-> , SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
-> , GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
-> FROM result_management_report
-> where (ck_pos is not null or ck_neg is not null) = 1
-> GROUP BY Timeline
-> ) r;
+--------------+----------+---------------+---------------+
| month_number | Timeline | ck_pos_median | ck_neg_median |
+--------------+----------+---------------+---------------+
| 1 | Jan | 2 | 2 |
| 2 | Feb | 2 | 3 |
| 3 | Mar | 2 | 3 |
| 4 | Apr | 4 | 4 |
| 5 | May | 2 | 3 |
| 6 | Jun | 3 | 3 |
| 7 | Jul | 4 | 4 |
| 8 | Aug | 3 | 7 |
| 9 | Sep | 4 | 12 |
| 10 | Oct | 5 | 8 |
| 11 | Nov | 4 | 9 |
| 12 | Dec | 2 | 12 |
+--------------+----------+---------------+---------------+
12 rows in set (0.02 sec)
Sample old query
mysql>
mysql> SET @row_num_pos := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @median_group_pos := '';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @row_num_neg := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @median_group_neg := '';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT
-> p.month_num AS 'month_num',
-> CASE
-> WHEN p.month_num = 1 THEN 'Jan'
-> WHEN p.month_num = 2 THEN 'Feb'
-> WHEN p.month_num = 3 THEN 'Mar'
-> WHEN p.month_num = 4 THEN 'Apr'
-> WHEN p.month_num = 5 THEN 'May'
-> WHEN p.month_num = 6 THEN 'Jun'
-> WHEN p.month_num = 7 THEN 'Jul'
-> WHEN p.month_num = 8 THEN 'Aug'
-> WHEN p.month_num = 9 THEN 'Sep'
-> WHEN p.month_num = 10 THEN 'Oct'
-> WHEN p.month_num = 11 THEN 'Nov'
-> WHEN p.month_num = 12 THEN 'Dec'
-> END AS 'Timeline',
-> p.ck_pos_median AS 'CK+ Median',
-> n.ck_neg_median AS 'CK- Median'
-> FROM
-> (SELECT
-> s.median_month_pos AS 'month_num',
-> ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
-> FROM
-> (SELECT
-> @row_num_pos:=CASE
-> WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
-> ELSE 1
-> END AS 'count_of_group',
-> @median_group_pos:=q.month_num AS 'median_month_pos',
-> q.month_num,
-> q.ck_pos,
-> (SELECT
-> COUNT(*)
-> FROM
-> result_management_report
-> WHERE
-> ck_pos IS NOT NULL
-> AND MONTH(order_date) = q.month_num) AS total_month
-> FROM
-> (SELECT
-> MONTH(order_date) AS 'month_num', ck_pos
-> FROM
-> result_management_report
-> WHERE
-> ck_pos IS NOT NULL
-> ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
-> WHERE
-> s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
-> GROUP BY s.median_month_pos) AS p
-> JOIN
-> (SELECT
-> s.median_month_neg AS 'month_num',
-> ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
-> FROM
-> (SELECT
-> @row_num_neg:=CASE
-> WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
-> ELSE 1
-> END AS 'count_of_group',
-> @median_group_neg:=q.month_num AS 'median_month_neg',
-> q.month_num,
-> q.ck_neg,
-> (SELECT
-> COUNT(*)
-> FROM
-> result_management_report
-> WHERE
-> ck_neg IS NOT NULL
-> AND MONTH(order_date) = q.month_num) AS total_month
-> FROM
-> (SELECT
-> MONTH(order_date) AS 'month_num', ck_neg
-> FROM
-> result_management_report
-> WHERE
-> ck_neg IS NOT NULL
-> ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
-> WHERE
-> s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
-> GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
-> ORDER BY p.month_num;
+-----------+----------+------------+------------+
| month_num | Timeline | CK+ Median | CK- Median |
+-----------+----------+------------+------------+
| 1 | Jan | 2.0 | 2.0 |
| 2 | Feb | 2.0 | 3.0 |
| 3 | Mar | 2.0 | 3.0 |
| 4 | Apr | 4.0 | 4.0 |
| 5 | May | 2.0 | 3.0 |
| 6 | Jun | 3.0 | 3.0 |
| 7 | Jul | 4.0 | 4.0 |
| 8 | Aug | 3.0 | 7.0 |
| 9 | Sep | 4.0 | 12.0 |
| 10 | Oct | 5.0 | 8.0 |
| 11 | Nov | 4.0 | 9.0 |
| 12 | Dec | 2.0 | 12.0 |
+-----------+----------+------------+------------+
12 rows in set (6.22 sec)
mysql>
- Will modifying the primary key type using ALTER in MySQL reset the auto-increment value to 0?
- Auto-increment is not resetting in MySQL
- Php update database if checkbox is not checked
- Retrieve 10 records from MySQL?
- how to use str_to_date (MySQL) to parse two-digit year correctly
- sum column value(s) if previous values has same value with current value from the same table in mysql
- JDBC connection without database definition
- Checking connection to MySQL (Java)
- WARN: Establishing SSL connection without server's identity verification is not recommended
- how to solve django.db.utils.NotSupportedError in django
- How to make an Inner Join in django?
- Can I order by a conditional combination of fields in MySQL?
- Select from 2 tables in the last 30 days, return 0 if null/not exist
- Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)
- Access denied for user 'root@localhost' (using password:NO)
- Watching a table for change in MySQL?
- table is specified twice both as a target for INSERT and as separate source of data
- Python Mysql, "commands out of sync; you can't run this command now"
- Does Laravel's "soft_delete" need index on MySQL?
- How do I access a field from the main query inside a sub query in mySQL?
- Not getting unpaid or partially paid records some can help me?
- Warning: The post-install step did not complete successfully when trying to install mysql using brew in Mac OS High Sierra
- MySQL Entity Attribute Value table structure proposal
- CodeIgniter SELECT query on table with JOIN and WHERE IN a subquery
- is there a MYSQL tables schema for storing raw material, intermediate and final products?
- MySQL: Total GROUP BY WITH ROLLUP curiosity
- Problem with pip install mariadb - mariadb_config not found
- nodejs mysql memory leak on large volume of high frequency queries
- `MODIFY COLUMN` vs `CHANGE COLUMN`
- Displaying BLOB image from Mysql database into dynamic div in html