Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 30
×

SQL: мне только спросить. Как писать скрипты для получения выборок

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Всем привет! Меня зовут Андрей, и чуть больше трех лет я занимаюсь анализом данных и использую SQL для работы с разными БД. Сейчас работаю дата-аналитиком в компании Genesis. В статье расскажу о том, как писать SQL-скрипты для получения выборок (из нуля в один), на примерах.

Материал ориентирован на человека с начальным уровнем владения SQL для максимально быстрого вхождения в технологию.

Предыстория

Однажды компания, в которой я занимал должность Data Analyst, решила обучить персонал основам работы с базой данных и для этого наняла преподавателя. Мотивация была в том, чтобы коллеги, имея read-only доступы, получали актуальную информацию и простую статистику, не обращаясь к техническим специалистам. Спустя три занятия ученики не написали ни одного запроса и были недовольны материалом.

Программа курса попала ко мне на «переосмысление», в результате чего из неё была выброшена история, теория, все, что касается проектирования и создания БД, а остались способы получения выборок данных.

Это все напомнило мне, как когда-то я сам был вынужден быстро освоить SQL. Шпаргалки содержали всевозможные типы соединений таблиц и минималистичные примеры, а онлайн-курсы рассказывали про нормальные формы, хранимые процедуры и типы данных, что мало подходило для моих целей.

Так и возникла идея написать статью, которая сможет последовательно и без воды рассказать, как использовать SQL для получения выборок из нескольких источников с нужными ограничениями, в нужном виде и разрезах. Особенности используемой БД, оптимизация и прочая теория остается для самоизучения.

Это первая часть материала. Вторая часть будет содержать некоторые фишки и приемы для сложных выборок, чтобы решить все практические задания по SQL, которые находятся на первых страницах выдачи Google.

Итак, начнем.

Первый SELECT, сортировка, LIMIT

В базе данных информация хранится в табличках.

Допустим, у нас есть таблица users, содержащая данные о регистрациях пользователей, которая лежит в схеме (скажем, папке) product. В табличке есть несколько десятков колонок (полей). Выберем интересующие нас:

SELECT reg_dt, id, gender, age, country_code, app — список интересующих полей.
FROM product.users — источник, схема.название_таблицы.
LIMIT 5 — количество строк, которые нужно вывести.

Примечание. Большими буквами обозначим зарезервированные слова (команды).

Будет работать и с маленькими буквами, но так принято для наглядности.

Как говорит мой коллега: «SQL-запрос — это же просто английский текст. Возьми это отсюда, отфильтруй, отсортируй и так далее...»

Итак, мы получили табличку:

reg_dt

id

gender

age

country_code

app

2014-01-02 17:30:21

4446022755

f

37

US

desktop

2014-01-02 21:07:08

4446556074

f

40

CH

android

2014-01-14 21:07:15

4481548107

m

40

GE

mobile

2013-12-30 8:33:09

4436447691

m

49

US

mobile

2013-12-30 11:04:15

4436702697

m

61

CH

desktop

То же самое, отсортированное по возрасту и коду страны:

SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
ORDER BY age DESC, country_code
 — через запятую укажем поля, по которым будет отсортирована полученная выборка.

LIMIT 5 — эту строку можно удалить, тогда у нас будет список из всех пользователей (но их много).

По умолчанию команда ORDER BY отсортирует поле по возрастанию (текстовое по алфавиту, например), но, указав DESC (descending) после названия поля, мы зададим сортировку по убыванию.

(Чтобы выбрать все поля, укажите астериск * вместо названий полей SELECT * FROM product.users.)

Структура запроса, условия WHERE, логические операторы

Модифицируем запрос, чтобы найти пользователей-женщин старше 45 лет:

SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
WHERE gender =  'f' AND age > 45 – указываем условия для выводимых полей.
ORDER BY age DESC, country_code

'f' значение заключено в одинарные кавычки, чтобы указать, что это текст, а не название поля.

Порядок команд в запросе фиксированный:

SELECT 'столбцы или * для выбора всех столбцов; обязательно'
FROM 'таблица'*
WHERE 'условие/фильтрация; необязательно'
GROUP BY 'столбцы, по которым хотим сгруппировать данные; необязательно'
HAVING 'условие/фильтрация на уровне сгруппированных данных; необязательно'
ORDER BY 'столбцы, по которым хотим отсортировать вывод; необязательно'

*Если вдаваться в детали, можно написать запрос без указания таблицы, пример: SELECT 1 column1, 'text' column2. В данном блоке можно записать несколько источников, но об этом поговорим далее.

column1

column2

1

text

В условии WHERE можно использовать большое количество критериев, связанных логическими операторами AND и OR.

age > 45 (еще варианты: =, >, <, <=, >= и != не равно).

