Трюк дня. Извлечение имени, отчества и фамилии из строки с полным именем

В столбце fullname таблицы customers содержится имя, отчество и фамилия в следующем формате: 'name middlename surname'.

Напишите MySQL-запрос, который извлечет name, middlename и surname в отдельные столбцы. Если middlename не существует, то в результате в middlename должен быть NULL.

Например, из 'Alena Igorevna Petrova' должны получиться 3 столбца:
• name = 'Alena',
• middlename = 'Igorevna',
• surname = 'Petrova'.

А из строки 'Jack Powers' получится следующее:
• name = 'Jack',
• middlename = NULL,
• surname = 'Powers'.

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

#tips

Трюк дня. Извлечение имени, отчества и фамилии из строки с полным именем. Решение.

SELECT
SUBSTRING_INDEX (SUBSTRING_INDEX (fullname, ' ', 1), ' ', -1) AS name,
IF (LENGTH (fullname) - LENGTH (REPLACE (fullname, ' ', '')) > 1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX (fullname, ' ', 2), ' ', -1), NULL) 
           AS middlename,
SUBSTRING_INDEX (SUBSTRING_INDEX (fullname, ' ', 3), ' ', -1) AS surname
FROM customers;

#tips

Трюк дня. SQL - Сортировка зеркальных строк

Есть БД со следующей структурой:

id team1      team2
1  spartak    csk
2  csk        spartak
3  real       spartak
4  csk        real

Нужно написать запрос что-бы узнать сколько раз команды играли между собой в виде:

game          num
spartak-csk   2
real-spartak  1
csk-real      1

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

#tips

Трюк дня. Получить id покупки с максимальной value для каждого customer.

Например, есть таблица:

id customer value
1 Alex 5
2 Sam 1
3 Sam 3

В результате нужно получить следующее:

id customer value
1 Alex 5
3 Sam 3

В PostgreSQL можно использовать оператор DISTINCT ON:


SELECT
DISTINCT ON(customer)
id,
customer,
value
FROM table
ORDER BY customer, value DESC, id


#tips
А какие варианты решения вы знаете?

Трюк дня. Найти записи с граничными значениями без соединения таблицы на саму себя

Один из вариантов - это сначала найти максимальное значение для каждого ID с помощью GROUP BY, и затем соединить таблицу с самой собой по ID и max значению.
Но есть способ лучше:

SELECT
dat2.*
FROM
(
SELECT
dat.*,
CASE WHEN (num = MAX(num) OVER (PARTITION BY id)) THEN 'y' ELSE 'n' END AS max_num
FROM current_table dat
) dat2
WHERE max_num = 'y'

#tips

Трюк дня. Удаление лишних пробелов в строке с использованием REGEX.

Предположим, есть строка, содержащая такой текст:
This is a test string


Требуется удалить из строки лишние пробелы.


Решение:
Используйте функцию REGEXP_REPLACE:

SELECT
REGEXP_REPLACE('This is a test string','( ){2,}',' ');

#tips

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

Напишите SQL-запрос для получения из таблицы (employee) с зарплатами записи, содержащие вторую по размеру заработную плату.

Например, для таблицы на картинке выше такой запрос должен вернуть 200. Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null.

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

#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

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

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

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

#tips

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

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

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

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

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

#tips

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

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

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

#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