SQL分组查询如何显示空分箱的COUNT=0结果？

DevOps 小满

2026-8-1

问题

在对数据进行分箱分析分布时，部分分箱无数据，当前查询仅返回含数据的分箱，希望同时显示空分箱并将计数设为0。尝试创建包含所有分箱的表后与数据查询结果做LEFT JOIN，但仍无法显示空分箱。以下是使用的SQL代码：

WITH tb AS 
(
  SELECT 'A [+0 min-1 min]' AS bins UNION ALL
  SELECT 'B [+1 min-113 min]'       UNION ALL
  SELECT 'C [+113 min-223 min]'     UNION ALL
  SELECT 'D [+223 min-335 min]'     UNION ALL
  SELECT 'E [+335 min-447 min]'     UNION ALL
  SELECT 'F [+447 min-559 min]'     UNION ALL
  SELECT 'G [+559 min-671 min]'     UNION ALL
  SELECT 'H [+671 min-783 min]'     UNION ALL
  SELECT 'I [+783 min-895 min]'     UNION ALL
  SELECT 'J [+895 min-1007 min]'    UNION ALL
  SELECT 'K [+1007 min-1119 min]'   UNION ALL
  SELECT 'L [+1119 min-1231 min]'   UNION ALL
  SELECT 'M [+1231 min-1343 min]'   UNION ALL
  SELECT 'N [+1343 min-1440 min]'   UNION ALL
  SELECT 'O [+1140 min]'
), aa AS 
(
  SELECT CASE
         WHEN ride_length BETWEEN '00:00:00' AND '00:01:00' THEN
          'A [+0 min-1 min]'
         WHEN ride_length BETWEEN '00:01:01' AND '01:53:00' THEN
          'B [+1 min-113 min]'
         WHEN ride_length BETWEEN '01:53:01' AND '03:43:00' THEN
          'C [+113 min-223 min]'
         WHEN ride_length BETWEEN '03:43:01' AND '05:35:00' THEN
          'D [+223 min-335 min]'
         WHEN ride_length BETWEEN '05:35:01' AND '07:27:00' THEN
          'E [+335 min-447 min]'
         WHEN ride_length BETWEEN '07:27:01' AND '09:19:00' THEN
          'F [+447 min-559 min]'
         WHEN ride_length BETWEEN '09:19:01' AND '11:11:00' THEN
          'G [+559 min-671 min]'
         WHEN ride_length BETWEEN '11:11:01' AND '13:03:00' THEN
          'H [+671 min-783 min]'
         WHEN ride_length BETWEEN '13:03:01' AND '14:55:00' THEN
          'I [+783 min-895 min]'
         WHEN ride_length BETWEEN '14:55:01' AND '16:47:00' THEN
          'J [+895 min-1007 min]'
         WHEN ride_length BETWEEN '16:47:01' AND '18:39:00' THEN
          'K [+1007 min-1119 min]'
         WHEN ride_length BETWEEN '18:39:01' AND '20:31:00' THEN
          'L [+1119 min-1231 min]'
         WHEN ride_length BETWEEN '20:31:01' AND '22:23:00' THEN
          'M [+1231 min-1343 min]'
         WHEN ride_length BETWEEN '22:33:01' AND '24:00:00' THEN
          'N [+1343 min-1440 min]'
         ELSE
          'O [+1140 min]'
         END AS bins,
         member_casual,
         rideable_type
    FROM (SELECT TIMEDIFF(ended_at, started_at) AS ride_length,
                 member_casual,
                 rideable_type
            FROM cyclistic) a
)
SELECT tb.bins,
       aa.member_casual,
       aa.rideable_type,
       IFNULL(COUNT(member_casual), 0)
  FROM tb
  LEFT JOIN aa
    ON tb.bins = aa.bins
 GROUP BY tb.bins, aa.member_casual, aa.rideable_type;

得到的输出仅包含有数据的分箱，空分箱未显示：