Стоит добавить, что 45 < age даст эквивалентный результат, кроме того, возможно сравнить поля между собой age > steps_count.

country_code IN ('US', 'PL', 'CL') Если нужно сравнить наши значения со значениями в списке, используем IN, указав в скобках возможные варианты через запятую.

Условия также можно задавать с приставкой «не» country_code NOT IN ('RU', 'VE').

Это исключит из выборки страны, перечисленные в списке.

BETWEEN '2020-02-02' AND '2020-07-04' — значение между указанными датами, включительно.

В SQL дата записывается в следующем формате: '2020-12-29' (год-месяц-день).

Дата и время: '2020-04-15 06:25:49.000009'. Если не требуется точность, то запись можно сократить '2020-04-15 06:25:49' (отброшенная справа часть времени будет заменена нулями '2020-04-15 06:25:49.000000'). Соответственно, когда мы фильтруем поле, которое содержит дату и время, указывая только дату BETWEEN '2020-02-02' AND '2020-07-04', это равносильно '2020-02-02 00:00:00.000000' AND '2020-07-04 00:00:00.000000'.

Для следующих примеров рассмотрим табличку:

reg_dt

gender

age

site

2012-08-01 0:43:09

m

34

ametconsectetur.us

2014-02-02 0:49:19

f

28

abcdefghij.com

2018-04-02 4:07:42

f

55

loremipsum.com

2021-05-08 12:00:02

m

48

ametconsectetur.com

2021-09-09 10:10:37

m

44

abcdefghij.us

Сравнивать строки можно конструкцией site LIKE '%.com'.

Эта формулировка поможет найти пользователей, зарегистрированных на ресурсах, которые содержат любой набор символов (задано зарезервированным символом %), затем .com, таким образом мы получим пользователей, зарегистрированных на ресурсе с доменом .com.

SELECT site FROM product.users WHERE site LIKE '%.com';

site

abcdefghij.com

loremipsum.com

ametconsectetur.com

SELECT site FROM product.users WHERE site LIKE 'abcdefghij.%';

site

abcdefghij.com

abcdefghij.us

Символ % может содержаться в любой части строки или быть указан несколько раз. Означает «любой набор символов или отсутствие символа».

Написав 'a%.com', мы найдем пользователей в домене .com, где название сайта начинается с буквы a.

site

abcdefghij.com

ametconsectetur.com

Есть еще один зарезервированный символ _ (нижнее подчеркивание), обозначающий один символ. Таким образом найдем пользователей ресурса, который содержит вторым символом букву m и заканчивается доменом .us, можно так: site LIKE '_m%.us'.

site

ametconsectetur.us

Как и в курсе школьной математики (умножение выполняется раньше, чем вычитание), операторы имеют порядок выполнения.

При использовании логического оператора OR («или») стоит быть осторожным и ставить скобки, четко обозначая альтернативы.

Если мы захотим найти пользователей, что зарегистрировались в конкретном временном диапазоне с доменом '%.com' или '%.us', напишем запрос:

SELECT reg_dt, gender, age, site
FROM product.users
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01' 
      AND site LIKE '%.us' OR site LIKE '%.com'

В SQL можно использовать переносы строк и отступы для форматирования, они не влияют на выполнение запроса.

Примечание. В условии хочется написать site LIKE '%.com' OR '%.us', но так не работает, после команды LIKE ожидается один искомый паттерн, нужно повторить конструкцию для второго паттерна.

Выполнив запрос, получим список пользователей. Некоторые из них зарегистрированы за пределами выделенного нами диапазона времени 2012–2014 гг.

reg_dt

gender

age

site

2012-08-01 0:43:09

m

34

ametconsectetur.us

2014-02-02 0:49:19

f

28

abcdefghij.com

2018-04-02 4:07:42

f

55

loremipsum.com

2021-05-08 12:00:02

m

48

ametconsectetur.com

А все потому, что указанное в конце условие OR отменяет все предыдущие условия, связанные оператором AND.

Это условие стоит интерпретировать как «дай указанные поля для пользователей, которые зарегистрированы в дату такую-то и имеют домен .us или просто имеют домен .com (без условия по дате)».

Происходит это как раз по той причине, что AND выполняется раньше, чем OR.

Если мы хотим, чтобы условие даты сохранялось для обоих доменов, стоит явно выделить альтернативы для оператора OR с помощью круглых скобок:

WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
         AND (site LIKE '%.us' OR site LIKE '%.com')

reg_dt

gender

age

site

2012-08-01 0:43:09

m

34

ametconsectetur.us

2014-02-02 0:49:19

f

28

abcdefghij.com

Условие может быть сформулировано и как результат преобразований:

SELECT age, site, LENGTH(site) 
FROM product.users
WHERE age%2 != 0 OR LENGTH(site) > 17

