11177
Изучаем SQL с нуля По всем вопросам @valentin_mascarov Реклама на бирже - https://telega.in/c/SQl_and_DB_Learning № 4974313625
MySQL: MONTHNAME
Функция MONTHNAME в MySQL используется для извлечения названия месяца из даты. Она принимает аргументом дату и возвращает название месяца на текущем языке соответствующей локали.
Синтаксис - MONTHNAME(date)
где date - это дата в формате yyyy-mm-dd или в формате yyyy-mm-dd hh:mm:ss.
Функция MONTHNAME может быть полезна при создании отчетов и графиков, когда вам нужно отобразить данные по месяцам. Она может также использоваться для сортировки данных по месяцам.
Вы можете использовать эту функцию в запросах SELECT, ORDER BY и других операторах SQL.
Ранжирующая функция DENSE_RANK()
- это ранжирующая функция в SQL, которая используется для нумерации строк в результате запроса в соответствии с заданным порядком.
Она работает очень похоже на функцию RANK(), но в отличие от нее, DENSE_RANK() не пропускает номера ранжируемых строк, если они имеют одинаковые значения. Например, если две строки имеют одинаковое значение для ранжирующего параметра, то они получат одинаковый номер в ранжированном результате.
В запросе с фото мы выбираем имена и оценки студентов из таблицы students и ранжируем их по убыванию оценок с помощью DENSE_RANK(). Результат будет содержать три столбца: name, score и rank.
Особенности реализации функции CONCAT в разных языках и СУБД sql
Функция CONCAT используется для объединения строк в SQL. Однако, ее реализация может отличаться в разных языках и СУБД. Рассмотрим некоторые из них:
🟠MySQL
Функция CONCAT может принимать любое количество аргументов. Она объединяет строки в порядке их передачи в функцию. Если хотя бы один из аргументов NULL, то результатом будет NULL.
🟠PostgreSQL
Функция CONCAT эквивалентна оператору ||, который также используется для объединения строк. Она может принимать только два аргумента. Если хотя бы один аргумент NULL, то результатом будет NULL.
🟠Oracle
Функция CONCAT эквивалентна оператору ||, который также используется для объединения строк. Она может принимать только два аргумента. Если хотя бы один аргумент NULL, то результатом будет NULL.
🟠SQL Server
Функция CONCAT может принимать любое количество аргументов и объединяет строки в порядке их передачи в функцию. Если хотя бы один из аргументов NULL, то результатом будет NULL.
🟠Общие рекомендации
При использовании функции CONCAT рекомендуется проверять аргументы на NULL и использовать функцию COALESCE для замены NULL на пустую строку.
Журнал XOR – мастхев для любого программиста. Новости, мемы и много кода.
Присоединяйся к самому большому сообществу айтишников: @xor_journal
Извлекаем имя из адреса электронной почты SQL
Для извлечения имени из адреса электронной почты в SQL можно использовать функции CHARINDEX и LEFT.
В запросе мы используем функцию CHARINDEX для нахождения символа "@" в строке email. Затем мы используем функцию LEFT, чтобы извлечь часть строки до символа "@", которая является именем.
Просто замените your_table на имя вашей таблицы и email на имя столбца, содержащего адреса электронной почты.
Оператор CROSS APPLY
CROSS APPLY - это оператор, который применяет правую таблицу выражения к каждой строке левой таблицы выражения и возвращает только те строки, которые имеют соответствующие значения в правой таблице выражения.
Оператор CROSS APPLY очень похож на оператор JOIN, но в отличие от JOIN, оператор CROSS APPLY может использоваться с табличными функциями, что позволяет более эффективно объединять данные из разных таблиц.
В примере с фото мы используем оператор CROSS APPLY, чтобы присоединить к каждому клиенту его последний заказ. Мы используем табличную функцию, которая возвращает максимальную дату заказа для каждого клиента.
Поиск значений в таблице, которые не совпадают с другой таблицей
Для выполнения поиска значений в таблице, которые не совпадают с другой таблицей в SQL, можно использовать операторы JOIN, NOT IN и NOT EXISTS.
1. JOIN - используется для объединения двух таблиц по заданному условию. Для поиска значений, которые не совпадают, можно использовать LEFT JOIN или RIGHT JOIN, а затем выбрать строки с NULL значениями в столбцах таблицы-присоединения.
2. NOT IN - спользуется для выбора значений из одной таблицы, которые не содержатся в другой таблице.
3. NOT EXISTS - используется для проверки наличия значений в подзапросе и выбора строк, которые не имеют соответствия в другой таблице.
Как исследовать сырые данные, а на их основе находить решения?
⬆️ Ответь на 11 вопросов и проверь, насколько ты готов к обучению на курсе «Аналитик данных» от OTUS —
На курсе ты освоишь мощные навыки анализа данных:
анализ требований + статистика + BI и получишь востребованную профессию. Курс доступен в рассрочку.
✍️ ПРОЙТИ ТЕСТ: https://otus.pw/LrmK/Нативная интеграция. Информация о продукте www.otus.ru
BINARY - MySQL
BINARY - это тип данных, который используется для хранения двоичных данных. Этот тип данных может быть использован для хранения любых данных, которые не могут быть интерпретированы как символы, такие как изображения, звуковые файлы, видео и т.д.
BINARY имеет следующие характеристики:
- Фиксированная длина: Длина поля BINARY всегда фиксирована и задается при создании таблицы. Например, если вы создаете поле BINARY(10), то оно всегда будет занимать 10 байт в таблице, даже если вы вставляете данные меньшего размера.
- Регистрозависимость: Данные типа BINARY регистрозависимы, что означает, что значения 'abc' и 'ABC' будут различаться, даже если они имеют одинаковую длину.
- Сравнение: Для сравнения данных типа BINARY используется бинарное сравнение, то есть двоичный код каждого символа сравнивается отдельно.
- Индексирование: Поля типа BINARY могут быть проиндексированы, что повышает скорость поиска данных.
Все о jsonb_to_recordset в SQL
Функция jsonb_to_recordset в SQL используется для преобразования данных, хранящихся в формате JSON, в таблицы. Она позволяет распаковывать массивы и объекты JSON и превращать их в строки и столбцы в таблице.
Кроме того, jsonb_to_recordset позволяет работать с вложенными объектами и массивами. Для этого нужно использовать функцию jsonb_each, которая распаковывает объекты и массивы JSON в отдельные строки.
Начать заниматься IT можно, не дожидаясь выпускного! Так вы не только освоите уникальные скиллы, но и поймёте, какая профессия вам по-настоящему подходит.
А погружаясь в IT с Яндекс Лицеем, вы сможете попробовать себя в практических задачах и получите актуальные знания от опытных разработчиков.
Сейчас Яндекс Лицей предлагает бесплатное обучение по Python, Go, разработке игр, Machine Learning, Django, анализу данных и Big Data. Можно выбрать годовые офлайн- или онлайн-курсы. В программе — интенсивное погружение в IT, работа над групповыми проектами и много общения с единомышленниками.
В Лицее ждут школьников 8‒11-х классов и студентов ссузов 1-2-х курсов. Скорее переходите по ссылке и читайте подробности
Ранжирующая функция NTILE()
Функция NTILE() является частью языка SQL и используется для разделения результата запроса на определенное количество групп или сегментов. Эта функция принимает один аргумент - количество сегментов на которые нужно разделить результат.
Суррогатные ключи
В базах данных ключ - это поле или набор полей, которые однозначно идентифицируют запись в таблице. Суррогатный ключ - это ключ, который используется вместо естественного ключа, который может быть слишком сложным или неустойчивым для использования в качестве ключа.
Например, в таблице базы данных, содержащей информацию о сотрудниках, естественный ключ может быть сочетанием имени, фамилии и даты рождения. Однако, если имя или фамилия сотрудника изменятся, ключ также должен быть изменен, что может привести к проблемам с целостностью данных. В этом случае использование суррогатного ключа, такого как уникальный идентификатор, может быть более надежным.
Разбор примера с фото:
В этом примере, id является суррогатным ключом, который генерируется автоматически при каждом вставке новой записи. Он гарантирует уникальность каждой записи и не зависит от других полей таблицы.
Существует несколько преимуществ использования суррогатных ключей в SQL:
1. Простота и удобство в использовании.
2. Улучшение производительности при выполнении операций вставки и обновления.
3. Улучшение целостности данных, так как суррогатный ключ не зависит от других полей таблицы.
4. Удобство для программистов при написании запросов, так как они могут использовать суррогатный ключ вместо нескольких полей для поиска конкретной записи в таблице.
JSON_VALUE в Microsoft SQL Server
JSON_VALUE - это функция в Microsoft SQL Server, которая извлекает значение из JSON-объекта по заданному пути.
Синтаксис функции JSON_VALUE выглядит следующим образом: JSON_VALUE (expression, path)
-expression - это JSON-объект, из которого нужно извлечь значение.
- path - это путь к значению внутри JSON-объекта.
Функция JSON_VALUE также может использоваться в фильтрах WHERE.
Разница между using и on в join-запросах
Join-запросы используются для объединения данных из нескольких таблиц в один результат. В MySQL для этого можно использовать конструкции using и on. Обе конструкции используются для указания условий объединения таблиц, но они имеют некоторые различия.
1 - Конструкция using используется, когда объединение происходит по столбцу, имеющему одинаковое имя в обеих таблицах.
2 - Конструкция on используется, когда объединение происходит по каким-то другим условиям.
Конструкция using удобна в случаях, когда объединение происходит по столбцам с одинаковыми именами, а on - в случаях, когда объединение происходит по другим условиям.
#Вопросы_с_собеседования
Что делает команда с фото?
Данная команда SQL используется для получения информации о свободном месте в базе данных.
Пояснение:
Команда выполняет выборку из системной таблицы sys.dm_db_file_space_usage, которая содержит информацию о распределении страниц в файлах базы данных.
Результатом выполнения команды является две колонки:
- free pages - количество свободных страниц в базе данных
- free space in MB - количество свободного места в базе данных, выраженное в мегабайтах.
Результат выполнения данной команды может выглядеть следующим образом:
free pages - 240
free space in MB - 1.875
Некоррелированный подзапрос
Некоррелированный подзапрос в SQL - это подзапрос, который может быть выполнен независимо от внешнего запроса. Он возвращает результат, который используется во внешнем запросе для выполнения операций.
Ключевым моментом в некоррелированном подзапросе является то, что он выполняется только один раз и его результат сохраняется в памяти до тех пор, пока он не будет использован во внешнем запросе.
Использование некоррелированных подзапросов может улучшить производительность запросов, так как они могут быть выполнены быстрее, чем коррелированные подзапросы, которые зависят от внешнего запроса и выполняются для каждой строки во внешнем запросе.
Суррогатные ключи
В базах данных ключ - это поле или набор полей, которые однозначно идентифицируют запись в таблице. Суррогатный ключ - это ключ, который используется вместо естественного ключа, который может быть слишком сложным или неустойчивым для использования в качестве ключа.
Например, в таблице базы данных, содержащей информацию о сотрудниках, естественный ключ может быть сочетанием имени, фамилии и даты рождения. Однако, если имя или фамилия сотрудника изменятся, ключ также должен быть изменен, что может привести к проблемам с целостностью данных. В этом случае использование суррогатного ключа, такого как уникальный идентификатор, может быть более надежным.
Разбор примера с фото:
В этом примере, id является суррогатным ключом, который генерируется автоматически при каждом вставке новой записи. Он гарантирует уникальность каждой записи и не зависит от других полей таблицы.
Существует несколько преимуществ использования суррогатных ключей в SQL:
1. Простота и удобство в использовании.
2. Улучшение производительности при выполнении операций вставки и обновления.
3. Улучшение целостности данных, так как суррогатный ключ не зависит от других полей таблицы.
4. Удобство для программистов при написании запросов, так как они могут использовать суррогатный ключ вместо нескольких полей для поиска конкретной записи в таблице.
#разбор_кода
Найдите идентификаторы продуктов, которые одновременно содержат мало жира и подлежат переработке.
Таблица с идентификаторами - 1 фото.
Пример кода с решением - 2 фото.
Интуиция, лежащая в основе приведенного решения, заключается в использовании простого SQL-запроса для фильтрации строк в таблице Products на основе заданных условий. Запрос выбирает столбец product_id из таблицы, где low_fats - 'Y' и recyclable - 'Y'.
Объяснение:
1. Запрос начинается с указания условия SELECT для получения столбца product_id.
2. В предложении FROM указывается имя таблицы "Продукты", из которой будут выбраны данные.
3. Предложение WHERE используется для применения условий фильтрации. Он проверяет, что значение low_fats равно 'Y' (указывает на низкое содержание жира), а значение recyclable равно 'Y' (указывает на возможность переработки).
4. Результатом запроса будут значения product_id, удовлетворяющие заданным условиям.
Есть ли преимущество в использовании IN вместо множества OR в SQL?
Когда нужно выполнить выборку данных из таблицы, можно использовать операторы IN и OR для задания условий. Однако, есть различия в использовании этих операторов.
🟠Оператор IN позволяет задать несколько значений, которые должны быть найдены в столбце таблицы.
🟠Оператор OR позволяет задать несколько условий, которые должны быть выполнены.
В целом, использование оператора IN более эффективно, чем использование множества операторов OR. Это связано с тем, что при использовании OR, каждое условие проверяется отдельно, что может привести к большому количеству операций. В случае с IN, все значения проверяются одновременно, что позволяет сделать запрос более быстрым и эффективным.
Также, использование оператора IN может упростить написание запроса и сделать его более лаконичным, особенно если нужно проверять большое количество значений.
Как правило, рекомендуется использовать оператор IN, если это возможно, вместо множества операторов OR.
Онлайн перестроение таблицы в PostgreSQL
В PostgreSQL существует возможность выполнения онлайн перестроения таблицы без блокировки записи. Для этого используется расширение pg_repack.
Для начала необходимо установить расширение pg_repack. Это можно сделать с помощью команды:
CREATE EXTENSION pg_repack;
После установки расширения, можно выполнить перестроение таблицы с помощью команды:
SELECT pg_repack('public.{table_name}');
Эта команда выполнит перестроение таблицы без блокировки записи. Однако, если во время перестроения таблицы происходят операции вставки, обновления или удаления, то они могут быть заблокированы до завершения перестроения.
Для выполнения онлайн перестроения таблицы необходимо иметь достаточно свободного места на диске, так как pg_repack создает временные файлы в процессе работы.
JSON_QUERY в Microsoft SQL Server
JSON_QUERY является одним из встроенных операторов, который позволяет извлекать данные из JSON-документов в Microsoft SQL Server. Оператор JSON_QUERY возвращает фрагмент JSON-данных, который удовлетворяет определенному условию.
Синтаксис оператора JSON_QUERY: JSON_QUERY (expression, path)
- expression - это JSON-документ или JSON-фрагмент.
- path - это выражение, которое определяет, какая часть JSON-документа должна быть выбрана.
MySQL: PERIOD_ADD
PERIOD_ADD является одной из функций MySQL, которая используется для добавления количества периодов к указанной дате. Эта функция особенно полезна в тех случаях, когда вам нужно добавить несколько месяцев или лет к дате.
Синтаксис PERIOD_ADD выглядит следующим образом:
PERIOD_ADD(P,N) - где P - это период в формате YYMM или YYYYMM, а N - это количество периодов, которые нужно добавить к указанной дате.
ETCD — это простое в использовании, но при этом высоконадёжное распределённое хранилище для пар ключ-значение. И нужно уметь с ним работать.
Первый подход предлагаем сделать на открытом уроке курса «NoSQL» в Отус 28 августа, в 20.00 (мск).
💬На вебинаре «Введение в ETCD-кластер», который проведет Евгений Аристов, вы изучите:
- цели и задачи ETCD;
- отличия ETCD от прочих аналогичных решений;
- алгоритм Raft;
- основные моменты установки и настройки кластера ETCD.
👉Регистрируйтесь и ставьте событие в календарь: https://otus.pw/wyj0/
🔥Продолжить изучение тонкостей NoSQL вы сможете на самом курсе, который стартует уже в сентябре.
Нативная интеграция. Подробная информация на сайте otus.ru
Преобразование xml-поля в SQL
Для преобразования xml-поля в SQL необходимо выполнить следующие шаги:
1. Создать таблицу в базе данных с полем типа xml.
2. Вставить данные в таблицу.
3. Использовать функции для извлечения данных из xml-поля.
Например, функция value() извлекает значение элемента или атрибута из xml-поля.
4. При необходимости, выполнить другие действия с данными, полученными из xml-поля, с помощью стандартных средств SQL.
REPLICATE в SQL Server
REPLICATE - это функция в SQL Server, которая повторяет входную строку указанное количество раз. Эта функция может быть полезна во многих сценариях, например, при создании тестовых данных или при форматировании вывода.
Синтаксис функции REPLICATE выглядит следующим образом:
REPLICATE ( string_expression , integer_expression )
- string_expression - это строковое выражение, которое нужно повторить.
- integer_expression - это выражение целого типа, определяющее количество раз, которое нужно повторить строку.
Поиск референта заказчика
Напишите SQL-запрос, чтобы вывести в отчет имена клиентов, на которых не ссылается клиент с id = 2.
Верните таблицу результатов в любом порядке.
В решении будет подход с использованием: <>(!=) и IS NULL
Некоторые интуитивно приходят к следующему решению.
SELECT name FROM customer WHERE referee_Id <> 2;
Однако этот запрос вернет только один результат: Zack, хотя есть 4 клиента, которых не направляла Jane (включая саму Jane). Все клиенты, которых вообще никто не приводил (значение NULL в столбце referee_id), не отображаются.
Алгоритм
В MySQL используется трехзначная логика - TRUE, FALSE и UNKNOWN. Все, что сравнивается с NULL, оценивается в третье значение: UNKNOWN. Это "все" включает в себя и само значение NULL! Именно поэтому в MySQL предусмотрены операторы IS NULL и IS NOT NULL, специально проверяющие наличие NULL.
Таким образом, в предложение WHERE необходимо добавить еще одно условие 'referee_id IS NULL'.
🔥Приглашаем на открытый урок по аналитике данных.
Тема: Базовый сбор требований в работе дата-аналитика
Урок пройдёт 23 августа в 20:00. Вебинар приурочен к старту онлайн-курса "Аналитик данных"
⚡️Что разберём на уроке:
-Жизненный цикл задачи в анализе данных;
-Какие вопросы нужно обязательно задавать и как правильно фиксировать ответы;
-Как преодолевать возражения и не заработать ненужный стресс в процессе.
Урок будет полезен:
-Новичкам в области анализа данных, которые задумываются об углублении знаний;
-Специалистам, которые уже делают первые шаги в этой профессии;
-Аналитикам, работающим с подготовкой ручной отчетности.
Продолжить обучение можно на онлайн-курсе "Аналитик данных",
который можно приобрести в рассрочку.
💪Пройдите тестирование и регистрируйтесь на занятие:
https://otus.pw/TLwm/Нативная интеграция. Информация о продукте www.otus.ru
Все о команде COLLATE в SQL
Команда COLLATE в SQL используется для указания правил сортировки и сравнения символов при выполнении операций сравнения в запросах.
Существует несколько вариантов значений, которые можно использовать в команде COLLATE, включая:
- _CS - с учетом регистра
- _CI - без учета регистра
- _AS - с учетом локали (language sensitive)
- _AI - без учета локали (language insensitive)
Примеры:
- Cyrillic_General_CS_AS - с учетом регистра и локали для кириллицы
- Latin1_General_CI_AI - без учета регистра и локали для латиницы
Быстро получаем количество записей в большой таблице
отображение общего кол-ва записей в админках
Первый запрос - возвращает точное количество записей, но может быть медленным.
Второй запрос - возвращает приблизительное количество записей, но быстрее, чем первый запрос. Точность данного запроса выше, чем у следующего, но для его выполнения требуется актуальная статистика по таблице.
Третий запрос - возвращает приблизительное количество записей, но быстрее, чем первый запрос.
Точность третьего запроса ниже, чем у предыдущего, но для его выполнения не требуется актуальная статистика по таблице. Преимуществом данного подхода является возможность задавать условие выборки.