Sql w3schools: W3Schools Online Web Tutorials

Учебник по SQL, примеры для начинающих

Учебник по SQL для начинающих и профессионалов с примерами по основам, структурированию, программированию, языку, синтаксису, базе данных, пунктам, концепциям баз данных, синтаксису SQL, SELECT, INSERT, DELETE, UPDATE, DROP, TRUNCATE, DISTINCT, ORDER BY, GROUP BY, WHERE и т. д.

База данных — это организованный набор данных.

СУБД:

СУБД относится к системе управления базами данных. Это набор программ, которые позволяют хранить, изменять и извлекать данные из базы данных.

СУРБД:

СУРБД относится к системе управления реляционными базами данных. Это система управления базами данных, основанная на реляционной модели, представленной Э. Ф. Коддом. Все современные системы баз данных, такие как MS SQL Server, Oracle, IBM DB2, MySQL и Microsoft Access, основаны на СУБД.

Разница между СУБД и РСУБД:

СУБД	РСУБД
СУБД не определяет никаких ограничений или безопасности для обеспечения ACID PROPERTY. Концепция нормализации отсутствует. В СУБД данные внутренне обрабатываются как файлы. СУБД не поддерживает распределенные базы данных. СУБД поддерживает одного пользователя.	СУРБД определяют ограничение целостности для обеспечения ACID PROPERTY. Присутствует концепция нормализации. В СУБД данные внутренне обрабатываются как таблицы. RDBMS поддерживает распределенные базы данных. РСУБД поддерживает несколько пользователей.

SQL означает язык структурированных запросов. SQL — это компьютерный язык для доступа к базам данных. Он используется для хранения, обработки и извлечения данных, хранящихся в базе данных.

SQL-команды:

SQL-команды — это инструкции, которые используются для взаимодействия с базой данных для выполнения определенной задачи.

Категории команд SQL:

1. DDL (язык определения данных):
Команды DDL используются для определения структуры объектов базы данных.

1. CREATE — используется для создания объектов базы данных, таких как таблица, представление и т. д.

2. ALTER — используется для изменения структуры существующего объекта базы данных.

3. DROP – Используется для удаления существующих объектов из базы данных.

4. TRUNCATE — удаляет все записи из таблицы, включая все места, выделенные для записей.

5. RENAME — используется для переименования существующего объекта базы данных.

2. DML (язык обработки данных):
1. SELECT — используется для извлечения записей из базы данных.

2. INSERT – используется для создания записи.

3. ОБНОВЛЕНИЕ — используется для изменения существующей записи.

4. DELETE – Используется для удаления всех записей из таблицы, место для записей остается.

5. MERGE- используется для вставки или обновления записи.

6. CALL – вызвать подпрограмму PL/SQL или Java.

7. ТАБЛИЦА БЛОКИРОВКИ — используется для управления параллелизмом.

3. DCL (язык управления данными):
1. ПРЕДОСТАВЛЯЕТ — предоставляет пользователю привилегии.

2. REVOKE — отзывает привилегии доступа у пользователя, предоставленного с помощью команды GRANT.

4. TCL (управление транзакциями):
1. COMMIT — используется для сохранения выполненной работы.

2. ТОЧКА СОХРАНЕНИЯ — используется для определения точки в транзакции, к которой можно вернуться позже.

3. ROLLBACK — используется для восстановления исходной базы данных с момента последней фиксации.

Учебник по SQL:

Вопросы для собеседования по SQL:

• Вопросы и ответы для собеседования по SQL

 

Пожалуйста, поделитесь

Оконные функции SQL | Расширенный SQL

Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

• Введение в оконные функции
• Основной синтаксис работы с окнами
• Обычные подозреваемые: SUM, COUNT и AVG
• ROW_NUMBER()
• РАНГ() и DENSE_RANK()
• НТИЛЕ
• LAG и LEAD
• Определение псевдонима окна
• Расширенные методы работы с окнами

В этом уроке используются данные программы Capital Bikeshare Program в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные о поездках на своем веб-сайте. Данные были загружены в феврале 2014 г., но ограничены данными, собранными в течение первого квартала 2012 г. Каждая строка представляет одну поездку. Большинство полей говорят сами за себя, кроме

rider_type : «Зарегистрированный» означает ежемесячное членство в программе совместного использования, «Повседневный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от их исходных форм, чтобы соответствовать форматированию даты SQL — они хранятся в этой таблице как метки времени.

Введение в оконные функции

Документация PostgreSQL прекрасно знакомит с концепцией оконных функций:

А оконная функция выполняет вычисления для набора строк таблицы, которые так или иначе связаны с текущей строкой. Это сравнимо с типом вычислений, которые можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций использование оконной функции не приводит к группированию строк в одну строку вывода — строки сохраняют свои отдельные идентификаторы.

За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.

Наиболее практичным примером этого является промежуточная сумма:

 SELECT duration_seconds,
       SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
  ИЗ tutorial.dc_bikeshare_q1_2012
 

Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.

Основной синтаксис работы с окнами

Первая часть приведенной выше агрегации, SUM(duration_seconds) , очень похоже на любую другую агрегацию. Добавление НАД определяет его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов, в порядке start_time ».

Если вы хотите сузить окно от всего набора данных до отдельных групп в наборе данных, вы можете использовать

PARTITION BY для этого:

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Приведенный выше запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal оно упорядочено по start_time , а промежуточный итог суммируется по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY . В случае, если вы все еще в тупике