Возраст — непарное число (остаток от деления на 2 не равен 0) или длина ресурса — больше 17 символов. Причем поле, для которого создано условие, необязательно выводить в SELECT.

age

site

LENGTH

34

ametconsectetur.us

18

55

loremipsum.com

14

48

ametconsectetur.com

19

JOIN, что видно и чего не видно. UNION

В основном приходится использовать более одного источника.

В нашей схеме, кроме таблицы с регистрациями:

reg_dt

id

gender

age

country_code

...

2014-01-02 17:30:21

4446022755

f

37

US

...

2014-01-02 21:07:08

4446556074

f

40

CH

...

2014-01-14 21:07:15

4481548107

m

40

GE

...

2013-12-30 8:33:09

4436447691

m

49

US

...

...

...

...

...

...

...

Есть таблица заказов:

user_id

dt

order_id

service_id

...

4446022755

2014-01-02 18:30:01

2435206

14

...

4446022755

2014-02-02 18:25:17

2437018

14

...

4481548107

2014-01-14 21:08:45

2455378

18

...

4481548107

2014-04-11 15:11:18

2460491

14

...

4481548107

2014-04-13 12:10:09

2460602

5

...

...

...

...

...

...

И таблица-справочник для расшифровки типов сервисов:

id

service_name

5

test_srv

9

vip

14

month

18

90 day

...

...

Чтобы извлечь данные из дополнительных таблиц, используем LEFT JOIN, что означает, что наша исходная таблица users словно находится слева и мы приставляем к ней соответствующие строки из таблицы orders_paid.

SELECT u.reg_dt, u.gender AS sex, u.age, u.id, op.user_id, order_id,
op.dt order_dt, op.service_id, pay_services.*
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN dictionary.pay_services ON pay_services.id = op.service_id
WHERE u.reg_dt > '2013-02-02' – условия, применимы к полю из любой таблицы.
ORDER BY u.reg_dt DESC

Для поля gender мы использовали так называемый алиас (иначе говоря, переименовали для нашей результирующей таблицы в sex) u.gender AS sex. Для этого можно использовать слово AS или написать алиас через пробел, работает идентично dt order_dt.

Алиасы применимы и к названиям таблиц с тем же синтаксисом (с AS или без).

Чаще всего алиас для таблицы — это аббревиатура её названия orders_paid op.

Это удобнее, чем название t1, table_1, и позволяет ускорить запись op.user_id вместо orders_paid.user_id.

Несмотря на удобство сокращения, иногда лучше не использовать алиасы, так как это может усложнить понимание скрипта.

Как видно в примере order_id, можно использовать только название поля, без указания таблицы, если это название уникально. Но все же более наглядный и универсальный способ писать таблица.поле.

pay_services.* позволяет вывести все поля из таблицы pay_services.

В результате выполнения запроса получили следующую таблицу:

reg_dt

sex

age

id

user_id

order_id

order_dt

service_id

id

service_name

2014-01-14 21:07:15

m

37

4481548107

4481548107

2460602

2014-04-13 12:10:09

9

9

vip

2014-01-14 21:07:15

m

37

4481548107

4481548107

2460491

2014-04-11 15:11:18

14

14

month

2014-01-14 21:07:15

m

37

4481548107

4481548107

2455378

2014-01-14 21:08:45

18

18

90 day

2014-01-02 21:07:08

f

37

4446556074

NULL

NULL

NULL

NULL

NULL

NULL

2014-01-02 17:30:21

f

37

4446022755

4446022755

2435206

2014-01-02 18:30:01

14

14

month

2014-01-02 17:30:21

f

37

4446022755

4446022755

2437018

2014-02-02 18:25:17

14

14

month

2013-12-30 11:04:15

m

37

4436702697

NULL

NULL

NULL

NULL

NULL

NULL

2013-12-30 8:33:09

m

37

4436447691

NULL

NULL

NULL

NULL

NULL

NULL

Для пользователей, которые совершили несколько покупок, строки из user задублировались.

Для пользователей, у которых нет оплат, мы получили значения NULL. Его нужно понимать как «нет записей в присоединяемой таблице».

Таблицы users и orders_paid связаны через ID покупателя, его называют link’ом (связью). К одной строке в пользователях может находиться несколько (в том числе 0) строк заказов, таким образом можно сказать, что эта связь имеет тип (1:М) один ко многим.

Для того чтобы корректно сопоставить строки таблиц, после ON указываются условия, которые должны выполняться, чтобы строка была добавлена в выборку. В нашем случае мы обозначаем, что строки из таблицы заказов должны быть выведены в тех случаях, когда идентификатор пользователя в таблице регистраций равен идентификатору пользователя в таблице оплат u.id = op.user_id.

Аналогично связаны ps.id = op.service_id за тем исключением, что справочник имеет только по одной записи для каждого идентификатора и не увеличивает количество строк.

