Учебник по Data Explorer — Часть 2: Основы SQL в Discourse

[SaraDev (Sara Devlaeminck)]

Данное руководство является продолжением Учебника по Data Explorer — Часть 1: Написание вашего первого запроса.

Теперь, когда вы увидели, как работает Data Explorer на практике, давайте поговорим об основах SQL, которые пригодятся вам в ваших приключениях по исследованию данных.

SQL-операторы

Все действия, которые вам потребуется выполнить с базой данных Discourse, осуществляются с помощью SQL-операторов.

Ключевые слова 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. Оператор HAVING применяется в SQL, потому что ключевое слово WHERE нельзя использовать с агрегатными функциями (например: COUNT(), MAX(), MIN(), SUM(), AVG()).

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, ...);

Точки с запятой не обязательны при написании SQL-запросов в Data Explorer.

PostgreSQL

Discourse в настоящее время использует PostgreSQL версии 13 для своей базы данных — это система управления реляционными базами данных (СУБД) с открытым исходным кодом, которая делает акцент на расширяемость и соответствие стандарту SQL.

Для глубокого понимания PostgreSQL рекомендуется ознакомиться с официальной документацией PostgreSQL. В частности, разделы, посвященные Запросам, Типам данных и Функциям, будут особенно полезны при написании SQL-запросов.

Таблицы базы данных

База данных Discourse состоит из таблиц. Таблицы — это основной способ хранения данных в реляционной системе управления базами данных.

Каждая таблица в Discourse имеет имя (например, posts или topics), и каждая таблица содержит записи (строки) с данными. Столбцы в таблицах часто называются полями.

База данных Discourse содержит более 240 таблиц!

Data Explorer сначала перечисляет 9 наиболее важных таблиц в панели редактирования запросов, а вы можете увидеть структуру и типы столбцов всех таблиц одним щелчком мыши:

Схема базы данных

В PostgreSQL, как и в других реляционных базах данных, концепции первичного ключа и внешнего ключа являются фундаментальными. Они используются для установления связей между таблицами и обеспечения согласованности и целостности данных.

Первичные ключи

Первичный ключ — это столбец или набор столбцов в таблице, которые однозначно идентифицируют каждую строку в этой таблице. Ни две строки в таблице не могут иметь одинаковое значение первичного ключа. Значение первичного ключа не может быть NULL и должно быть уникальным. Первичный ключ используется для индексации данных в таблице, что значительно ускоряет извлечение данных. В обозревателе схемы Discourse первичный ключ таблицы всегда указывается первым.

Пример: Первичным ключом таблицы posts является поле id.

Внешние ключи

Внешний ключ — это столбец или набор столбцов в таблице, который используется для установления связи между данными в двух таблицах. Он действует как перекрестная ссылка между таблицами, поскольку ссылается на первичный ключ другой таблицы, тем самым устанавливая связь между ними. Таблица с внешним ключом называется дочерней таблицей, а таблица с первичным ключом — родительской или ссылочной таблицей. В обозревателе схемы 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'

В этом запросе мы используем внешний ключ user_id в таблице posts для соединения с первичным ключом id в таблице users. Это позволяет нам найти все сообщения, сделанные определенным пользователем.

SQL-объединения (JOINS)

Как упоминалось ранее, оператор JOIN используется для объединения строк из двух или более таблиц на основе связанного столбца между ними.

Важно отметить, что существует несколько типов операторов 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, также известный как общее табличное выражение (Common Table Expression, 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, содержащую идентификатор каждого пользователя и общее количество его сообщений. Затем основной запрос объединяет эту таблицу с таблицей users, чтобы получить имена пользователей, связанные с каждым идентификатором, и сортирует результат по количеству сообщений в порядке убывания.

Этот запрос вернет количество сообщений, опубликованных каждым пользователем.

Подзапросы — это запросы, вложенные внутрь другого запроса. Подзапрос может использоваться внутри операторов 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) выбирает user_id из таблицы posts для пользователей, сделавших более 100 сообщений. Затем основной запрос получает username из таблицы users для этих user_id.

Этот запрос вернет список имен пользователей, сделавших более 100 сообщений.

Другие темы в этой серии

• Учебник по Data Explorer — Часть 1: Написание вашего первого запроса
• Эта тема
• Использование параметров в запросах Data Explorer
• Форматирование результатов таблицы Data Explorer