Comment convertir et enregistrer des lignes dynamiques en colonnes dynamiques

Question

J'ai une table appelée events où je sauvegarde chaque événement qui se produit sur le site Web. Violon et code que j'ai essayé

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

J'ai un tableau dynamique devant et je veux y avoir des colonnes dynamiques, comme celle-ci.

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

où event1、event2 ...是来自 events.type est la colonne dynamique de la colonne.

Je recherche la meilleure façon d'organiser et d'agréger les données dans un events tableau.

J'ai écrit la requête pour convertir les lignes en colonnes et afficher les données comme je le souhaite, mais le problème est de sauvegarder ces données agrégées de manière à pouvoir les filtrer plus tard.

• events 表中的 type 列可以包含数千个 DISTINCT La colonne type dans un tableau peut contenir des milliers de valeurs DISTINCT
• Je dois agréger les données pour chaque utilisateur. Par exemple : l'utilisateur 1 ne peut avoir que pageview、purchase ...事件，用户2可能有purchase、testevent1... événements, il y aura donc différentes colonnes devant.

Des suggestions sur la façon d'agréger events les données et de les enregistrer dans une autre table afin de pouvoir y exécuter différentes requêtes plus tard ?

Le violon et le code que j'ai essayé

Toute aide serait grandement appréciée.

Answer 1

En SQL, vous ne pouvez pas créer une table avec des colonnes différentes pour chaque ligne. Il n’existe pas de « colonne dynamique ».

L'un des faits fondamentaux du fonctionnement des bases de données relationnelles est qu'un tableau a un en-tête qui nomme les colonnes et les types de données, suivi d'un ensemble de lignes où chaque ligne du tableau contient exactement les mêmes colonnes que l'en-tête. Ce tableau.

La meilleure chose à faire est donc de créer un tableau avec une colonne pour chaque type d'événement, même si ce type d'événement n'est pas disponible pour certains utilisateurs. La valeur de cette colonne peut être NULL, indiquant qu'elle n'est pas pertinente pour cet utilisateur.

Dans une requête SQL SELECT, vous devez spécifier toutes les colonnes de la requête. Ces colonnes doivent être corrigées dans la requête avant de l'analyser et avant le début de l'exécution. Vous ne pouvez pas créer une requête qui ajoute dynamiquement des colonnes lors de l'examen des données lors de l'exécution de la requête.

Vous avez donc un type de requête pivot :

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;

Avant d'écrire cette requête, vous devez comprendre tous les types d'événements possibles. Vous pouvez utiliser une autre requête pour ce faire :

SELECT DISTINCT type FROM events;