Karpov Flashcards
(40 cards)
Сортировка
Для сортировки значений по какой-либо колонке в SQL используется оператор ORDER BY с ключевыми словами ASC (по возрастанию) или DESC (по убыванию).
Алиасы
При составлении SQL-запросов колонкам в результирующей таблице можно присваивать любые другие имена (их ещё называют «алиасами»). Это можно делать с помощью оператора AS:
SELECT name AS new_name
FROM table
Функция вывода длины строки
Функция LENGTH выполняет довольно простую задачу — подсчитывает количество символов в поданном ей на вход значении текстового типа (текстовый тип данных ещё часто называют строкой — от англ. «string»). Иными словами, функция LENGTH измеряет длину некоторой строки в символах:
SELECT LENGTH(column) AS column_length
FROM table
Функция вернуть часть строки
LEFT — возвращает первые n символов в строке:
SELECT LEFT(‘karpov.courses’, 6)
Результат:
karpov
Функция разбить строку на части и вернуть одну из частей
Функция SPLIT_PART разбивает поданную ей на вход строку на несколько частей в соответствии с указанным разделителем и возвращает одну из частей.
Помимо самой строки (или колонки со значениями строкового типа) функция принимает ещё два аргумента — разделитель и порядковый номер части, которую необходимо вернуть. Посмотрите на следующий пример:
SELECT SPLIT_PART(‘karpov.courses’, ‘.’, 2)
Результат:
courses
Способы изменения типа данных
1) На вход функции CAST необходимо подать имя колонки, указав через AS тип данных, к которому нужно привести все значения:
SELECT CAST(column AS VARCHAR)
FROM table
2) Также изменить тип данных можно с помощью специального синтаксиса с двумя двоеточиями («::»):
SELECT column::VARCHAR
FROM table
Функция, с помощью которой можно соединять в одну строку значения из нескольких столбцов.
Функция CONCAT принимает на вход несколько аргументов и возвращает результат их последовательного сложения друг с другом. Хорошая аналогия — составление предложений из разных карточек со словами:
SELECT CONCAT(‘SQL’, ‘ ‘, ‘Simulator ‘, 2022)
Результат:
SQL Simulator 2022
Функция, извлечь часть из даты (день, месяц, год и пр.)
SELECT DATE_PART(part, column)
На месте part необходимо в кавычках указать ту часть, которую нужно извлечь: ‘year’, ‘month’, ‘day’, ‘hour’ и т.д. На месте column следует указать нужную колонку либо конкретную дату или время. Можете самостоятельно запустить в Redash следующие запросы:
SELECT DATE_PART(‘year’, DATE ‘2022-01-12’)
Результат:
2022.00
SELECT DATE_PART(‘month’, DATE ‘2022-01-12’)
Результат:
1.00
SELECT DATE_PART(‘day’, DATE ‘2022-01-12’)
Результат:
12.00
SELECT DATE_PART(‘hour’, TIMESTAMP ‘2022-01-12 20:31:05’)
Результат:
20.00
SELECT DATE_PART(‘minute’, TIMESTAMP ‘2022-01-12 20:31:05’)
Результат:
31.00
Функция COALESCE
Работу COALESCE можно описать следующим образом: она буквально читает список значений слева направо и, как только видит значение, которое не является NULL, сразу же возвращает его и прекращает чтение списка. Посмотрите внимательно на следующие примеры:
SELECT COALESCE(NULL, ‘I am not NULL’ , ‘karpov.courses’)
Результат:
I am not NULL
Арифметические операторы: остаток от деления, возведение в степень
% Остаток от деления
^ Возведение в степень
Округление вещественных чисел
SELECT ROUND(100.5454, 2)
Результат:
100.55
Условная конструкция CASE
Синтаксис:
CASE
WHEN logical_expression_1 THEN expression_1
WHEN logical_expression_2 THEN expression_2
…
ELSE expression_else
END AS case_example
Пример:
SELECT name,
CASE
WHEN name=’свинина’ OR name=’баранина’ OR name=’курица’ THEN ‘мясо’
WHEN name=’треска’ OR name=’форель’ OR name=’окунь’ THEN ‘рыба’
ELSE ‘другое’
END AS сategory
FROM table
Фильтрация по шаблону
Для фильтрации по колонкам с текстовыми значениями в SQL предусмотрен оператор LIKE.
Оператор LIKE не просто сравнивает строки на полное совпадение (или несовпадение), а проверяет их на соответствие заданному шаблону: если строка ему соответствует, то возвращается TRUE, в противном случае — FALSE. Как можно догадаться, конструкция NOT LIKE работает с точностью до наоборот.
Шаблоны могут содержать как обычные символы, так и символы-шаблоны: знак процента ( % ) и подчёркивание ( _ ). Подчёркивание подменяет любой одиночный символ, а знак процента — любую (в том числе и пустую) последовательность символов:
SELECT ‘karpov.courses’ LIKE ‘karpov%’
Результат:
true
SELECT ‘karpov.courses’ LIKE ‘karpov_’
Результат:
false
SELECT ‘karpov.courses’ LIKE ‘%karpov%’
Результат:
true
SELECT ‘karpov.courses’ LIKE ‘_karpov%’
Результат:
false
SELECT ‘karpov.courses’ LIKE ‘%.%’
Результат:
true
SELECT ‘karpov.courses’ LIKE ‘.’
Результат:
false
SELECT ‘karpov.courses’ LIKE ‘Karpov%’
Результат:
false
Обратите внимание на последний пример: оператор LIKE чувствителен к регистру.
Отобрать значения из некоторого списка или диапазона
В этом случае в логическом выражении после ключевого слова WHERE можно использовать операторы IN и BETWEEN. Для получения обратного результата в сочетании с операторами IN и BETWEEN можно использовать оператор NOT.
1) Оператор IN проверяет, соответствует ли значение в колонке одному из значений из заданного списка. Иными словами, он проверяет, входит ли значение в этот список:
SELECT column_1, column_2
FROM table
WHERE column_1 IN (‘product_1’, ‘product_2’, ‘product_3’)
2) В свою очередь оператор BETWEEN позволяет отобрать данные, относящиеся к некоторому интервалу. При этом границы интервала включаются:
SELECT column_1, column_2
FROM table
WHERE column_2 BETWEEN 5 AND 10
NULL: проверка на NULL, сравнение NULL с другими значениями
Для проверки на NULL значения в SQL есть оператор IS NULL. В сочетании с WHERE записывается он так:
SELECT column_1, column_2
FROM table
WHERE column_1 IS NULL
Если же, наоборот, необходимо отобрать не NULL значения, то дополнительно используется оператор NOT:
SELECT column_1, column_2
FROM table
WHERE column_1 IS NOT NULL
Как мы уже знаем, в мире данных NULL означает отсутствие информации. NULL — это не какая-то величина, и поэтому её нельзя сравнить с чем-либо ещё. Результатом сравнения NULL с любым другим значением будет тот же NULL. Более того, NULL не получится сравнить и с другим NULL, потому что в таком случае будут сравниваться две неопределённости и нельзя наверняка сказать, равны они или нет.
Попробуйте самостоятельно запустить в Redash следующие запросы и посмотрите на их результат:
SELECT NULL = NULL
Результат:
NULL
SELECT NULL IS NULL
Результат:
true
SELECT 100 = NULL
Результат:
NULL
SELECT 100 IS NULL
Результат:
false
Вывести несколько уникальных колонок
Ключевое слово DISTINCT можно применять не только к одной колонке, но и сразу к нескольким.
Например, запрос для двух колонок будет выглядеть так:
SELECT DISTINCT column_1, column_2
FROM table
В таком случае в качестве результата запрос вернёт уникальные комбинации значений в колонках.
Агрегирующие функции (определение и основные)
Агрегирующими функциями называют функции, которые обрабатывают определённый набор строк и возвращают одно обобщающее значение. Если вы когда-нибудь работали в Excel, то наверняка сталкивались с подсчётом суммы или максимального/минимального значения по столбцу — речь идёт именно об этом.
Вот несколько примеров таких функций в SQL:
- COUNT — считает количество значений в колонке.
- SUM — вычисляет сумму значений.
- AVG — вычисляет среднее значение.
- MAX — вычисляет максимальное значение.
- MIN — вычисляет минимальное значение.
Различия count(*) и count(column_name)
Важно учитывать один нюанс: запрос со звёздочкой возвращает количество вообще всех записей в таблице, а запрос с указанием столбца — количество тех записей, где в заданном столбце значения не являются NULL.
Таким образом, если в некоторой колонке column есть пропуски, выражения COUNT(*) и COUNT(column) вернут разные значения.
Функция посчитать длину массива
Функция array_length вычисляет количество элементов в массиве (длину массива) и записывается следующим образом:
SELECT array_length(ARRAY[1,2,3], 1)
Результат:
3
Синтаксис может показаться вам немного сложным, но это только на первый взгляд! Давайте разбираться.
ARRAY[1,2,3] — это некоторый список из трёх значений: 1, 2 и 3.
Единица в качестве второго аргумента — это размерность массива, по которой считается его длина. Так как список у нас одноразмерный (просто значения, записанные в одну строчку), то выбор у нас невелик — можем указать только первую размерность.
Функция Age()
Функция AGE возвращает разницу между двумя значениями, представленными в формате TIMESTAMP. При этом из первого значения вычитается второе, а сама разница получается в формате INTERVAL:
SELECT AGE(‘2022-12-12’, ‘2021-11-10’)
Результат:
397 days, 0:00:00
Таким образом, с ‘2021-11-10’ по ‘2022-12-12’ прошло ровно 397 дней.
Если в качестве первого аргумента не указать ничего, то на место первой даты автоматически подставится текущая дата (полночь текущего дня, т.е. начало дня).
Если сегодня ‘2022-12-12’, то с ‘2021-11-10’ прошло ровно столько же дней, сколько в примере выше:
SELECT AGE(TIMESTAMP ‘2021-11-10’)
Результат:
397 days, 0:00:00
На самом деле текущей дате соответствует значение current_date, которое можно указывать в качестве аргумента функции AGE:
SELECT AGE(current_date, ‘2021-11-10’)
Результат:
397 days, 0:00:00
Можете самостоятельно запустить два запроса — с current_date и без — и сравнить полученные результаты. В вашем случае это будут новые результаты, но они должны совпасть.
А само значение current_date можно вызвать так:
SELECT current_date
Результат:
12/12/22
И ещё один нюанс: чтобы результат отображался не в виде количества дней, а в более удобном формате, можно переводить результат вычислений в тип VARCHAR:
SELECT AGE(current_date, ‘2021-11-10’)::VARCHAR
Результат:
1 year 1 mon 2 days
Агрегация расчетной колонки
Аргументом агрегирующей функции может быть и более сложная расчётная колонка — например, полученная в результате работы конструкции CASE.
В таком случае сама конструкция CASE помещается внутрь скобок агрегирующей функции:
AVG(
CASE
WHEN logical_expression_1 THEN expression_1
WHEN logical_expression_2 THEN expression_2
ELSE expression_else
END
)
Так, если бы в нашей таблице все товары были разбиты по категориям и мы захотели бы посчитать среднюю цену товаров с учётом повышающих или понижающих коэффициентов для каждой категории, то мы могли бы сделать это, например, следующим образом:
SELECT AVG(
CASE
WHEN category=’мясо’ THEN price0.95
WHEN category=’рыба’ THEN price0.9
WHEN category=’напитки’ THEN price*1.05
ELSE price
END
) AS avg_price
FROM products
Агрегатные выражения с фильтрацией
Если после агрегирующей функции указать ключевое слово FILTER и поместить в скобках некоторое условие condition после WHERE, то агрегирующей функции на вход будут поданы только те строки, для которых условие фильтра окажется истинным.
В общем виде эта конструкция выглядит так:
SELECT agg_function(column) FILTER (WHERE condition)
FROM table
Например, если бы мы захотели посчитать среднюю цену только для товаров категории ‘рыба’, то запрос выглядел бы так:
SELECT AVG(price) FILTER (WHERE category = ‘рыба’) AS avg_fish_price
FROM table
Обратите внимание: это очень похоже на обычную фильтрацию с агрегацией, которую мы рассматривали в предыдущих задачах, только в данном случае условие на отбор записей указывается сразу в блоке SELECT.
Преимущество такой записи в том, что она позволяет проводить расчёты без промежуточных запросов с условиями в блоке WHERE.
Как получить уникальные значения (не DISTINCT)
Если не указать агрегирующую функцию, то запрос вернёт уникальные значения в столбце (или уникальные комбинации значений в столбцах), т.е. тот же результат, что и оператор DISTINCT. Можете самостоятельно запустить следующие запросы в Redash и убедиться:
SELECT user_id
FROM user_actions
GROUP BY user_id
Отличие операторов WHERE и HAVING
первый фильтрует записи в таблице перед группировкой и агрегацией, а второй — после. Кстати, это простой, но довольно популярный вопрос на собеседовании по SQL.
