動的行を動的列に変換して保存する方法

Question

events というテーブルがあり、そこに Web サイトで発生するすべてのイベントが保存されます。 私が試したフィドルとコード

リーリー

前に動的テーブルがあり、その中に次のように動的列を入れたいと考えています。

リーリー

ここで、 event1、event2... は、events.type 列の動的列です。

events テーブルのデータを整理して集計する最適な方法を探しています。

行を列に変換し、希望どおりにデータを表示するクエリを作成しましたが、問題は、この集計データを後でフィルター処理できる方法で保存することです。

• events テーブル内の type 列には、数千の DISTINCT 値
を含めることができます。
• ユーザーごとにデータを集計する必要があります。例: ユーザー 1 は pageview、purchase... イベントのみを持つことができ、ユーザー 2 は purchase、testevent1... イベントを持つことができます。イベントがあるため、前に異なる列が表示されます。

events データを集計して別のテーブルに保存し、後でそのテーブルで別のクエリを実行できるようにする方法について何か提案はありますか?

私が試したフィドルとコード

ご協力いただければ幸いです。

Answer 1

SQL では、行ごとに異なる列を持つテーブルを作成することはできません。 「動的列」などというものはありません。

リレーショナル データベースの動作に関する基本的な事実の 1 つは、テーブルには列とデータ型の名前を指定するヘッダーがあり、その後にテーブル内の各行がヘッダーとまったく同じ列を持つ一連の行が続くということです。あのテーブル。

したがって、たとえそのイベント タイプが一部のユーザーにとって利用できない場合でも、各イベント タイプの列を含むテーブルを作成することが最善の策です。この列の値は、このユーザーに関係がないことを示す NULL にすることもできます。

SQL SELECT クエリでは、クエリ内のすべての列を指定する必要があります。これらの列は、クエリを解析する前、および実行を開始する前にクエリ内で修正する必要があります。クエリの実行中にデータを調べながら動的に列を追加するクエリを作成することはできません。

つまり、1 種類のピボット クエリがあります:

SELECT ユーザー 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',
 ...考えられるすべての型を説明するまで、さらに列の式を追加します...
イベントから
ユーザー ID ごとにグループ化;

このクエリを作成する前に、考えられるすべてのイベント タイプを理解する必要があります。これを行うには、別のクエリを使用できます:

イベントから DISTINCT タイプを選択;