如何用SQL将同表数据按App拆分列并合并查询结果？

问题描述 #

我数据库中有一张表结构如下：

| date      | app    | action    | response |
|-----------|--------|-----------|----------|
| 9/22/2022 | e-file | launch    | 2        |
| 9/22/2022 | e-file | login     | 3        |
| 9/22/2022 | e-file | edit      | 5        |
| 9/22/2022 | e-file | clicksave | 6        |
| 9/22/2022 | e-file | logout    | 7        |
| 9/28/2022 | cube   | launch    | 3        |
| 9/28/2022 | cube   | login     | 2        |
| 9/28/2022 | cube   | edit      | 7        |
| 9/28/2022 | cube   | clicksave | 8        |
| 9/28/2022 | cube   | logout    | 9        |

希望得到可用于Grafana表格的结果：将response列按app字段拆分为不同列，并新增基于这两列的计算列，目标结果如下：

| action    | response_e-file | response_cube | e vs cube |
|-----------|-----------------|---------------|-----------|
| launch    | 2               | 3             | 0.33      |
| login     | 3               | 2             | -0.33     |
| edit      | 5               | 7             | 0.40      |
| clicksave | 6               | 8             | 0.33      |
| logout    | 7               | 9             | 0.29      |

作为SQL新手，我没能实现这个需求，请问该如何编写查询语句？是否可以实现？

解决方案 #

这个需求完全可以实现，核心是通过条件聚合（行转列）拆分列，再添加计算逻辑。

完整查询语句 #

假设你的表名为app_actions，执行以下SQL即可得到目标结果：

SELECT
    action,
    -- 提取e-file对应的response值
    MAX(CASE WHEN app = 'e-file' THEN response END) AS `response_e-file`,
    -- 提取cube对应的response值
    MAX(CASE WHEN app = 'cube' THEN response END) AS `response_cube`,
    -- 计算e vs cube：(cube值 - e-file值)/e-file值，保留两位小数
    ROUND(
        (MAX(CASE WHEN app = 'cube' THEN response END) - MAX(CASE WHEN app = 'e-file' THEN response END)) 
        / MAX(CASE WHEN app = 'e-file' THEN response END),
        2
    ) AS `e vs cube`
FROM app_actions
-- 若需指定日期范围，可添加WHERE条件，例如：
-- WHERE date IN ('9/22/2022', '9/28/2022')
GROUP BY action
ORDER BY action;

关键逻辑说明 #

1. 条件聚合拆分列：用CASE WHEN匹配app值，仅保留对应app的response，再通过MAX聚合（因为每个action+app组合只有一条数据，用MIN也可以），将同一action的不同app数据合并到一行。
2. 对比列计算：按照(cube响应值 - e-file响应值)/e-file响应值的逻辑计算，用ROUND函数保留两位小数，和目标结果的精度一致。
3. 分组排序：通过GROUP BY action实现行转列，ORDER BY action保证结果按动作名称排序。

额外注意事项 #

• 如果数据库中日期是日期类型而非字符串，需调整WHERE子句的日期写法，比如MySQL中用STR_TO_DATE('9/22/2022', '%m/%d/%Y')转换格式。
• 若存在某个action在其中一个app中无数据的情况，对应列会返回NULL，可使用COALESCE替换为默认值，例如COALESCE(MAX(CASE WHEN app = 'e-file' THEN response END), 0)。

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