请参见下面的mysql表的示例部分:
表名:eb\ U票
+-------------------+----------------------+
| ticket_type | time_first_response |
| Standard Traffic | 0:18:14 |
| Standard Traffic | 0:48:06 |
| Miscellaneous | 44:12:23 |
| Feed | 4:48:22 |
| Miscellaneous | 15:33:20 |
| Banners | 21:00:02 |
| Integration | 36:00:02 |
+-------------------+----------------------+
我想输出这样的响应,用不同的值asc计算中值:
+-------------------+----------------------+
| median_group | median |
| Banners | 21:00:02 |
| Feed | 4:48:22 |
| Integration | 36:00:02 |
| Miscellaneous | 32:36:13 |
| Standard Traffic | 0:33:10 |
+-------------------+----------------------+
目前,我通过以下查询实现了这一点:
SET @row_number:=0;
SET @median_group:='';
SELECT
median_group, AVG(time_first_response) AS median
FROM
(SELECT
@row_number:=CASE
WHEN @median_group = ticket_type THEN @row_number + 1
ELSE 1
END AS count_of_group,
@median_group:=ticket_type AS median_group,
ticket_type,
time_first_response,
(SELECT
COUNT(*)
FROM
eb_tickets
WHERE
a.ticket_type = ticket_type) AS total_of_group
FROM
(SELECT
ticket_type, time_first_response
FROM
eb_tickets
ORDER BY ticket_type, time_first_response) AS a) AS b
WHERE
count_of_group BETWEEN total_of_group / 2.0 AND total_of_group / 2.0 +1
GROUP BY median_group
不幸的是,每个记录的查询时间大约为1秒,我查询的记录可能有20个不同的票证类型列值。
我在寻求帮助,看看是否有可能优化我当前的查询,试图使查询执行得更快。
谢谢你的帮助!
1条答案
按热度按时间vfh0ocws1#
你能这样试试吗?
编辑:
起初,我在试验sql时,对列使用了较短的名称-
type
而不是ticket_type
以及子查询counter
留下了错误的列名type
而不是ticket_type