MySQL:如何将表列聚合成一个列表并计数? [英] MySQL : How to aggregate table column into a list and count?

问题描述

我在 MySql 中有一个表，例如:

I have a table in MySql like :

URL       Browser
A         Chrome
B         Chrome 
C         Firefox
A         Chrome
A         Firefox
A         Opera
A         Chrome
B         Chrome
B         Firefox
C         Tor

URL 列的数据范围很广，但浏览器列的数据集有限.我想在 URL 列上聚合并按降序获取列表中每个浏览器的最高计数，例如:

The URL column has a wide range of data, but the Browser column has a limited set. I want to aggregate on the URL column and get the top counts for each browser in a list in descending order, like :

URL      FrequentlyUsedBrowser 
A        [(Chrome,3),(Firefox,1),(Opera,1)]
B        [(Chrome,2),(Firefox,1)]
C        [(Chrome,1),(Tor,1)] 

我一直在为它编写 SQL 以使用窗口分区来计算每个浏览器的一个条目，但无法将其放入列表中.

I have been writing SQL for it to use window partition to get the count as one entry for each browser, but not been able to get it into a list.

推荐答案

这是您的查询.这可以使用 group_concat()

Here's your query. This is achievable using group_concat()

select url
    , concat('[', group_concat('(', browser, ',', ct, ')'), ']') as FrequentlyUsedBrowser 
    from 
    (select count(1) ct, url, browser from test
        group by url, browser) t1
group by url

参见 dbfiddle

这篇关于MySQL:如何将表列聚合成一个列表并计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！