Data Explorerチュートリアル - パート2 - Discourse SQLの基本

コミュニティ データとレポート
, ,
1

:discourse: このガイドは、データエクスプローラーチュートリアル - パート 1 - 最初のクエリ作成 の続編です。

Data Explorer が実際にどのように機能するかをご覧いただいたところで、データ探索の旅を進める際に役立つ SQL の基礎について説明しましょう。

SQL ステートメント

Discourse のデータベース上で実行する必要があるすべての操作は、SQL ステートメントを用いて行われます。

:discourse: SQL キーワードは大文字小文字を区別しません：select は SELECT と同じですが、このチュートリアルではベストプラクティスとして、すべての SQL キーワードを大文字で記述します。

以下は、よく使用される SQL ステートメントの一部です。これらの中には、前の例のクエリ でも使用したものが含まれています。

  • SELECT: データベースからデータを取得するために使用されます。返されるデータは結果セットと呼ばれる結果テーブルに格納されます。
SELECT column1, column2, ...
FROM table_name;
  • WHERE: レコードをフィルタリングするために使用されます。
SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • ORDER BY: 結果セットを昇順または降順にソートするために使用されます。
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
  • COUNT: 指定された条件に一致する行数を返します。
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
  • GROUP BY: 集約関数（COUNT、MAX、MIN、SUM、AVG）と組み合わせて使用され、結果セットを 1 つ以上の列でグループ化します。
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
  • HAVING: GROUP BY 操作の結果をフィルタリングするために使用されます。WHERE キーワードは集約関数（例：COUNT()、MAX()、MIN()、SUM()、AVG()）と併用できないため、SQL では HAVING 句が使用されます。
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
  • IN: 値が値のセットに含まれているか、またはサブクエリによって返されているかを確認するために使用されます。複数の OR 条件の簡略版であり、SQL クエリをより簡潔で読みやすくします。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

:discourse: Data Explorer で SQL クエリを書く場合、セミコロンは必須ではありません。

PostgreSQL

Discourse は現在、データベースとして PostgreSQL バージョン 13 を使用しています。これは、拡張性と SQL 準拠を重視したオープンソースのリレーショナルデータベース管理システム（RDBMS）です。

PostgreSQL を詳しく理解するには、PostgreSQL 公式ドキュメント を参照することをお勧めします。特に、SQL クエリ作成に関連する クエリ、データ型、関数 のセクションが参考になるでしょう。

データベーステーブル

Discourse データベースはテーブルで構成されています。テーブルは、リレーショナルデータベース管理システムでデータを格納する基本的な方法です。

Discourse の各テーブルは名前（例：posts や topics）で識別され、各テーブルにはデータを含むレコード（行）が含まれています。テーブルの列は一般的にフィールドと呼ばれます。

:discourse: Discourse データベースには 240 以上のテーブルがあります！

Data Explorer では、クエリ編集 UI パネルに最も重要な 9 つのテーブルが最初にリストされ、すべてのテーブルの列構造と型をワンクリックで確認できます。

データベーススキーマ

PostgreSQL および他のリレーショナルデータベースでは、主キーと外部キーの概念が基本的です。これらはテーブル間の関係を確立し、データの整合性と一貫性を保証するために使用されます。

主キー

主キーとは、テーブル内の列または列のセットであり、そのテーブル内の各行を一意に識別します。テーブル内の 2 つの行が同じ主キー値を持つことはできません。主キー値に NULL を設定することはできず、値は一意でなければなりません。主キーはテーブル内のデータをインデックス化するために使用され、データ検索を大幅に高速化します。Discourse のスキーマエクスプローラーでは、テーブルの主キーは常に最初にリストされます。

例：posts テーブルの主キーは id フィールドです。

外部キー

外部キーとは、テーブル内の列または列のセットであり、2 つのテーブル間のデータリンクを確立するために使用されます。これは他のテーブルの主キーを参照するため、テーブル間の相互参照として機能し、それらをリンクします。外部キーを持つテーブルを子テーブル、主キーを持つテーブルを参照テーブルまたは親テーブルと呼びます。Discourse のスキーマエクスプローラーでは、外部キーは fkey というテキストに続いて親テーブルの名前が表示されることで示されます。

例：posts テーブルにおいて、user_id は users テーブルの外部キーです。

主キーと外部キーの使用