Дальнейшее взаимодействие с полученным набором данных не отличается от одной таблицы — выбираем интересующие нас поля, фильтруем и сортируем.

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

SELECT u.reg_dt, u.gender, u.age, u.id, op.order_id
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.order_id IS NULL

reg_dt

gender

age

id

order_id

2013-12-30 11:04:15

m

37

4436702697

NULL

2013-12-30 8:33:09

m

37

4436447691

NULL

2014-01-02 21:07:08

f

37

4446556074

NULL

Причем нам необязательно выводить поле, которое проверяем в WHERE.

Сравнение с NULL производится через IS, ведь NULL — неопределенное значение и не равно само себе. Поскольку если мы используем равенство, то результат сравнения order_id NULL — ложь, даже если order_id примет значение NULL.

Чтобы найти строки для пользователей, которые имели заказы, модифицируем условие WHERE op.order_id IS NOT NULL или используем другой вид соединения таблиц.

LEFT JOIN оставляет все значения таблицы «слева» и добавляет значения из указанной таблицы, если выполняется условие в ON.

INNER JOIN, или JOIN, влияет на обе таблицы и полностью скрывает строку, в которой не выполняется условие ON.

Для нахождения строк, которые имеют соответствие в обеих таблицах, можем использовать JOIN product.orders_paid op ON u.id = op.user_idНужно учитывать особенность INNER JOIN скрывать значения.

Если JOIN’ы используются для горизонтального объединения таблиц, существует способ вертикально объединить выборки с помощью UNION:

SELECT id, country_code
FROM product.users
WHERE id IN (4446556074, 4436447691, 4446022755)
UNION ALL
SELECT id, country_code
FROM product.users
WHERE id IN (4446022755, 4436702697, 4481548107)
UNION ALL произведет вертикальное объединение всех строк.

id

country_code

4446556074

CH

4436702697

CH

4481548107

GE

4436447691

US

4446022755

US

4446022755

US

UNION при этом отбросит дубликаты (за счет поиска дублей выполняется дольше).

id

country_code

4446556074

CH

4436447691

US

4436702697

CH

4446022755

US

4481548107

GE

Оба запроса должны иметь одинаковое количество столбцов и подходящие типы данных для колонок (если в первом запросе цифры в виде int, то они не смогут объединиться с VARCHAR во второй).

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

Типы связей, отличие условий в WHERE и LEFT JOIN

Если перечислить таблицы через запятую в FROM, получим результат как в CROSS JOIN, который сопоставляет строки «каждая с каждой» (количество строк в таблице, будет равно произведению количества строк исходных таблиц). CROSS JOIN при этом более распространенный и явный способ сделать декартово произведение множеств.

SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op

Что эквивалентно:

SELECT u.id, op.user_id, op.order_id
FROM product.users u
CROSS JOIN product.orders_paid op

id

user_id

order_id

4436447691

4446022755

2435206

4436447691

4446022755

2437018

4436447691

4481548107

2455378

4436447691

4481548107

2460491

4436447691

4481548107

2460602

4436702697

4446022755

2435206

...

...

...

В нашем случае для каждого пользователя будут записаны заказы всех пользователей.

Указав в условии WHERE, что ID пользователя должен быть общий для двух таблиц:

SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
WHERE u.id = op.user_id

Получим результат, аналогичный следующему:

SELECT u.id, op.user_id
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id

id

user_id

order_id

4481548107

4481548107

2455378

4446022755

4446022755

2437018

4481548107

4481548107

2460491

4481548107

4481548107

2460602

4446022755

4446022755

2435206

Каждому пользователю отнесены строки его заказов из таблицы orders_paid (исключая пользователей без заказов).

Отличие условий в WHERE и LEFT JOIN

Условие, написанное в LEFT JOIN, влияет только на поля из присоединяемой таблицы:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id AND op.service_id = 14

id

service_id

user_id

4446022755

14

4446022755

4446022755

14

4446022755

4481548107

14

4481548107

4436702697

NULL

NULL

4436447691

NULL

NULL

4446556074

NULL

NULL

Сначала была произведена фильтрация таблицы orders_paid, и остались только все заказы сервиса № 14, затем — объединение. Таким образом получили из users всех зарегистрированных пользователей. Для пользователей, у которых есть заказ сервиса № 14, будут выведены строки из таблицы заказов, для остальных — NULL.

Когда условие написано в WHERE, оно фильтрует выборку уже после объединения таблиц.

До фильтрации:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id

id

service_id

user_id

4446022755

14

4446022755

4446022755

14

4446022755

4436702697

NULL

NULL

4446556074

NULL

NULL

4436447691

NULL

NULL

4481548107

14

4481548107

4481548107

9

4481548107

4481548107

18

4481548107

После фильтрации:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.service_id = 14

id

service_id

user_id