ORDER BY , он просто упорядочивает по назначенному столбцу (столбцам) так же, как предложение ORDER BY , за исключением того, что он рассматривает каждый раздел как отдельный. Он также создает промежуточную сумму — без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить приведенный выше запрос без ORDER BY , чтобы понять:

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) КАК start_terminal_total
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

ПРИКАЗ и ЧАСТЬ определяют так называемое «окно» — упорядоченное подмножество данных, по которым выполняются вычисления.

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

GROUP BY .

Практическая задача

Напишите модификацию приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, накопленного пассажирами с каждого start_terminal.

Попробуйте См. ответ

Обычные подозреваемые: SUM, COUNT и AVG

При использовании оконных функций вы можете применять те же агрегаты, что и в обычных обстоятельствах — SUM , COUNT и AVG 9 0164 . Самый простой способ понять это — повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделать

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ BY start_terminal) AS running_total,
       COUNT(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ ПО start_terminal) AS running_count,
       СРЕДНЕЕ (длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ ПО start_terminal) AS running_avg
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

В качестве альтернативы те же функции с ORDER BY :

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total,
       COUNT(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_count,
       СРЕДНЕЕ (длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_avg
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Убедитесь, что вы подключили два предыдущих запроса к режиму и запустили их. Следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.

Практическая задача

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

ПопробуйтеСмотреть ответ

ROW_NUMBER()

ROW_NUMBER() делает именно то, на что похоже — отображает номер заданной строки. Он начинается с 1 и нумерует строки в соответствии с ORDER BY частью оператора окна. ROW_NUMBER() не требует указания переменной в круглых скобках:

 SELECT start_terminal,
       время начала,
       длительность_секунд,
       ROW_NUMBER() OVER (ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ tutorial.
  dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Использование предложения PARTITION BY позволит вам снова начать считать 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:

 SELECT start_terminal,
       время начала,
       длительность_секунд,
       ROW_NUMBER() OVER (РАЗДЕЛ ПО start_terminal
                          ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

РАНГ() и DENSE_RANK()

RANK() немного отличается от ROW_NUMBER() . Например, если вы заказываете по start_time , может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается одинаковый ранг, тогда как ROW_NUMBER() присваивает им разные номера. В следующем запросе вы видите 4-е и 5-е наблюдения для

start_terminal 31000 — им обоим присваивается ранг 4, а следующий результат получает ранг 6:

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       RANK() OVER (РАЗДЕЛ ПО start_terminal
                    ЗАКАЗАТЬ ПО start_time)
              AS ранг
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Вы также можете использовать DENSE_RANK() вместо RANK() в зависимости от вашего приложения. Представьте ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для примера допустим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:

• RANK() даст идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующим результатом будет 5
• DENSE_RANK() по-прежнему будет присваивать всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 — ни один ранг не будет пропущен.

Практическая задача

Напишите запрос, который показывает 5 самых длинных поездок от каждого начального терминала, упорядоченных по терминалам, и от самых длинных до самых коротких поездок в каждом терминале. Ограничение на поездки, совершенные до 8 января 2012 года.

Попробуйте См. ответ

NTILE

Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE(*# сегментов*) . В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого другого количества плиток, которое вы укажете). Например:

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       НЕТ (4) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
          Квартиль AS,
       НЕТ (5) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Квинтиль AS,
       NTILE(100) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         AS процентиль
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

Глядя на результаты запроса выше, вы можете видеть, что столбец процентиль вычисляется не так, как вы могли бы ожидать. Если бы у вас было только две записи и вы измеряли процентили, вы бы ожидали, что одна запись будет определять 1-й процентиль, а другая — 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле. Вы можете увидеть это в результатах для start_terminal 9.0164 31000 — столбец процентиль выглядит просто как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что он правильно вычисляет процентили, потому что для этого start_terminal существует более 100 записей. Если вы работаете с очень маленькими окнами, имейте это в виду и рассмотрите возможность использования квартилей или аналогичных небольших диапазонов.

Практическая задача

Напишите запрос, который показывает только продолжительность поездки и процентиль, в который попадает эта продолжительность (по всему набору данных, а не по терминалам).

Попробуйте См. ответ

LAG и LEAD

Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если вы получили данные в порядке, который имеет смысл. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк — все, что вам нужно сделать, это указать, из какого столбца извлекаться и сколько строк вы хотите извлечь. LAG вытягивает из предыдущих рядов, а LEAD вытягивает из следующих рядов:

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       ЗАДЕРЖКА(длительность_секунд, 1) ПРЕВЫШЕНО
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS отставание,
       ОПЕРЕЖЕНИЕ(длительность_секунд, 1) ПРЕВЫШЕНИЕ
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS ведущий
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

Это особенно полезно, если вы хотите вычислить разницу между строками:

 SELECT start_terminal,
       длительность_секунд,
       duration_seconds -LAG(duration_seconds, 1) ПРЕВЫШЕНО
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         разница в качестве
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

Первая строка столбца разность пуста, так как нет предыдущей строки, из которой можно извлечь. Точно так же использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете обернуть их во внешний запрос, чтобы удалить пустые значения:

 SELECT *
  ОТ (
    ВЫБЕРИТЕ start_terminal,
           длительность_секунд,
           duration_seconds -LAG(duration_seconds, 1) ПРЕВЫШЕНО
             (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
             разница в качестве
      ИЗ tutorial.dc_bikeshare_q1_2012
     ГДЕ start_time < '2012-01-08'
     ORDER BY start_terminal, duration_seconds
       ) суб
 ГДЕ sub.difference НЕ NULL
 

Определение псевдонима окна

Если вы планируете записать несколько оконных функций в один и тот же запрос, используя одно и то же окно, вы можете создать псевдоним.