mysql - 如果 COUNT(*) > X 则分组，否则不分组

Question

我试图从我的数据库中返回 map 项目列表，仅当组数大于 4 时才对项目进行分组，否则我不希望对项目进行分组。

我的项目旨在返回设定区域内的所有条目，我将使用分组将该区域分解为网格。如果网格中的每个单元格都有太多结果，那么我将显示一个带有组计数的组标记，而不是单个条目标记。

我当前的查询用于确定是否有按 COUNT(*) 分组的项目

SELECT *, COUNT(*) as groupCount, floor(longitude/0.0007) AS groupLong, floor(latitude/0.0007) AS groupLat 
FROM items 
WHERE longitude>=151.1 
    AND longitude<=151.2 
    AND latitude>=-33.9 
    AND latitude<=-33.8 
GROUP BY floor(longitude/0.0007), floor(latitude/0.0007)

我想做的是仅在组数 > 4 时对项目进行分组，并且组中的项目 <=4 将作为未分组的项目返回。

我知道我可以使用 HAVING COUNT(*)>4 只返回 5 组及以上的组，但是我该怎么做才能返回 4 组中未分组的项目在？

我很乐意进行两次查询以获得结果，但如果有一种方法可以一次完成，那就太好了!

Answer 1

这是我的解决方案的 SQL Fiddle:http://sqlfiddle.com/#!8/e40ba/1

想法是首先弄清楚组是什么，这是在子查询grouping 中完成的。然后我们将这些组连接到原始表，但我们使用左外部连接，因此任何不在组中的值都将在分组列中具有空值。最后，我们使用 AVG 为该组提供一个代表性坐标。对于未分组的值，这将是坐标本身，这很好。

我建议不要在不进行一些测试和基准测试的情况下实际使用它。如果 items 很大，该连接可能会很糟糕。我真的只是想找到一种在单个查询中执行此操作的方法。正如我在评论中所说，执行此操作的正确方法是使用窗口函数，但 MySQL 没有这些函数。

SELECT AVG(longitude) AS longitude
     , AVG(latitude) AS latitude
     , COUNT(*) AS count

FROM items
    LEFT OUTER JOIN
        ( SELECT COUNT(*) AS group_count
               , FLOOR(longitude/0.0007) AS group_longitude
               , FLOOR(latitude/0.0007) AS group_latitude
          FROM items
          -- Repeat the filter to avoid computing unnecessary groups
          WHERE longitude >= 151.1
            AND longitude <= 151.2
            AND latitude >= -33.9
            AND latitude <= -33.8
          GROUP BY group_longitude, group_latitude
          HAVING group_count > 4
        ) AS grouping
    -- Match each row up with its group
    ON FLOOR(longitude/0.0007) = group_longitude
    AND FLOOR(latitude/0.0007) = group_latitude

WHERE longitude >= 151.1
  AND longitude <= 151.2
  AND latitude >= -33.9
  AND latitude <= -33.8

GROUP BY COALESCE(group_longitude, id)
       , COALESCE(group_latitude, id)