SQL Pro. SQL для профессионалов.

Решение сегодняшней задачи на логику и мышление. Вместо знака вопроса должно стоять число 179. Если двигаться по часовой стрелке начиная с 3, то каждое последующее число равно удвоенному предыдущему, к которому прибавили 1, 3, 5, 7, 9. 3 × 2 + 1 = 7. 7 × 2 + 3 = 17. 17 × 2 + 5 = 39. 39 × 2 + 7 = 85. 85 × 2 + 9 = 179. #логика

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

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

    
        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

Трюк дня. Извлечение имени, отчества и фамилии из строки с полным именем В столбце 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

Трюк дня. Сравнение с предыдущим. Решение. Решение: использование 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

Трюк дня. Найти медиану Предположим, есть таблица, содержащая в столбце 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

Вакансия VBA/SQL‑разработчика в Контур ☁️ Контур — продуктовая экосистема, которой пользуется каждый третий бизнес в России. Мы создаем решения для автоматизации процессов: электронный документооборот, отчетность, сервисы для видеоконференций и другое. Сейчас мы ищем опытного VBA/SQL-программиста в отдел внутренней разработки продукта Контур.Персонал. Его поддерживают и развивают для внутренних нужд Контура. 🔘Задачи — Настраивать и дорабатывать информационную систему по учету кадров. — Оптимизировать производительность сервисов: выявлять узкие места, проводить рефакторинг кода, настраивать базы данных и API для повышения скорости и стабильности работы. — Участвовать в архитектурных обсуждениях и код-ревью, быть наставником. 🔘Мы ожидаем — Опыт работы в VBA, базовые знания SQL. — Знание принципов ООП на практике. — Опыт проектирования и внедрения технических решений. — Знание инструментов оптимизации производительности сервиса. Будет плюсом — Опыт в C-подобном языке программирования: C, C++, C#. — Опыт работы с Access и Excel. — Знание предметных областей: кадры, зарплата, документооборот. 🔘Мы предлагаем — Удаленка в РФ или гибрид на выбор — Сильное инженерное сообщество: раз в год проводим внутреннюю конференцию Конфур для 2000 разработчиков с разным бэкграундом; — Развитие: поддерживаем участие в конференциях, митапах и обучающих проектах. Наши деврелы помогут написать статью на Хабр или подготовиться к выступлению; — Ежегодное ревью по матрице компетенций и пересмотр дохода; — Приятные бонусы крупной аккредитованной IT-компании: ДМС, частичная оплата спорта и питания. ➡️ Смотрите условия и откликайтесь на сайте