Data Explorer でクエリを作成する際、これらの主キーと外部キーを使用してテーブルを JOIN し、より複雑なデータを取得できます。例えば、特定のユーザーが投稿したすべての投稿を見つけたい場合、以下のようなクエリを作成できます。

SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'username_here'

このクエリでは、posts テーブルの user_id 外部キーを使用して、users テーブルの id 主キーと結合しています。これにより、特定のユーザーが投稿したすべての投稿を見つけることができます。

SQL JOIN

前述の通り、JOIN ステートメントは、関連する列に基づいて 2 つ以上のテーブルから行を結合するために使用されます。

重要な点として、いくつかの種類の SQL JOIN ステートメントがあり、それぞれ異なる目的で使われます。

  1. INNER JOIN: INNER JOIN キーワードは、両方のテーブルで一致する値を持つレコードを選択します。一致する行を両方のテーブルから返します。両方のテーブルで一致するレコードのみを返したい場合は INNER JOIN を使用します。JOIN を指定せずに使用した場合（LEFT、RIGHT、FULL など）、デフォルトでは INNER JOIN になります。

  2. LEFT (OUTER) JOIN: LEFT JOIN キーワードは、左側のテーブル（table1）からすべてのレコードと、右側のテーブル（table2）から一致するレコードを返します。一致がない場合、右側からの結果は NULL になります。左側のテーブルからすべてのレコードと、右側のテーブルから一致するレコードを返したい場合は LEFT JOIN を使用します。一致がない場合、右側の結果は NULL になります。

  3. RIGHT (OUTER) JOIN: RIGHT JOIN キーワードは、右側のテーブル（table2）からすべてのレコードと、左側のテーブル（table1）から一致するレコードを返します。一致がない場合、左側からの結果は NULL になります。右側のテーブルからすべてのレコードと、左側のテーブルから一致するレコードを返したい場合は RIGHT JOIN を使用します。一致がない場合、左側の結果は NULL になります。

  4. FULL (OUTER) JOIN: FULL JOIN キーワードは、左側（table1）または右側（table2）のテーブルレコードのいずれかで一致がある場合、すべてのレコードを返します。いずれかのテーブルで一致がある場合、すべてのレコードを返したい場合は FULL JOIN を使用します。

複数の JOIN ステートメントを組み合わせて、複数のテーブルを結合することもできます。例えば、topic_tags テーブルを topics テーブルに結合し、その後 tags テーブルを topic_tags テーブルに結合することで、トピックに関連するタグ名にアクセスできます。

SELECT 
    t.id AS topic_id, 
    tg.name as tag_name
FROM 
    topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id

WITH ステートメントとサブクエリ

SQL の WITH ステートメントは、共通テーブル式（CTE）とも呼ばれ、他の SELECT ステートメント内で参照できる一時結果セットを作成するために使用されます。これは複雑な SQL クエリを扱う際に特に有用で、クエリを簡素化し、読みやすく保守しやすくするのに役立ちます。

以下は、WITH ステートメントの基本的な例です。

WITH post_counts AS (
  SELECT user_id, COUNT(*) as post_count
  FROM posts
  GROUP BY user_id
)

SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;

このクエリでは、WITH ステートメントが post_counts という一時テーブルを作成し、そこには各ユーザーの ID と総投稿数が含まれています。メインクエリはその後、このテーブルを users テーブルと結合して各 ID に関連するユーザー名を取得し、結果を投稿数の降順でソートします。

このクエリは、各ユーザーが投稿した投稿数を返します。

サブクエリとは、他のクエリの中にネストされたクエリのことです。サブクエリは SELECT ステートメント内または他のサブクエリ内で使用できます。サブクエリはレコードのセット、単一のレコード、または単一の値を返すことができます。

以下は、サブクエリの例です。

SELECT u.username 
FROM users u
WHERE u.id IN (
  SELECT p.user_id 
  FROM posts p
  GROUP BY p.user_id 
  HAVING COUNT(p.id) > 100
)

この例では、サブクエリ (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) が、100 件以上投稿したユーザーの posts テーブルから user_id を選択します。メインクエリはその後、それらの user_id に対して users テーブルから username を取得します。

このクエリは、100 件以上投稿したユーザーのユーザー名のリストを返します。

このシリーズの他のトピック

「いいね！」 9
Discourse Data Explorer
Using Parameters in Data Explorer Queries
Dashboard Report - Consolidated Pageviews