如何將動態行轉換並儲存為動態列

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;