mysql - 获取具有多种组合的行数

Question

我需要 MySQL 查询方面的帮助。我们有一个数据库(约 10K 行)，我已将其简化为这个问题。

我们有 7 名卡车司机，每天会访问 9 个可能地点中的 3 个地点。他们每天都会访问 3 个不同的地点，并且每天他们可以访问与前一天不同的地点。以下是代表性表格:

Table: Drivers

id name
10 Abe
11 Bob
12 Cal
13 Deb
14 Eve
15 Fab
16 Guy

Table: Locations

id day address driver.id
1   1    Oak   10
2   1    Elm   10
3   1    4th   10
4   1    Oak   16
5   1    4th   16
6   1    Toy   16
7   1    Toy   11
8   1    5th   11
9   1    Law   11
10   2    Oak   11
11   2    4th   11
12   2    Toy   11   
.........

我们有全年的数据，我们需要找出一年内每条“路线”被访问的次数，从最多到最少排序。

根据我的高中数学，我相信有 9!/(6!3!) 种路线组合，即总共 84 种。我想做这样的事情:

获取路线地址 = 'Oak' 和 'Elm' 和 '4th' 的路线计数

然后再次运行其中路线地址 = 'Oak' 和 'Elm' 和 '5th'

然后一次又一次，等等。然后对路线计数进行降序排序。但我不想做84次。有办法做到这一点吗？

Answer 1

我正在查看GROUP_CONCAT

 SELECT t.day 
      , t.driver
      , GROUP_CONCAT(t.address ORDER BY t.address)
  FROM mytable t
 GROUP
    BY t.day
     , t.driver

这里不清楚的是，路线上的站点是否有顺序。顺序有什么区别吗？我们如何判断顺序是什么？要以不同的方式询问，请考虑以下两条路线:

('Oak','Elm','4th') 和 ('Elm','4th','Oak')

这些是等价的(因为它们是同一组停靠点)还是不同的(因为它们的顺序不同)？

如果路线上的停靠点顺序使其与具有相同停靠点(顺序不同)的其他路线区分开来，则将 ORDER BY t.address 替换为 ORDER BY t.id 或任何给出停止顺序的表达式。

关于 GROUP_CONCAT 的一些注意事项:最大长度受到 group_concat_max_len 和 max_allowed_pa​​cket 变量设置的限制。另外，用作分隔符的逗号...如果我们组合包含逗号的字符串，那么在我们的结果中，我们无法可靠地区分 'a,b'+'c' 和 'a'+'b,c'

<小时/>

我们可以将该查询用作内联 View ，并获取具有相同路由的行数:

 SELECT c.route
      , COUNT(*) AS cnt
   FROM ( SELECT t.day 
               , t.driver
               , GROUP_CONCAT(t.address ORDER BY t.address) AS route
            FROM mytable t
           GROUP
              BY t.day
               , t.driver
        ) c
  GROUP
     BY c.route
  ORDER
     BY cnt DESC