4446022755

14

4446022755

4446022755

14

4446022755

4481548107

14

4481548107

В итоге, несмотря на использование LEFT JOIN, мы получили аналогичное поведение, как в INNER JOIN (в способности скрывать строки, не подходящие под условие одной из таблиц).

Условия в блоке WHERE влияют на всю полученную после объединения выборку, это нужно учитывать. Как видно в примере выше ON u.id = op.user_id AND op.service_id 14, блок ON не ограничивается равенством идентификаторов. Это просто блок с условиями (по тем же правилам, что и в WHERE), которые могут быть разными в зависимости от ситуации.

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

Так же привяжем справочник с названиями сервисов. Используем INNER JOIN для связи с orders_paid, чтобы остались только пользователи с заказами.

SELECT u.id, opp.order_id parent_order, opp.dt AS parent_dt, op.order_id, op.dt AS order_dt, ps.service_name
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN product.orders_paid opp ON op.parent_order_id = opp.order_id
LEFT JOIN product.pay_services ps ON ps.id = op.service_id
ORDER BY op.order_id

id

parent_order

parent_dt

order_id

order_dt

service_name

4446022755

2435206

2014-01-02 18:30:01

2435206

2014-01-02 18:30:01

month

4446022755

2435206

2014-01-02 18:30:01

2437018

2014-02-02 18:25:17

month

4481548107

2455378

2014-01-14 21:08:45

2455378

2014-01-14 21:08:45

90 day

4481548107

2460491

2014-04-11 15:11:18

2460491

2014-04-11 15:11:18

month

4481548107

NULL

NULL

2460602

2014-04-13 12:10:09

vip

Видим, что для инициирующего платежа parent_order = order_id. А сервис vip не имеет parent_order, так как это иной тип сервиса. Таким образом parent_dt — это дата начала подписки, а order_dt — дата конкретного платежа.

Агрегации (distinct и разрезы)

В SQL есть агрегатные функции, которые могут превратить несколько строк в одну:

SUM, COUNT, AVG, MIN, MAX и так далее.

Возвращает одну строку:

SELECT COUNT(u.id)
FROM product.users u

count

8073565

8073565 — количество строк в таблице users.

Функция COUNT позволяет вписать *, чтобы подсчитать количество строк в выборке:

SELECT COUNT(*)
FROM product.users u

В большинстве случаев нам требуется считать величины в каких-либо разрезах, оставляя несгруппированные поля.

Мы можем найти дату первой регистрации и количество зарегистрированных пользователей в каждой стране отдельно для мужчин и женщин (упорядочено от самой большой группы):

SELECT country_code, gender, MIN(reg_dt), COUNT(id) id_count
FROM product.users
GROUP BY country_code, gender
ORDER BY COUNT(id) DESC

country_code

gender

MIN

id_count

CH

f

2014-01-02 21:07:08

983742

GE

m

2014-01-14 21:07:15

464232

CH

m

2013-12-30 11:04:15

534654

US

f

2014-01-02 17:30:21

453645

US

m

2013-12-30 8:33:09

236235

Общее правило будет звучать так: для агрегации данных нужно внести все поля разрезов (поля, что не должны быть агрегированы) в конструкцию GROUP BY, а для полей, что должны быть агрегированы,  выбрать агрегатные функции. Также, разумеется, доступны математические преобразования +, -, *, /.

Если в SELECT указаны только агрегированные выражения, GROUP BY отсутствует.

Если в SELECT все поля без агрегаций и они же в GROUP BY — запрос вернет только уникальные значения.

Важно держать в уме уровень детализации данных, который получается на каждом этапе запроса. Иначе говоря, чему соответствует одна строка данных в результате выполнения запроса.

SELECT u.country_code,
      COUNT(DISTINCT u.id)                                    registered,
      COUNT(DISTINCT op.user_id)                              payers,
      COUNT(DISTINCT op.user_id) / COUNT(DISTINCT u.id) * 100 conv
FROM product.users u
        LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
GROUP BY u.country_code
HAVING COUNT(DISTINCT op.user_id) > 100

Этим запросом мы хотим посчитать процент пользователей, которые совершили хотя бы одну оплату. Для этого нужно количество уникальных пользователей COUNT(DISTINCT u.id), что зарегистрировались, поделить на количество уникальных пользователей из таблицы оплат COUNT(DISTINCT op.user_id). Слово DISTINCT удалит дубликаты.

Подсчет мы проведем отдельно для каждой локации GROUP BY u.country_code.

Из полученной выборки HAVING COUNT(DISTINCT op.user_id) > 100 оставим только строки, что соответствуют условию, в нашем случае это локации, в которых больше 100 плательщиков.

country_code

registered

payers

conv

GE

491871

16164

3.286227486

CH

232116

9128

3.9325165

US

267327

13758

5.146505965

