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

Question

我有一个名为events的表，用于保存现场发生的每个事件。我尝试的Fiddle和代码

+====+==========+=========+===============+=========+=====================+
| 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。。。是来自CCD_ 4列的动态列。

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

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

events表中的type列可以包含数千个DISTINCT值

我必须聚合每个用户的数据。例如：用户1可能只有pageview、purchase。。。事件，用户2可能具有purchase、testevent1。。。事件，所以他们会在前面有不同的列

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

我尝试的Fiddle和代码

任何帮助都将不胜感激。

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;