多布尔列出行数据表的出行模式统计实现疑问

以下是针对trips表的统计查询，可一次性获取所有需求的出行模式数据：

SELECT
  -- 单一出行方式统计
  COUNT(CASE WHEN foot = true AND bike = false AND bus = false AND car = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 仅步行行程数,
  COUNT(CASE WHEN bus = true AND foot = false AND bike = false AND car = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 仅公交行程数,
  COUNT(CASE WHEN car = true AND foot = false AND bike = false AND bus = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 仅驾车行程数,
  -- 组合出行方式统计
  COUNT(CASE WHEN foot = true AND car = true AND bike = false AND bus = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 步行_驾车组合行程数,
  COUNT(CASE WHEN foot = true AND bus = true AND bike = false AND car = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 步行_公交组合行程数,
  -- 无任何出行方式的行程数
  COUNT(CASE WHEN foot = false AND bike = false AND bus = false AND car = false 
              AND metro = false AND motorcycle = false AND train = false AND other = false 
            THEN 1 END) AS 无出行方式行程数
FROM trips;

统计逻辑说明 #

• 单一出行方式：判断对应出行方式为true，其余所有出行方式字段均为false
• 组合出行方式：判断指定的两种出行方式为true，其余所有字段均为false
• 无出行方式：所有出行方式字段均为false

针对提供的示例数据，执行此查询会得到如下结果：

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