В блоке WHERE указываются условия, относящиеся к данным в исходных таблицах, а в HAVING — условия, которые относятся к результатам агрегации.

Запрос обрабатывается в следующем порядке:

  1. FROM (и JOIN’ы)
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Таким образом сначала произойдет объединение источников данных в одну таблицу, аналогичное выполнению следующего запроса:

SELECT u.country_code, u.id id_from_u, op.user_id id_from_op, op.order_id*
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'

*op.order_id выведено для демонстрации.

Видим, что для пользователей, у которых больше одного заказа, появилось по несколько строчек. И если бы мы использовали COUNT без DISTINCT, то подсчитали бы количество пользователей, умноженное на количество их заказов.

country_code

id_from_u

id_from_op

order_id

US

4446022755

4446022755

2435206

US

4446022755

4446022755

2437018

CH

4436702697

NULL

NULL

CH

4446556074

NULL

NULL

US

4436447691

NULL

NULL

GE

4481548107

4481548107

2460491

GE

4481548107

4481548107

2460602

GE

4481548107

4481548107

2455378

Затем будут проведены агрегации и удаление из списка локаций с малым количеством пользователей HAVING COUNT(DISTINCT op.user_id) > 100.

Агрегатные функции будут подсчитаны, не учитывая значения NULL.

Итог

Здесь мы проанализировали, как извлечь данные из таблиц, корректно их объединять, фильтровать и группировать. Этого достаточно для того, чтобы получать несложные выборки и считать статистики.

В следующей части рассмотрим выделение признаков, подзапросы, работу с временными промежутками, транспонирование и другие приемы, с которыми сталкивается аналитик в работе.

👍ПодобаєтьсяСподобалось17
До обраногоВ обраному22
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Так и возникла идея написать статью, которая сможет последовательно и без воды рассказать, как использовать SQL для получения выборок из нескольких источников с нужными ограничениями, в нужном виде и разрезах.

Дякую за статтю, але коли ви пишете про SQL, потрібно завжди уточнювати — це ANSI SQL (якщо так, то якого року) або розширення (наприклад, T-SQL), то що 100% працювало на вашій СУБД, може цілком собі перестати працювати на інший.

и использую SQL для работы с разными БД.

Я так розумію, ви всі зазначені SQL запити тестували, цікаво, на який СУБД?

Дякую! Звісно, тестував на:
MySQL 5.6
PostgreSQL 9.6
PostgreSQL 9.3
SQLite
Vertica
BigQuery (Standard SQL)
В статті використаний базовий функціонал, спільний для більшості БД, з мінімальними відмінностями. Наприклад в MS SQL Server «LIMIT n» треба замінити на «TOP n» і написати в SELECT. А в Oracle не можна порівняти дату зі строковим типом даних, а треба зробити приведення типу, щось накшталт TO_DATE (‘2020/02/02’, ‘yyyy/mm/dd’). Таким чином вважаю, що питання розбіжності діалектів переоцінене і вирішуються за допомогою google-пошуку або місцевого DBA.

От тільки місцевому дба нічим зайнятися, крім як за вами ваші запити підбирати.

Скрипти важко читаються через різне іменування. Було би добре звести до одного.

Що мається на увазі під «іменування»?

Ну и кому ты это написал? Для того кто знает — это ликбез. Для того кто не знает — булщит крэп

Ну почему, я где-то по подобным описаниям получал начальные сведения типа «с чем это едят», от которых можно было плясать дальше. Просто надо адекватно пометить.

Просто надо адекватно пометить.

Просто надо найти в куче мусора крупицу истины. Собственно говоря, это и есть критерий полезности — чтобы мусора поменьше, пользы побольше, инфа структурирована так, что отвечает типичным шаблонам поиска.

Если этого всего нет — тогда стартовая страница гугла полезнее этого бла-бла-бла. И да, лучше написать 10 маленьких статеек с маленькой пользой, чем одну большую с отрицательной. Конечно, можно сделать магию вроде оглавления и внутренних ссылок... но не царское ж это дело, свой какантент структурировать.

Алексей, я не первый раз наталкиваюсь на ваши комментарии и каждый раз они в духе «обоже, как все плохо» (я тут смягчил) — хочу поинтересоваться, это у вас миссия такая и, может, вы пишите статьи совсем на другом уровне и можете похвастаться?

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

Я не писал «как всё плохо». Если бы вы прочитали, вы бы это знали. Но вы НЕ прочитали, лишь заметили слово-триггер, да и то в лучшем случае. В общем, прочитайте СТАТЬЮ, потом судите.

Нравится когда просто и хештегами → идите на Твитер, вам понравится! И меня там нет :)

Не сомневайтесь — я прочитал и статью, и комментарий)
Но не нашел ответ на мой вопрос, хотя, конечно, вы не обязаны отвечать)

