데이터 탐색자 튜토리얼 - 2부 - Discourse SQL 기초

커뮤니티 구축 데이터 및 보고
, ,
1

:discourse: 이 가이드는 Data Explorer 튜토리얼 - Part 1 - 첫 쿼리 작성의 연속편입니다.

이제 Data Explorer가 실제로 어떻게 작동하는지 확인했으니, 데이터 탐색을 진행하는 데 도움이 될 몇 가지 SQL 기초 지식을 알아볼 차례입니다.

SQL 문법

Discourse 데이터베이스에서 수행해야 하는 모든 작업은 SQL 문을 통해 이루어집니다.

:discourse: SQL 키워드는 대소문자를 구분하지 않습니다. select와 SELECT은 동일하지만, 이 튜토리얼에서는 모범 사례에 따라 모든 SQL 키워드를 대문자로 작성하겠습니다.

다음은 사용할 수 있는 가장 일반적인 SQL 문법들 중 일부입니다. 이전 예제 쿼리에서 이 중 일부가 사용되었음을 주목해 주세요.

  • SELECT: 데이터베이스에서 데이터를 선택하는 데 사용됩니다. 반환된 데이터는 결과 테이블(result-set)에 저장됩니다.
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)와 함께 자주 사용됩니다.
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 쿼리를 작성할 때 세미콜론은 필요하지 않습니다.

PostgresSQL

Discourse는 현재 데이터베이스로 PostgreSQL 버전 13을 사용하며, 이는 확장성과 SQL 준수에 중점을 둔 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)입니다.

PostgreSQL에 대한 자세한 이해를 원한다면 PostgreSQL 공식 문서를 읽어보시는 것을 권장합니다. 특히 SQL 쿼리를 작성할 때 쿼리, 데이터 유형, 함수 섹션이 특히 관련이 있을 수 있습니다.

데이터베이스 테이블

Discourse 데이터베이스는 테이블로 구성됩니다. 테이블은 관계형 데이터베이스 관리 시스템에서 데이터를 저장하는 기본적인 방식입니다.

Discourse의 각 테이블은 이름(예: posts 또는 topics)으로 식별되며, 각 테이블에는 데이터가 포함된 레코드(행)가 있습니다. 테이블의 열은 일반적으로 필드라고 불립니다.

:discourse: Discourse 데이터베이스에는 240개 이상의 테이블이 있습니다!

Data Explorer는 쿼리 편집 UI 패널에서 가장 중요한 9개의 테이블을 먼저 나열하며, 클릭 한 번으로 모든 테이블의 열 구조와 유형을 확인할 수 있습니다:

데이터베이스 스키마

PostgreSQL을 비롯한 다른 관계형 데이터베이스에서도 기본 키(Primary Key)와 외부 키(Foreign Key) 개념은 근본적입니다. 이들은 테이블 간의 관계를 설정하고 데이터의 일관성과 무결성을 보장하는 데 사용됩니다.

기본 키(Primary Keys)

기본 키는 테이블 내에서 각 행을 고유하게 식별하는 열 또는 열의 집합입니다. 테이블의 두 행이 동일한 기본 키 값을 가질 수 없습니다. 기본 키 값은 null일 수 없으며, 값은 고유해야 합니다. 기본 키는 테이블의 데이터를 색인화하여 데이터 검색 속도를 훨씬 빠르게 만듭니다. Discourse 스키마 탐색기에서 테이블의 기본 키는 항상 첫 번째로 나열됩니다.

예: posts 테이블의 primary key는 id 필드입니다.

외부 키(Foreign Keys)

외부 키는 두 테이블의 데이터 사이에 링크를 설정하는 데 사용되는 테이블의 열 또는 열의 집합입니다. 이는 다른 테이블의 기본 키를 참조하여 테이블 간에 링크를 설정하므로 테이블 간 교차 참조 역할을 합니다. 외부 키가 있는 테이블은 자식 테이블(child table)이라고 하며, 기본 키가 있는 테이블은 참조 테이블 또는 부모 테이블(parent table)이라고 합니다. Discourse 스키마 탐색기에서 외부 키는 fkey 텍스트 뒤에 부모 테이블 이름이 붙어 표시됩니다.

예: posts 테이블에서 user_id는 users 테이블의 foreign key입니다.

기본 키와 외부 키 사용

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 JOINS

앞서 언급했듯이, JOIN 문은 관련 열을 기준으로 두 개 이상의 테이블에서 행을 결합하는 데 사용됩니다.

여러 종류의 SQL JOIN 문이 있으며, 각각 다른 목적을 위해 사용됨을 알아두는 것이 중요합니다:

  1. INNER JOIN: INNER JOIN 키워드는 두 테이블 모두에서 일치하는 값을 가진 레코드를 선택합니다. 일치하는 행이 있는 경우에만 두 테이블의 행을 반환합니다. 두 테이블 모두에서 일치하는 레코드만 반환하려면 INNER JOIN을 사용하세요. 조인 유형(LEFT , RIGHT , 또는 FULL 등)을 지정하지 않고 JOIN을 사용할 경우, 기본적으로 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 문은 각 사용자의 ID와 총 게시물 수를 포함하는 임시 테이블 post_counts를 생성합니다. 메인 쿼리는 이 테이블을 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개 이상의 게시물을 작성한 사용자의 user_id를 posts 테이블에서 선택합니다. 메인 쿼리는 그런 user_id에 해당하는 username을 users 테이블에서 가져옵니다.

이 쿼리는 100개 이상의 게시물을 작성한 사용자의 사용자 이름 목록을 반환합니다.

이 시리즈의 더 많은 주제

9개의 좋아요
Using Parameters in Data Explorer Queries
Discourse Data Explorer
Dashboard Report - Consolidated Pageviews