如何将动态行转换并保存为动态列

Question

我有一个名为 events 的表，我在其中保存网站上发生的每个事件。 我尝试过的小提琴和代码

+====+==========+=========+===============+=========+=====================+
| id | type     | user_id | website       | data    | created_at          |
+====+==========+=========+===============+=========+=====================+
| 1  | pageview | 1       | example.com   | /       | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 2  | pageview | 2       | example.com   | /path   | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 3  | purchase | 3       | example.com   | 2222    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 4  | view     | 3       | example.com   | product | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 5  | click    | 3       | example.com   | card    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 6  | pageview | 4       | site.com      | /       | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 7  | click    | 4       | site.com      | product | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 8  | custom   | 5       | example1.com  | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 9  | custom2  | 6       | google.com    | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+
| 10 | custom3  | 5       | example11.com | test    | 2022-02-16 22:00:00 |
+----+----------+---------+---------------+---------+---------------------+

我在前面有动态表，我想在其中有动态列，就像这样。

+==========+=========+==========+=========+=========+=========+=========+=========+
| name     | ...     | event 1  | event 2 | event 3 | event 4 | event 5 | ...     |
+==========+=========+==========+=========+=========+=========+=========+=========+
| test     | ...     | 5        | 50      | 5       | 76      | 23      | ...     |
+----------+---------+----------+---------+---------+---------+---------+---------+
| test2    | ...     | 1        | 78      | 25      | 88      | 54      | ...     |
+----------+---------+----------+---------+---------+---------+---------+---------+

其中 event1、event2 ...是来自 events.type 列的动态列。

我正在寻找组织和聚合 events 表中数据的最佳方法。

我已经编写了查询来将行转换为列并按照我想要的方式显示数据，但问题是以稍后可以过滤的方式保存这些聚合数据。

• events 表中的 type 列可以包含数千个 DISTINCT 值
• 我必须汇总每个用户的数据。例如：用户1可能只有pageview、purchase ...事件，用户2可能有purchase、testevent1 ...事件，所以前面会有不同的列。

有关如何聚合 events 数据并将其保存在另一个表中的任何建议，以便稍后我可以在其中运行不同的查询？

我尝试过的小提琴和代码

任何帮助将不胜感激。

Answer 1

在 SQL 中，您无法创建每行具有不同列的表。不存在“动态列”这样的东西。

关系数据库工作的基本事实之一是表有一个标题，该标题命名了列和数据类型，后面跟着一组行，其中表中的每一行都具有与标题完全相同的列。那张桌子。

因此，您能做的最好的事情就是制作一个表，其中每种事件类型都有一列，即使某些用户无法使用该事件类型。该列中的值可以为 NULL，表示它与该用户无关。

在 SQL SELECT 查询中，您必须指定查询中的所有列。在解析查询之前和开始执行之前，必须在查询中修复这些列。无法创建在查询执行期间检查数据时动态添加列的查询。

因此，您有一种类型的数据透视查询：

SELECT user_id,
 COUNT(CASE type WHEN 'pageview' THEN 1 END) AS `pageview`,
 COUNT(CASE type WHEN 'purchase' THEN 1 END) AS `purchase`,
 COUNT(CASE type WHEN 'view' THEN 1 END) AS `view`,
 COUNT(CASE type WHEN 'click' THEN 1 END) AS `click`,
 ...more column expressions until you account for all possible types... 
FROM events
GROUP BY user_id;

在编写此查询之前，您必须了解所有可能的事件类型。您可以使用另一个查询来执行此操作：

SELECT DISTINCT type FROM events;