Cara menukar dan menyimpan baris dinamik kepada lajur dinamik

Question

Saya mempunyai jadual yang dipanggil events di mana saya menyimpan setiap acara yang berlaku di tapak web. Biola dan kod saya cuba

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

Saya mempunyai jadual dinamik di hadapan dan saya mahu mempunyai lajur dinamik di dalamnya, seperti ini.

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

di mana event1、event2 ...是来自 events.type ialah lajur dinamik lajur.

Saya sedang mencari cara terbaik untuk menyusun dan mengagregat data dalam events jadual.

Saya telah menulis pertanyaan untuk menukar baris menjadi lajur dan memaparkan data seperti yang saya mahu, tetapi masalahnya ialah menyimpan data terkumpul ini dengan cara yang saya boleh menapisnya kemudian.

• events 表中的 type 列可以包含数千个 DISTINCT Lajur type dalam jadual boleh mengandungi beribu-ribu nilai DISTINCT
• Saya perlu mengagregatkan data untuk setiap pengguna. Contohnya: Pengguna 1 mungkin hanya mempunyai pageview、purchase ...事件，用户2可能有purchase、testevent1... acara, jadi akan ada lajur yang berbeza di hadapan.

Ada sebarang cadangan tentang cara mengagregatkan events data dan simpan dalam jadual lain supaya saya boleh menjalankan pertanyaan yang berbeza di dalamnya kemudian?

Rebab dan kod yang saya cuba

Sebarang bantuan amat kami hargai.

Answer 1

Dalam SQL anda tidak boleh mencipta jadual dengan lajur berbeza untuk setiap baris. Tiada perkara seperti "lajur dinamik".

Salah satu fakta asas tentang cara pangkalan data hubungan berfungsi ialah jadual mempunyai pengepala yang menamakan lajur dan jenis data, diikuti dengan set baris di mana setiap baris dalam jadual mempunyai lajur yang sama persis dengan pengepala. meja itu.

Jadi perkara terbaik yang boleh anda lakukan ialah membuat jadual dengan lajur untuk setiap jenis acara, walaupun jenis acara itu tidak tersedia kepada sesetengah pengguna. Nilai dalam lajur ini boleh menjadi NULL, menunjukkan bahawa ia tidak berkaitan dengan pengguna ini.

Dalam pertanyaan SQL SELECT, anda mesti menentukan semua lajur dalam pertanyaan. Lajur ini mesti dibetulkan dalam pertanyaan sebelum menghuraikannya dan sebelum pelaksanaan bermula. Anda tidak boleh membuat pertanyaan yang menambahkan lajur secara dinamik semasa memeriksa data semasa pelaksanaan pertanyaan.

Jadi anda mempunyai satu jenis pertanyaan pangsi:

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;

Sebelum menulis pertanyaan ini, anda mesti memahami semua jenis acara yang mungkin. Anda boleh menggunakan pertanyaan lain untuk melakukan ini:

SELECT DISTINCT type FROM events;