按列分组的计算模式

Question

+--------+-------+
| client | price |
+--------+-------+
|     54 |    25 |
|    648 |    35 |
|     54 |    10 |
|    648 |     8 |
|     54 |    25 |
|    648 |    35 |
+--------+-------+

上面假设是我的表模式是如何设置的，我想计算每个client最常见的price值，例如。

+--------+-------+
| client | price |
+--------+-------+
|     54 |    25 |
|    648 |    35 |
+--------+-------+

我很难在 MySQL 中完成此操作。我已经在 PHP 中做到了，如下所示：

$clientPrices = $this->database->select('design', [
'clientid',
'price'
]);
$pricesByClients = [];
foreach ($clientPrices as $value) {
$pricesByClients[$value['clientid']][] = $value['price'];
}
foreach ($pricesByClients as $key => $value) {
$priceCount = array_count_values($value);
$mode = array_search(max($priceCount), $priceCount);
$pricesByClients[$key] = $mode;
}
return $pricesByClients;

但是，这很慢，我希望我是否可以使它更有效率或在SQL中做到这一点。

编辑：这是MySQL 5.*而不是8。

Answer 1

不幸的是，MySQL没有内置的函数来计算mode()。

如果您使用的是MySQL 8.0，则可以使用窗口函数和聚合：

select client, price
from (
select client, price, rank() over(partition by client order by count(*) desc) rn
from mytable
group by client, price
) t
where rn = 1

在早期版本中，选项是使用having子句和相关子查询进行筛选

select client, price
from mytable t
group by client, price
having count(*) = (
select count(*)
from mytable t1
where t1.client = t.client
group by t1.price
order by count(*) desc
limit 1
)

Answer 2

对于 MySql 8.0+，您可以使用 row_number(( 窗口函数：

select t.client, t.price
from (
select client, price,
row_number() over (partition by client order by count(*) desc) rn
from tablename
group by client, price
) t
where t.rn = 1;

对于以前的版本，您可以使用相关的子查询：

select distinct t.client, t.price
from tablename t
where (t.client, t.price) = (
select client, price
from tablename
where client = t.client
group by client, price
order by count(*) desc
limit 1
);

请参阅演示。