SQL и Базы Данных

Функции SQL для символьных данных

В этом видеоуроке автор подробно разбирает процесс работы с символьными (строчными) функциями в SQL.

00:00 Вступление
00:18 Преобразование строки в нижний регистр (LOWER)
00:32 Преобразование строки в верхний регистр (UPPER)
00:55 Получение длины указанной строки (LENGTH)
01:06 Вернуть символ, заданный числовым кодом (CHR)
01:28 Соединить вместе две и более строки (CONCAT)
01:57 Удалить символ с начала или конца строки (TRIM)
02:22 Посимвольная замена значений (TRANSLATE)
03:25 Замена слова в строке другим словом (REPLACE)
04:03 Извлечь часть данных из строки (SUBSTR)

Смотреть это видео на youtube: youtu.be/myYvKpjquGw

Трюк дня. Найти медиану

Предположим, есть таблица, содержащая в столбце sales такие записи:
2, 1, 3, 8, 7, 5.

Необходимо найти медиану для данного столбца.


Решение:
В PostgreSQL используйте функцию percentile_count:

SELECT percentile_cont (0.5) WITHIN GROUP (ORDER BY sales) FROM table;

В MS SQL также используйте функцию percentile_count:

SELECT percentile_cont (0.5) WITHIN GROUP (ORDER BY sales) OVER (PARTITION BY 1) FROM table;

В PARTITION BY можно внести столбец, по которому группируются данные. В данном случае мы использовали 1, чтобы посчитать медиану по всем данным столбца.

В Oracle используйте функцию MEDIAN:
SELECT MEDIAN (sales) OVER (PARTITION BY 1) FROM table;

Однако в MySQL подобных функций нет. Поэтому медиану нужно вычислять самостоятельно.
Напомним: медиана - это число, которое находится в середине набора чисел, отсортированных по возрастанию.

/*задаем переменную row_index = -1, чтобы отсчет индекса начался с 0 */
SET @row_index := -1;

/*находим среднее двух значений в центре отсортированного набора. Охватывает те случаи, когда общее число записей четное*/
SELECT AVG (subq.sales) as median_value
FROM (
    SELECT @row_index:=@row_index + 1 AS row_index, sales
    FROM table
    ORDER BY sales
  ) AS subq
  WHERE subq.row_index 
/*выбираем только значения в центре: одно если число записей нечетное и два если четное*/
  IN (FLOOR(@row_index / 2) , CEIL(@row_index / 2));

Ответ: медиана = 4.

#tips

Трюк дня. Выборка записей из таблицы при различных условиях. Решение

SELECT
DAT.*
FROM customer DAT
WHERE DATEDIFF(mod,trans)>=
CASE WHEN type IN('Sal', 'Adv') THEN 365
WHEN type IN('Cus') THEN 14
ELSE NULL END;

#tips

Трюк дня. Выборка записей из таблицы при различных условиях

Из MySQL таблицы customers выберите только тех, которые:

• имеют тип = ‘Sal’ или ‘Adv’ И разница между датами mod и trans >=365 дней;
• имеют тип = ‘Cus’ И разница между датами mod и trans >=14 дней.

Исходная таблица:
type mod trans
Cus 2022-01-01 2022-12-01
Sal 2022-08-01 2021-05-01
Adv 2022-05-01 2022-04-01
Cus 2022-05-01 2022-04-01

Ожидаемые результат:
type mod trans
Sal 2022-08-01 2021-05-01
Cus 2022-05-01 2022-04-01

Решение будет вечером.

#tips

Трюк дня. Выбрать значения, которые соответствуют одному из нескольких шаблонов. Решение

SELECT *
FROM customers
WHERE phone LIKE ANY('{%+011%,%+044%,%+099%}');

#tips

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

Из PostgreSQL таблицы customers выберите только тех пользователей, текстовое поле phone которых содержит либо +011, либо +044, либо +099.

Решение будет завтра.

#tips

Функции SQL для даты и времени

В этом видеоуроке автор подробно разбирает функции SQL, необходимые для работы с датой и временем.

