So konvertieren und speichern Sie dynamische Zeilen in dynamische Spalten

Question

Ich habe eine Tabelle namens events, in der ich jedes Ereignis speichere, das auf der Website passiert. Fiddle und Code habe ich ausprobiert

+====+==========+=========+===============+=========+=====================+
| 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 |
+----+----------+---------+---------------+---------+---------------------+

Ich habe vorne eine dynamische Tabelle und möchte darin dynamische Spalten haben, so wie diese.

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

wobei event1、event2 ...是来自 events.type die dynamische Spalte der Spalte ist.

Ich suche nach der besten Möglichkeit, Daten in einer events Tabelle zu organisieren und zusammenzufassen.

Ich habe die Abfrage geschrieben, um die Zeilen in Spalten umzuwandeln und die Daten so anzuzeigen, wie ich es möchte, aber das Problem besteht darin, diese aggregierten Daten so zu speichern, dass ich sie später filtern kann.

• events 表中的 type 列可以包含数千个 DISTINCT Die Spalte type in einer Tabelle kann Tausende von DISTINCT-Werten enthalten
• Ich muss die Daten für jeden Benutzer aggregieren. Beispiel: Benutzer 1 darf nur pageview、purchase ...事件，用户2可能有purchase、testevent1... Ereignisse haben, daher werden vorne verschiedene Spalten angezeigt.

Irgendwelche Vorschläge, wie man die Daten aggregiert events und in einer anderen Tabelle speichert, damit ich später verschiedene Abfragen darin ausführen kann?

Die Geige und der Code, die ich ausprobiert habe

Jede Hilfe wäre sehr dankbar.

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;