+------------------------+---------------+---------------+--------------------------------+
| bins                   | member_casual | rideable_type | IFNULL(COUNT(member_casual),0) |
+------------------------+---------------+---------------+--------------------------------+
       | classic_bike  |                          25999 |
       | electric_bike |                          47882 |
       | classic_bike  |                          12892 |
       | electric_bike |                          33882 |
       | electric_bike |                        1213471 |
       | electric_bike |                        1584591 |
       | classic_bike  |                        1679971 |
       | classic_bike  |                         857810 |
       | docked_bike   |                         158858 |
       | classic_bike  |                          15025 |
       | electric_bike |                           2426 |
       | docked_bike   |                          11824 |
       | classic_bike  |                           1661 |
       | electric_bike |                            635 |
       | classic_bike  |                            400 |
       | electric_bike |                            184 |
       | classic_bike  |                             84 |
       | classic_bike  |                             38 |
       | docked_bike   |                            195 |
       | docked_bike   |                           1554 |
       | classic_bike  |                           1213 |
       | electric_bike |                            323 |
       | docked_bike   |                            139 |
       | classic_bike  |                             25 |
       | classic_bike  |                             42 |
       | docked_bike   |                            263 |
       | classic_bike  |                            111 |
       | classic_bike  |                            213 |
       | docked_bike   |                            192 |
       | classic_bike  |                            159 |
       | classic_bike  |                            113 |
       | electric_bike |                           5263 |
       | classic_bike  |                            176 |
       | classic_bike  |                            400 |
       | electric_bike |                             64 |
       | docked_bike   |                            443 |
       | docked_bike   |                            169 |
       | classic_bike  |                            131 |
       | classic_bike  |                            194 |
       | classic_bike  |                            275 |
       | docked_bike   |                            207 |
       | classic_bike  |                            129 |
       | electric_bike |                              1 |
       | docked_bike   |                            152 |
       | classic_bike  |                             80 |
       | classic_bike  |                            126 |
       | classic_bike  |                           2606 |
       | docked_bike   |                           1507 |
       | classic_bike  |                            716 |
       | docked_bike   |                            201 |
       | classic_bike  |                             94 |
       | classic_bike  |                             47 |
       | docked_bike   |                           1528 |
       | electric_bike |                            168 |
       | docked_bike   |                            229 |
       | classic_bike  |                            137 |
       | classic_bike  |                            287 |
       | electric_bike |                             51 |
+------------------------+---------------+---------------+--------------------------------+

尝试COALESCE后结果一致，请问如何修改查询以显示空分箱并将计数设为0？

解决方案

问题出在分组维度的缺失：原查询直接将分箱表与数据LEFT JOIN后，按tb.bins, aa.member_casual, aa.rideable_type分组，但空分箱没有对应的member_casual和rideable_type数据，无法生成对应行。需要先生成所有分箱 + 所有维度组合的笛卡尔积，再与数据关联，确保每个分箱的所有维度组合都能被统计到。

修改后的SQL如下：