00:00 Вступление
00:27 Получение системной даты и времени (SYSDATE)
01:05 Преобразовать строку в дату (TO_DATE)
01:59 Получить последний день месяца (LAST_DAY)
02:28 Добавить N-ое количество месяцев (ADD_MONTHS)
02:57 Извлечь год, квартал, месяц (EXTRACT)
03:14 Получить первый день года, квартала, месяца или недели (TRUNC)

Смотреть это видео на youtube: youtu.be/_6XWUJ2zf8Y

Трюк дня. Простые числа. Решение.

Простое число - натуральное (целое положительное) число, имеющее ровно два различных натуральных делителя — единицу и самого себя.

Для начала создадим набор чисел от 2 до 1000 с помощью функции generate_series.

Затем с помощью NOT EXISTS (которое можно, модифицировав, заменить на JOIN) мы соединяем таблицу саму на себя и затем выбираем только те значения, для которых ни одно деление на меньшее число не дает остаток 0.

WITH x AS (
SELECT * FROM generate_series( 2, 1000 ) x
)
SELECT x.x
FROM x
WHERE NOT EXISTS (
SELECT 1 FROM x y
WHERE x.x > y.x AND x.x % y.x = 0
);

#tips

Трюк дня. Простые числа

Напишите PostgreSQL-запрос, который возвратит простые числа от 2 до 1000.

Решение будет вечером.

#tips

Трюк дня. Получить список аккаунтов в MySQL с помощью запроса

Используйте код:

SELECT User FROM mysql.user;

Результат будет следующего вида :

+------+
| User |
+------+
| root |
+------+
| user2 |
+------+

#tips
А как можно получить список аккаунтов в других СУБД?

Трюк дня. Ранг без RANK(). Решение.

Соединим таблицу саму на себя, сравнивая score.
Подсчитаем все уникальные значения объединённых score. Это и даёт эквивалент DENSE_RANK().

SELECT s1.score, count(DISTINCT s2.score) AS score_rank
FROM scores s1
JOIN scores s2
ON s1.score <= s2.score
GROUP BY s1.id, s1.score
ORDER BY 1 DESC

#tips

Трюк дня. Ранг без RANK()

Напишите SQL-запрос, который будет ранжировать scores из таблицы scores без использования оконной функции. Одинаковые scores должны иметь одинаковый ранг. Ранг, следующий за одинаковыми scores, должен быть следущий целым числом (см. ожидаемый результат на картинке выше).

Решение будет завтра.

#tips

Подзапросы в SQL

В этом видеоуроке автор подробно рассматривает работу с подзапросами в SQL. Вы разберете синтаксис подзапросов и узнаете как их использовать.

0:00 Вступление
0:15 Подзапросы
2:13 Значение подзапросов
4:12 Предикаты с подзапросами
4:26 Использование агрегатных функций
5:49 Оператор IN
8:10 Одиночные столбцы
8:35 Выражение
8:58 Предложение HAVING
9:17 Итог

Смотреть это видео на youtube: youtu.be/pIFiw2IjFHE

Трюк дня. Сравнение с предыдущим. Решение.

Решение: использование DATEDIFF

DATEDIFF: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

Если сформулировать обычным языком следующий запрос, то окажется, что он выражает следующую идею: нужно выбрать такие идентификаторы, чтобы температура, соответствующая представляемым ими датам, была бы больше, чем температура на «вчерашние» по отношению к ним даты.

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

#tips

Трюк дня. Сравнение с предыдущим

Напишите SQL-запрос, который находит в таблице weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

Решение будет завтра.

#tips

Трюк дня. Найти второе место по зарплате. Решение.

Решение 1: использование IFNULL и OFFSET

• IFNULL(expression, alt): эта функция возвращает аргумент expression в случае, если он не равен null. В противном случае возвращается аргумент alt. Мы используем эту функцию чтобы возвратить null, если в таблице не окажется искомого значения.
• OFFSET: этот оператор используется с ORDER BY для того чтобы отбросить первые n строк. Используется для получения второго по величине значения.

SELECT
IFNULL(
(SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1
OFFSET 1
), null) AS sec_salary
FROM employee
LIMIT 1


Решение 2: использование MAX

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

SELECT
MAX(salary) AS sec_salary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM employee)

#tips