如何基于用户调查响应表创建含布尔型列的宽表？

源表结构与数据 #

CREATE TABLE response (response_id INT, utime INT, 
                     question_id INT, response VARCHAR(500))

INSERT INTO response (response_id,  utime, question_id, response)
VALUES (5,1459542506,11,'1'),(5,1459542506,12,'0'),(5,1459542506,13,'0'),
  (5,1459542506,14,'0'),(5,1459542506,15,'0'),(7,1458210291,11,'0'),(7,1458210291,12,'1'),
  (7,1458210291,13,'1'),(7,1458210291,14,'0'),(7,1458216077,15,'1'),(10,1458212391,11,'1'),
  (10,1458212391,12,'0'),(10,1458212391,13,'1'),(10,1458212391,14,'0'),(10,1458212391,15,'0')

表与问题说明 #

• 该表存储用户调查结果：response_id为用户ID，question_id为问题编号，response为用户回答（取值为'0'或'1'，类型为VARCHAR）。
• 问题编号对应含义：

  question_id	对应行为
  11	坐着（sitting）
  12	走路（walking）
  13	站着（standing）
  14	跑步（running）
  15	慢跑（jogging）

需求 #

生成一个包含6列的结果表，列名依次为response_id, sitting, walking, standing, running, jogging，其中除response_id外，其余列均为BOOLEAN类型：

• sitting：当question_id=11且response='1'时为TRUE，否则为FALSE
• walking：当question_id=12且response='1'时为TRUE，否则为FALSE
• standing：当question_id=13且response='1'时为TRUE，否则为FALSE
• running：当question_id=14且response='1'时为TRUE，否则为FALSE
• jogging：当question_id=15且response='1'时为TRUE，否则为FALSE

解决方案 #

使用条件聚合实现行转列，按用户分组后，通过判断语句生成对应布尔字段：

通用SQL写法（支持多数数据库） #

SELECT 
    response_id,
    MAX(CASE WHEN question_id = 11 AND response = '1' THEN TRUE ELSE FALSE END) AS sitting,
    MAX(CASE WHEN question_id = 12 AND response = '1' THEN TRUE ELSE FALSE END) AS walking,
    MAX(CASE WHEN question_id = 13 AND response = '1' THEN TRUE ELSE FALSE END) AS standing,
    MAX(CASE WHEN question_id = 14 AND response = '1' THEN TRUE ELSE FALSE END) AS running,
    MAX(CASE WHEN question_id = 15 AND response = '1' THEN TRUE ELSE FALSE END) AS jogging
FROM response
GROUP BY response_id;

MySQL简化写法 #

MySQL支持IF函数，可替代CASE语句简化代码：

SELECT 
    response_id,
    MAX(IF(question_id = 11 AND response = '1', TRUE, FALSE)) AS sitting,
    MAX(IF(question_id = 12 AND response = '1', TRUE, FALSE)) AS walking,
    MAX(IF(question_id = 13 AND response = '1', TRUE, FALSE)) AS standing,
    MAX(IF(question_id = 14 AND response = '1', TRUE, FALSE)) AS running,
    MAX(IF(question_id = 15 AND response = '1', TRUE, FALSE)) AS jogging
FROM response
GROUP BY response_id;

预期结果 #

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