itzik ben gan давно все понятно изложил.

Прорекламирую пару вещей, чтобы от статьи хоть какая-то польза была.
www.sommarskog.se
use-the-index-luke.com

дякую за статтю, можливо можете підсказати як написати швидкий запит з наступними умовами є дві таблиці players і bets у яких спільне поле playerid
потрібно вивести перший запис у таблиці bets для кожного гравця не використовуючи функцію MAX()
Transact-SQL

SELECT *
FROM somes
WHERE some_id IN (
    SELECT (
               SELECT some_id
               FROM somes s
               WHERE s.user_id = u.user_id
               LIMIT 1
           ) AS some_id
    FROM users u
)

Думаю, можно определить первую запись в таблице bets чем-то типа row_number, а дальше джоинится
docs.microsoft.com/...​sql?view=sql-server-ver15

я после Vertica не доверяю таким функциям ))

из доки про last_value:

Due to default window semantics, LAST_VALUE does not always return the last value of a partition.

хотя first_value работает, как нужно

диявол в нюансах)) там ще питання Null рахуєм чи не рахуєм))

тут не дуже зрозуміло, бо не вказано чи є дублі в плеєрах, що означає перший запис (таблиця відсортована?), які ключі і що означає «спільне поле»)) але ви мабуть щось таке шукаєте (діалект T-SQL як просили):

select
player.id
,first_value (bets.id) over (partition by player.id order by bets.id)
from players
left join bets
on players.playerid = bets.playerid

будуть дублікати) треба ще додати group by

select
player.id
,first_value (bets.id) over (partition by player.id order by bets.id)
from players
left join bets
on players.playerid = bets.playerid
group by 1,2

перший — це мається на увазі сортування, чи будь-який? Fetch next використовується разом з Order by

оскільки сильно багато інфи не було надано, то шось тіпа такого

select p.field1, p.field2, bet.field3, bet.field4
from players p
outer apply (
    select top 1 b.field3, b.field4
    from bets b
    where b.playerid = p.playerid
    order by b.field5
) bet (field3, field4)

критично важливо звернути увагу на підзапит — top 1 та order by b.field5 — цей трюк вибере «перший» чи «останній» запис — це з якого боку дивитись

order by весьма дорогая операция. Потому SQL и допиливают выражениями вроде last_value, чтобы применять дешёвую операцию вместо дорогой.

Дорога, якщо без розуму і без індексів використовувати.
Тут вона йде в парі з top 1, що дозволить це оптимізувати у пошук першого/останнього рядка.

order by в підзапитах потенційно проблемна навіть із індексами. Я ж кажу, зроби бенчмарк. Зокрема, по оперативі.

Особисто я для подібних целей створюю окрему таблицю, де вже код бекенду складає потрібну мені статистику, або одночасно із діями юзера, або робить агрегацію щоночі чи щотижня. Не скажу, на чому я вертів ваші «нормальні форми», коли треба оптимізувати швидкість.

Лан, повіримо на слово, ібо спеціально робити бенчмарки ліниво і довго :)
Коли доведеться — поміряю і скажу.

Канешно, що підготовлені дані — це буде супер швидко, на грані з чітерством.
Хоча, я б не плодив лишні таблиці, а зробив би флажки або теги

В IT немає поняття «читерство». Що в бюрократичній теорії зветься «о боже, небо на землю впаде», на практиці, як і у більш фундаментальній теорії, є ефективністю.

Я з того покоління, яке ще пам′ятає обчислення на калькуляторі. І навіть у стовпчик на папері. Тому в мене імунітет проти маразму «нормальних форм», бо вони навіть не зменшують ймовірності помилок, лише перекидають цю ймовірність в інше місце, де їх буде ще важче знайти.

Особливо «весело», що теорія вважає дані абсолютно точними, і сподівається на строго НУЛЬОВУ ймовірність збою як при збереженні даних, так і при їх обробці. Хоча техніка має статистику відмов, і вона далеко не нульова. Маючи агрегати, можна дуже швидко відновити цілісніть системи накопичення даних навіть попри втрату частини первинної інфи, а первинну інфу — неспішно підняти з бекапів. Спробуйте ж швидко підняти з бекапів десятки терабайт, які ще й у досить незручній формі зберігаються, і також можуть бути пошкодженими (і це неможливо перевірити, доки не впишеш в базу).

Аналітику — так. Але на продакшен для типових задач подібне ставити то треба неабияку експертизу мати.

Звучит как задачка из тестового для какого-нибудь parimatch tech

Не из тестового. Просто в работе столкнулся с потребностью проанализировать data. Информацию я получил но долго и коряво через вложенные селекты и функцию MAX().
Нужно разобраться как over partition работает. Походу полезная штука.

