mysql - MySQL中子查询的聚合结果不保留我的字段之一

问题描述

为了从用户会话中提取几行关键数据，我试图通过后续group by聚合处理子查询的结果，但我发现子查询中的一列没有结转.

我有三个表：一个user_actions可以在 Web 应用程序中跟踪，另一个包含user_events所述操作的实例，第三个player_keys包含用于将多个 user_events 绑定到单个会话中的会话 ID。

由于定义方式的怪异user_actions，特定user_action实例有时由quantity与其一起传递的值标识，有时每个可能的结果都有自己的user_action.

所以给定一系列user_actions...

+----+-------------------+-------------+
| id |       NAME        | campaign_id |
+----+-------------------+-------------+
| 15 | Theme Vote Age    |         301 |
| 18 | Theme Vote Gender |         301 |
| 20 | Theme 5 Selected  |         301 |
+----+-------------------+-------------+

...我有user_events这样的

+---------------+----------------+----------+---------------------+
| player_key_id | user_action_id | quantity |      created_at     |
+---------------+----------------+----------+---------------------+
|           123 |             15 |       50 | 2019-10-11 12:34:56 |
|           123 |             18 |        2 | 2019-10-11 12:34:57 |
|           123 |             20 |        1 | 2019-10-11 12:34:58 |
+---------------+----------------+----------+---------------------+

我的查询是通过子查询的两步过程：首先，我在子查询中查询玩家键 ID 和主题投票数，然后将其与另一个查询连接起来，从这些玩家键 ID（即用户会话）中查找其他行以添加我想要的其他两个字段（重写为使用INNER JOIN我认为 Gordon 在 [他的评论] 中建议的语法，（MySQL 中子查询的聚合结果不保留我的一个字段）使用EricCASE的语句并删除了多余的根据tcadidot加入表格）：player_keys

SELECT ue.player_key_id, vd.theme_vote,
    max(if(ua.name = 'Theme Vote Age', quantity, 0)) as theme_age,
    max(if(ua.name = 'Theme Vote Gender', quantity, 0)) as theme_gender
FROM user_events AS ue
INNER JOIN user_actions ua
    ON ua.id = ue.user_action_id
INNER JOIN (
    SELECT ue.player_key_id AS player_key_id, 
        max(CASE ua.name
            WHEN 'Theme 1 Selected' THEN 1
            WHEN 'Theme 2 Selected' THEN 2
            WHEN 'Theme 3 Selected' THEN 3
            WHEN 'Theme 4 Selected' THEN 4
            WHEN 'Theme 5 Selected' THEN 5
            ELSE 6
        END) as theme_vote
    FROM user_events ue
    INNER JOIN user_actions ua
        ON ue.user_action_id = ua.id
    WHERE ua.campaign_id = 301
        AND ua.name LIKE 'Theme % Selected'
        AND date(ue.created_at) = current_date
    GROUP BY ue.player_key_id
    ) vd
    ON ue.player_key_id = vd.player_key_id
WHERE (ua.name = 'Theme Vote Age' OR ua.name = 'Theme Vote Gender')
GROUP BY ue.player_key_id
HAVING theme_age > 1 AND theme_age < 100 AND theme_gender != 3;

我的想法是需要内部查询GROUP BY，因为每个会话按分组player_key_id包含一组主题投票数据（包括与“主题百分比选定”匹配的一个。外部查询player_key_id再次分组，因为每个会话都包含一个“主题投票”我查过的“年龄”和“主题投票性别” user_action，每次会话都返回两行。

我期待回来的是这样的

+---------------+------------+-----------+--------------+
| player_key_id | theme_vote | theme_age | theme_gender |
+---------------+------------+-----------+--------------+
|           123 |          5 |        50 |            2 |
+---------------+------------+-----------+--------------+
|           163 |          1 |        37 |            1 |
+---------------+------------+-----------+--------------+
|           748 |          2 |        28 |            1 |
+---------------+------------+-----------+--------------+

但我得到的是

+---------------+------------+-----------+--------------+
| player_key_id | theme_vote | theme_age | theme_gender |
+---------------+------------+-----------+--------------+
|           123 |          6 |        50 |            2 |
+---------------+------------+-----------+--------------+
|           163 |          6 |        37 |            1 |
+---------------+------------+-----------+--------------+
|           748 |          6 |        28 |            1 |
+---------------+------------+-----------+--------------+

所以基本上，theme_vote所有分组都变成了 6。我的子查询本身工作正常。它按预期返回这样的行，其中theme_vote跨度为 1-6：

+---------------+------------+
| player_key_id | theme_vote |
+---------------+------------+
|           123 |          5 |
+---------------+------------+
|           724 |          2 |
+---------------+------------+
|           833 |          3 |
+---------------+------------+
|           298 |          2 |
+---------------+------------+
|           529 |          6 |
+---------------+------------+

在不同的日子运行它会返回不同数量的数据，表明日期过滤器有效，campaign_id过滤器也成功。

子查询返回的行数也明显低于整个查询。

在此处参考小提琴：https ://www.db-fiddle.com/f/8U2WoHG7tzimFbEZP956kq/0

为了进一步澄清，我GROUP BY在外部查询中使用了这个：

+---------------+------------+-----------+--------------+
| player_key_id | theme_vote | theme_age | theme_gender |
+---------------+------------+-----------+--------------+
|      12160443 |          1 |        33 |            0 |
|      12160443 |          1 |         0 |            2 |
+---------------+------------+-----------+--------------+

...进入这个：

+---------------+------------+-----------+--------------+
| player_key_id | theme_vote | theme_age | theme_gender |
+---------------+------------+-----------+--------------+
|      12160443 |          1 |        33 |            2 |
+---------------+------------+-----------+--------------+

标签： mysqlsqlsubquery

mysql - MySQL中子查询的聚合结果不保留我的字段之一

问题描述

解决方案

推荐阅读