WITH tb AS 
(
  SELECT 'A [+0 min-1 min]' AS bins UNION ALL
  SELECT 'B [+1 min-113 min]'       UNION ALL
  SELECT 'C [+113 min-223 min]'     UNION ALL
  SELECT 'D [+223 min-335 min]'     UNION ALL
  SELECT 'E [+335 min-447 min]'     UNION ALL
  SELECT 'F [+447 min-559 min]'     UNION ALL
  SELECT 'G [+559 min-671 min]'     UNION ALL
  SELECT 'H [+671 min-783 min]'     UNION ALL
  SELECT 'I [+783 min-895 min]'     UNION ALL
  SELECT 'J [+895 min-1007 min]'    UNION ALL
  SELECT 'K [+1007 min-1119 min]'   UNION ALL
  SELECT 'L [+1119 min-1231 min]'   UNION ALL
  SELECT 'M [+1231 min-1343 min]'   UNION ALL
  SELECT 'N [+1343 min-1440 min]'   UNION ALL
  SELECT 'O [+1140 min]'
),
-- 生成member_casual和rideable_type的所有唯一组合
dimensions AS (
  SELECT DISTINCT member_casual, rideable_type
  FROM cyclistic
),
-- 生成所有分箱与所有维度组合的笛卡尔积
all_combinations AS (
  SELECT tb.bins, dimensions.member_casual, dimensions.rideable_type
  FROM tb
  CROSS JOIN dimensions
),
aa AS 
(
  SELECT CASE
         WHEN ride_length BETWEEN '00:00:00' AND '00:01:00' THEN
          'A [+0 min-1 min]'
         WHEN ride_length BETWEEN '00:01:01' AND '01:53:00' THEN
          'B [+1 min-113 min]'
         WHEN ride_length BETWEEN '01:53:01' AND '03:43:00' THEN
          'C [+113 min-223 min]'
         WHEN ride_length BETWEEN '03:43:01' AND '05:35:00' THEN
          'D [+223 min-335 min]'
         WHEN ride_length BETWEEN '05:35:01' AND '07:27:00' THEN
          'E [+335 min-447 min]'
         WHEN ride_length BETWEEN '07:27:01' AND '09:19:00' THEN
          'F [+447 min-559 min]'
         WHEN ride_length BETWEEN '09:19:01' AND '11:11:00' THEN
          'G [+559 min-671 min]'
         WHEN ride_length BETWEEN '11:11:01' AND '13:03:00' THEN
          'H [+671 min-783 min]'
         WHEN ride_length BETWEEN '13:03:01' AND '14:55:00' THEN
          'I [+783 min-895 min]'
         WHEN ride_length BETWEEN '14:55:01' AND '16:47:00' THEN
          'J [+895 min-1007 min]'
         WHEN ride_length BETWEEN '16:47:01' AND '18:39:00' THEN
          'K [+1007 min-1119 min]'
         WHEN ride_length BETWEEN '18:39:01' AND '20:31:00' THEN
          'L [+1119 min-1231 min]'
         WHEN ride_length BETWEEN '20:31:01' AND '22:23:00' THEN
          'M [+1231 min-1343 min]'
         WHEN ride_length BETWEEN '22:33:01' AND '24:00:00' THEN
          'N [+1343 min-1440 min]'
         ELSE
          'O [+1140 min]'
         END AS bins,
         member_casual,
         rideable_type
    FROM (SELECT TIMEDIFF(ended_at, started_at) AS ride_length,
                 member_casual,
                 rideable_type
            FROM cyclistic) a
)
SELECT all_combinations.bins,
       all_combinations.member_casual,
       all_combinations.rideable_type,
       COUNT(aa.bins) AS count
  FROM all_combinations
  LEFT JOIN aa
    ON all_combinations.bins = aa.bins
    AND all_combinations.member_casual = aa.member_casual
    AND all_combinations.rideable_type = aa.rideable_type
 GROUP BY all_combinations.bins, all_combinations.member_casual, all_combinations.rideable_type
 ORDER BY all_combinations.bins, all_combinations.member_casual, all_combinations.rideable_type;

关键修改点：

生成维度组合表：通过dimensions CTE获取member_casual和rideable_type的所有唯一值组合。
生成全量组合：用CROSS JOIN将分箱表tb与维度表dimensions关联，得到所有分箱+维度的组合，确保每个分箱的所有可能维度组合都存在。
关联数据并统计：将全量组合表与数据aa做LEFT JOIN，关联条件包含分箱、member_casual和rideable_type，最后用COUNT(aa.bins)统计数据（无数据时返回0）。
排序优化：添加ORDER BY确保结果按分箱和维度有序展示。

另外注意原SQL中O [+1140 min]的分箱描述可能存在笔误（对应条件是ELSE，而N分箱是到1440min，这里可能是+1440 min），可根据实际需求修正。

内容的提问来源于stack exchange，提问作者Maximiliano Pimentel

SQL分组查询如何显示空分箱的COUNT=0结果？

关键修改点： #

关键修改点：