Нет ничего плохого во вложенных селектах. Ради интереса попробуй бенчмарк сделай этим запросам, убедишься что они летают. А вот с точки зрения человека как раз вложенные селекты наиболее читабельны.

погугли про віконні функції, тебе цікавить щось таке — www.sqlshack.com/...​dow-functions-sql-server

Якщо таке буде потрібно в реалі, прямо таки зроби ОКРЕМУ таблицю, в якій вестимеш статистику гравця. Бо таблиця ставок — це мільйони записів, а таблиця гравців — в кращому випадку тисяч 10, і там все індексовано по primary key. І в ній собі колекціонуватимеш по кожному гравцю що потрібно, і змінюватимеш цю таблицю під кожну нову забажанку — а їх буде ойдофіга.

Що це дасть: миттєвий доступ до агрегатів. І можливість збирати ці агрегати не тільки онлайново, але й вночі. Бо найсвіжіші дані зазвичай не потрібні. А коли потрібні — ти аналізуватимеш таблицю сирих даних лише за сьогодні, накладаючи результат в коді бекенду (краще), або запитом із арифметикою (також швидко, але гірше читається код людиною).

Це класична задача «MAX() by group», яка в жодному діалекті SQL не вирішується в лоба. Або через сабселект з МАХ(), або через мінорну денормалізацію. Причому другому підходу слід віддавати перевагу, якщо таблиці великі, а частота таких запитів висока.

Денормалізація полягає в тому, що треба заводити другу ідентичну таблицю lastbets, в якій поле playerid матиме унікальний індекс. В основну таблицю писати, як і раніше, а в нову — через «INSERT ... ON DUPLICATE KEY UPDATE ...»

Накладні витрати при цьому трошки зростають (два інсерти замість одного), але зате результат отримується миттєво. Фактично, накладні витрати при використанні однієї таблиці теж немаленькі, але вони «концентровані» у часі, під час власне виборки. При денормалізації ж ці накладні витрати рівномірно розмазуються у часі. База веде себе більш стабільно, без просадок продуктивності.

Треба додати ():

Если мы захотим найти пользователей, что зарегистрировались в конкретном временном диапазоне с доменом ’%.com’ или ’%.us’, напишем запрос:
SELECT reg_dt, gender, age, site
FROM product.users
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01' 
      AND (site LIKE '%.us' OR site LIKE '%.com')

Статтю можна було написати краще, описувати синтаксичний цукор BETWEEN та HAVING було зайве!
Замість HAVING можна було описати вкладені запити.

Треба було вказати назву БД з якою ви працюєте!
Треба було вказати як отримати список таблиць які є в БД!

Будь-ласка зробіть висновки та напишіть продовження краще!

Дякую за відгук!

HAVING зі стандарту SQL, вважаю що важливо було описати цю механіку, її часто використовують аналітики. Вкладені запити будуть в продовженні.

Намагався використати синтаксис, який працюватиме майже на всіх діалектах SQL (LIMIT не буде працювати в T-SQL, деякі запити не працюватимуть в ClickHouse).

Отримати список таблиць в різних БД має свої особливості.

Треба додати ()

а зачем еще «()»?

Треба було вказати назву БД з якою ви працюєте!

Ну на вскидку гайд подходит для старта работы с большинством sql бд — не описано ничего специфического по типу ilike и т.д.

Скобки потому что and имеет приоритет и выполняется раньше or. по этому для того что бы указать порядок вычисления, нужно ставить скобки.

в запросе из примера, который мы обсуждаем, есть скобки, про которые пишите вы и автор первого комментария в этой ветке о_О

Треба додати ():

Определённо лишний элемент, лол

Замість HAVING можна було описати вкладені запити

А заодно и ещё парочку суб-оптимальных или альтернативных решений 👌 (почему не common table expression, кстати?)

Если без сарказма — то на простых примерах (2 таблицы, 1 group by) сложно представить что движок бд / оптимизатор запросов будет оптимизировать разными путями вложенный запрос с фильтрацией и HAVING

Стаття орієнтована на початківців а тому HAVING значно ускладнює статтю.

Якщо замінити HAVING на вкладені запити то це буде значно зрозуміліше для початківців.

HAVING взагалі досить дорога операція, бо зазвичай передбачає створення тимчасової таблиці. Це фактично постфільтрація.

Постфільтрація тимчасової таблиці без індексів.
Worst case possible.

Майже завжди тимчасова таблиця повністю вміщується в пам′яті, а фільтрація HAVING в будь-якому випадку це прохід усієї таблиці. Зазвичай HAVING використовується із агрегатними функціями, де результуючий набір ну дуууже невеликий (рідко сягає навіть тисячі записів), тому про його вартість навіть не згадують. Але факт, що із ним легко начудити, перекинувши у постфільтрацію те, що варто було відфільтрувати за допомогою WHERE.

Підписатись на коментарі