УПРАЖНЕНИЯ
4.4 Упражнение 1 - фильтрация колонок и строк
Выберите строки из таблицы band, которые в колонке “name" содержат значение 'Led Zeppelin'.
Выведите в результат запроса только колонки “band_id”, “name” и “year”.
Обратите внимание на значение колонки “band_id”.
SELECT band_id, name, year FROM band WHERE name = 'Led Zeppelin';
Для найденного на шаге 1 значения “band_id”, выберите соответствующие строки из таблицы album. Сколько строк вернул запрос?
SELECT * FROM album WHERE band_id = 388;
4.9 Упражнение 2 - вложенные запросы
В предыдущем упражнении 1 мы написали 2 запроса:
• Запрос, выбирающий строки из таблицы band, которые в колонке “name" содержат значение 'Led Zeppelin'.
• Запрос, который для найденного ранее значения “band_id” выбирает соответствующие альбомы из таблицы album.
Теперь объедините эти два запроса в единый запрос: выберите строки из таблицы альбомов album, для которых колонка “band_id” принимает значения, соответствующие значениям “band_id” из таблицы band, где колонка name принимает значение 'Led Zeppelin'.
SELECT * FROM album WHERE band_id IN (SELECT band_id FROM band WHERE name = 'Led Zeppelin');
5.4 Упражнение 3 - работа со значениями NULL
Выберите из таблицы band музыкальные группы с названием Icarus.
Это яркий пример музыкальных групп разных лет и разных стран, имеющих одно и то же название.
SELECT * FROM band WHERE name = 'Icarus'
Выберите из таблицы band только те музыкальные группы с названием Icarus, которые содержат неопределенное значение NULL в колонке “year”.
SELECT * FROM band WHERE name = 'Icarus' AND year IS NULL;
6.8 Упражнение 4 - агрегация данных
Выберите строки из таблицы band, выполните группировку строк по названию группы, и посчитайте количество строк для каждого названия группы. Этот запрос вернёт много строк, в примере ниже приведены лишь первые строки результата. Вы можете увидеть другие строки. потому что без явного ORDER BY строки могут возвращаться в любом порядке.
SELECT name, COUNT(*) FROM band GROUP BY name ORDER BY name;
Добавьте в предыдущий запрос фильтр, чтобы найти только те названия групп, для которых нашлось две и более строки.
SELECT name, COUNT(*) FROM band GROUP BY name HAVING COUNT(*) >= 2 ORDER BY name;
Добавьте в запрос сортировку строк по колонке с количеством строк для каждого названия музыкальной группы. Сделайте сортировку по убыванию этой колонки - так, чтобы наибольшие значения оказались наверху результата запроса.
SELECT name, COUNT(*) FROM band GROUP BY name HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC, name;
Давайте применим аналогичный приём, чтобы найти музыкальные группы с наибольшим количеством альбомов.
Выберите строки из таблицы альбомов album, выполнив группировку данных по номеру музыкальной группы band_id. Для каждого номера музыкальной группы, посчитайте количество альбомов.
Сделайте сортировку данных по второй колонке запроса, по убыванию, чтобы наверху результата запроса оказался номер музыкальной группы с наибольшим количеством альбомов.
Возьмите номер band_id для музыкальной группы с наибольшим количеством альбомов. Найдите её название в таблице band по номеру band_id.
Сначала сделайте это с помощью отдельного запроса к таблице band.
А затем сложное задание - сделайте это с помощью единого запроса к таблицам band и album (подсказка: используйте подзапросы).
SELECT band_id, COUNT(*) FROM album GROUP BY 1 ORDER BY 2 DESC;
SELECT * FROM band WHERE band_id = 562672;
SELECT name FROM band WHERE band_id = (
SELECT band_id
FROM album
GROUP BY band_id
HAVING COUNT(*) = (
SELECT MAX(con)
FROM (
SELECT COUNT(*) as con
FROM album GROUP BY band_id)
AS mypain)
ORDER BY COUNT(*) DESC);
SELECT * FROM band
WHERE band_id IN (
SELECT band_id FROM(
SELECT band_id, COUNT(*)
FROM album GROUP BY 1 ORDER BY 2 DESC LIMIT 1)
);7.3 Упражнение 5 - INNER JOIN
Напишите запрос, выбирающий все колонки из таблицы band с условием name = 'Led Zeppelin'.
SELECT * FROM band WHERE name = 'Led Zeppelin';
Модифицируйте запрос - добавьте в запрос соединение с таблицей album по колонке “band_id”. Выберите в результат все колонки. Обратите внимание, что в результат запроса попадают колонки из обеих таблиц.
SELECT * FROM band as b INNER JOIN album as a ON a.band_id = b.band_id WHERE b.name = 'Led Zeppelin' ORDER BY a.year;
7.10 Упражнение 6 - OUTER JOIN
Выберите из таблицы album все альбомы группы Led Zeppelin, выбрав их по условию band_id=388. Отсортируйте данные по возрастанию колонки year. Обратите внимание, что в 1969 году они выпустили сразу два альбома.
SELECT * FROM album WHERE band_id = 388 ORDER BY year;
Сделайте в предыдущем запросе группировку по колонке year, чтобы посчитать количество альбомов в каждом году. Отсортируйте результат по колонке year.
SELECT year, COUNT(*) FROM album WHERE band_id = 388 GROUP BY year ORDER BY year;
Давайте сделаем список лет непрерывным.
Для этого сначала напишите отдельный запрос - выберите из таблицы calendar_year все строки, где колонка year принимает значения между 1969 и 1982.
Отсортируйте результат по колонке year.
SELECT * FROM calendar_year WHERE year BETWEEN 1969 AND 1982;
Теперь пришло время для внешнего соединения таблиц (outer join).
Выполните внешнее соединение таблицы calendar_year и album по колонке year. Выберите LEFT OUTER JOIN или RIGHT OUTER JOIN так, чтобы основной таблицей была таблицы calendar_year.
Из таблицы calendar_year выберите только года от 1969 по 1982.
Из таблицы album выберите альбомы музыкальной группы с номером 388.
Подсказка: условие на выборку номера группы 388 укажите не в условии фильтрации строк WHERE, а добавьте его с помощью AND в условие соединения таблиц.
Отсортируйте результат по колонке year таблицы calendar_year.
Результат должен получиться следующим: все года от 1969 по 1982, вне зависимости от того, есть ли в том или ином году альбомы группы 388. Например, год 1972 есть в таблице присутствует, хотя в этом году у Led Zeppelin не было ни одного альбома.
SELECT * FROM calendar_year as c_y LEFT JOIN album as a ON c_y.year = a.year AND band_id = 388 WHERE c_y.year BETWEEN 1969 AND 1982 ORDER BY c_y.year;
Для каждого года посчитайте количество альбомов, выпущенных группой Led Zeppelin в этом году.
Для этого добавьте в запрос группировку по полю “year" таблицы calendar_year. И подсчитайте количество альбомов в том или ином году.
Отсортируйте результат по колонке year таблицы calendar_year.
Подсказка: для функции COUNT указывайте в скобках не *, а номер альбома из таблицы альбомов. Это позволит получить количество альбомов 0 для тех лет, когда не было ни одного альбома (например, 1972 год).
SELECT c_y.year, COUNT(a.album_id) FROM calendar_year as c_y LEFT JOIN album as a ON c_y.year = a.year AND band_id = 388 WHERE c_y.year BETWEEN 1969 AND 1982 GROUP BY c_y.year ORDER BY c_y.year;
7.14 Упражнение 7 - SELF JOIN
Выберите из таблицы music_instrument инструмент с номером id=1. Отобразите только колонки id и name.
SELECT id, name FROM music_instrument WHERE id = 1;
Добавьте в запрос внешнее соединение с этой же таблицей, чтобы найти все дочерние инструменты для инструмента wind instruments.
Используйте алиасы колонок, чтобы задать дочерним колонкам названия child_id и child_name.
SELECT a.id, a.name, b.id as child_id, b.name as child_name FROM music_instrument as a LEFT JOIN music_instrument as b ON a.id = b.parent_id WHERE a.id = 1;
Для каждой найденной строки, найдите их дочерние инструменты. Используйте дополнительное внешнее соединение с той же самой таблицей.
Для вновь добавленной таблицы используйте названия колонок grandchild_id и grandchild_name.
Отсортируйте строки сначала по колонке name, затем по child_name, и наконец затем по grandchild_name. Это позволит визуально увидеть, какие инструменты относятся к brass, какие к organ и какие к woodwind.
SELECT a.id, a.name, b.id as child_id, b.name as child_name,
c.id as grandchild_id, c.name as grandchild_name
FROM music_instrument as a
LEFT JOIN music_instrument as b ON a.id = b.parent_id
LEFT JOIN music_instrument as c ON b.id = c.parent_id
WHERE a.id = 1
ORDER BY a.name, b.name, c.name;
Сделайте ещё несколько шагов вниз по иерархии музыкальных инструментов, каждый раз добавляя ещё одно внешнее соединение с той же самой таблицей. На каждом шаге выводите результат запроса колонки id и name из вновь добавленной таблицы - назовите их например level_4_id, level_4_name, затем level_5_id, level_5_name и так далее.
Двигайтесь до тех пор, пока на определенном уровне не будет значений NULL по всех строках - это будет означать, что никаких дочерних инструментов уже не нашлось, то есть мы дошли до окончания иерархии.
SELECT a.id, a.name, b.id as child_id, b.name as child_name,
c.id as grandchild_id, c.name as grandchild_name,
d.id as level_4_id, d.name as level_4_name,
e.id as level_5_id, e.name as level_5_name,
f.id as level_6_id, f.name as level_6_name,
g.id as level_7_id, g.name as level_7_name,
h.id as level_8_id, h.name as level_8_name,
i.id as level_9_id, i.name as level_9_name
FROM music_instrument as a
LEFT JOIN music_instrument as b ON a.id = b.parent_id
LEFT JOIN music_instrument as c ON b.id = c.parent_id
LEFT JOIN music_instrument as d ON c.id = d.parent_id
LEFT JOIN music_instrument as e ON d.id = e.parent_id
LEFT JOIN music_instrument as f ON e.id = f.parent_id
LEFT JOIN music_instrument as g ON f.id = g.parent_id
LEFT JOIN music_instrument as h ON g.id = h.parent_id
LEFT JOIN music_instrument as i ON h.id = i.parent_id
WHERE a.id = 1
ORDER BY a.name, b.name, c.name;Там скрин не получится сделать т.к. таблица слишком широкая. Вывод: 8 уровень - последний. На девятом все NULL.
8.5 Упражнение 8 - Операторы множеств (union, except, intersect)
Найдите номера всех музыкальных групп, которые выпустили альбомы с названием Now. Выберите только колонку band_id.
SELECT band_id FROM album WHERE name = 'Now'
Найдите номера всех музыкальных групп, которые выпустили альбомы с названием The Collection.
SELECT band_id FROM album WHERE name = 'The Collection';
А теперь найдите номера музыкальных групп, которые встречаются и в первом, и во втором списке (на шаге 1 и 2).
Выберите в каждом из предыдущих двух запросов только колонку band_id, и найдите пересечение этих двух списков.
В итоге мы найдём номера музыкальных групп, у каждой из которых есть и альбом Now, и альбом The Collection. Можете проверить это с помощью отдельных запросов к таблице album, выбирая альбомы для каждой из найденных музыкальных групп по её номеру.
Сколько нашлось таких музыкальных групп?
SELECT band_id FROM album WHERE name = 'Now' INTERSECT SELECT band_id FROM album WHERE name = 'The Collection'; -- 2
На предыдущем шаге мы получили только номера музыкальных групп.
Давайте также найдём их названия.
Для этого выберите строки из таблицы band, для которых колонка band_id принимает найденные значения.
Подсказка: используйте IN и подзапрос.
SELECT name
FROM band
WHERE band_id IN (
SELECT band_id FROM album WHERE name = 'Now'
INTERSECT
SELECT band_id FROM album WHERE name = 'The Collection'
);
9.6 Упражнение 9 - Функции и Операторы
Найдите музыкальные группы, в названии которых (в любом месте названия) встречается слово magic, маленькими буквами.
Отсортируйте строки по названию.
SELECT * FROM band WHERE name LIKE '%magic%' ORDER BY name
Найдите музыкальные группы, в названии которых (в любом месте названия) встречается слово magic, написанное любыми буквами - всеми строчными буквами, всеми заглавными буквами, первая буква заглавная и остальные строчные и т.д. Например, группы “Automagic" и “Blue Magic” должны попасть в результат запроса. Отсортируйте строки по названию.
Должно найтись 77 таких музыкальных групп.
SELECT * FROM band WHERE lower(name) LIKE '%magic%' ORDER BY name
Выберите из таблицы person только тех музыкальных исполнителей, чьи имена начинаются с заглавной английской буквы.
Подсказка: используйте функцию SUBSTRING.
Подсчитайте количество таких музыкальных исполнителей, и сравните его с общим количеством строк в таблице person.
SELECT COUNT(*), (SELECT COUNT(*) FROM person)
FROM person
WHERE ASCII(SUBSTRING(name, 1, 1)) BETWEEN ASCII('A') AND ASCII('Z');
SELECT COUNT(*) FROM (
SELECT name, SUBSTRING(name, 1, 1) as f_letter
FROM person
WHERE ASCII(SUBSTRING(name, 1, 1))
BETWEEN ASCII('A') AND ASCII('Z')
ORDER BY name
) as table_1
Для каждой заглавной английской буквы посчитайте количество музыкальных исполнителей, чьё имя начинается с этой буквы.
Отсортируйте результат таким образом, чтобы наверху оказались буквы с наибольшим количеством.
SELECT SUBSTRING(name, 1, 1) as f_letter, COUNT(*)
FROM person
WHERE ASCII(SUBSTRING(name, 1, 1)) BETWEEN ASCII('A') AND ASCII('Z')
GROUP BY 1
ORDER BY 2 DESC
Выберите из таблицы person следующих музыкальных исполнителей:
- колонка name начинается с заглавной английской буквы (как на шаге 3 и 4)
- в колонке name содержится пробел (подсказка: используйте LIKE)
Для таких музыкантов возьмите значение из колонки name, и извлеките только символы от начала и до первого пробела. Например, для Bob Ashley возьмите только Bob, для Michelle Nichol возьмите только Michelle и т.д.
Для каждого имени музыканта, посчитайте количество музыкантов с таким именем. Отсортируйте результат так, чтобы сверху оказались самые популярные имена.
SELECT substring(name, 1, POSITION (' ' in name )-1) as first_name,
COUNT(*)
FROM person
WHERE ASCII(SUBSTRING(name, 1, 1)) BETWEEN ASCII('A') AND ASCII('Z')
AND name LIKE '% %'
GROUP BY 1
ORDER BY 2 DESC
10.4 Упражнение 10 - Изменение данных
В таблице band_extended есть колонка n_albums, которая содержит суммарное количество музыкальных альбомов для каждой музыкальной группы. Например, для Metallica в этой таблице одна строка, и в колонке n_albums число 11. А в таблице album для Metallica уже не одна, а одиннадцать строк - по одной строке на каждый альбом.
Давайте проверим, что количество альбомов в этих двух таблицах совпадает.
- найдите сумму колонки n_albums в таблице band_extended, для всех музыкальных групп
- найдите общее количество альбомов в таблице album.
Эти две цифры должны совпадать.
SELECT 'band_extended' as tables, SUM(n_albums) FROM band_extended UNION ALL SELECT 'album' as tables,COUNT(*) FROM album;
Выполните изменение существующих данных (update) - найдите в таблице band_extended строку для Metallica, и поменяйте в колонке n_albums значение 11 на значение NULL.
Обратите внимание, что это изменение нужно внести только в одну строку этой таблицы.
Проверьте результат - выберите эту строку для Metallica с помощью отдельного запроса SELECT.
Также проверьте общую сумму в колонке n_albums для всех строк таблицы.
Она должна отличаться на 11 от суммы, найденной на шаге 2.
Если эти две суммы отличаются на другую величину, то возможно Вы поменяли не одну, а больше строк
UPDATE band_extended SET n_albums = NULL WHERE name = 'Metallica';
Вставьте в таблицу band_extended новую строку со следующими значениями:
- band_id: -100 (минус 100, это число выбираем специально для целей данного упражнения, чтобы точно не совпасть с уже существующими номерами музыкальных групп)
- name: ‘My Test Music Group’
- year: 2000
- comment: ‘My Comment’
- n_albums: 25
- n_songs: 54
Проверьте с помощью отдельного SELECT, что строка была добавлена в таблицу.
Далее проверьте количество строк в таблице. Раньше было 82928. Теперь должно стать 82929.
INSERT INTO band_extended VALUES (-100, 'My Test Music Group', 2000, 'My Comment', 25, 54);
И наконец, удалите из таблицы band_extended строку для музыкальной группы Queen.
После того, как строка удалена, попробуйте выбрать её с помощью отдельного запроса SELECT. Этот SELECT должен вернуть ноль строк - это и будет означать, что строка была успешно удалена.
Проверьте количество строк в таблице. После предыдущего шага было 82929. Теперь должно стать 82928.
DELETE FROM band_extended WHERE name = 'Queen';
11.3 Упражнение 11: CASE
Выполните внутреннее соединение таблиц album и band. Выберите в результат запроса следующие колонки:
- album_id, name из таблицы album
- band_id, name из таблицы band
Также добавьте в этот запрос ещё одну колонку, которая:
- принимает значение 1 для тех альбомов, название которых совпадает с названием музыкальной группы, которая выпустила этот альбом.
SELECT a.album_id, a.name as album_name,
b.band_id, b.name as band_name,
CASE WHEN a.name = b.name THEN 1 ELSE 0 END as res
FROM album as a
INNER JOIN band as b
ON a.band_id = b.band_id
На основе запроса, который Вы написали на предыдущем шаге, посчитайте количество альбомов, названия которых совпадают с названием музыкальной группы, выпустившей этот альбом.
SELECT SUM(CASE WHEN a.name = b.name THEN 1 ELSE 0 END) as sum_res
FROM album as a
INNER JOIN band as b
ON a.band_id = b.band_id;
Напишите другой запрос, который выбирает из таблицы album только те альбомы, названия которых совпадают с названием любой музыкальной группы (необязательно именно той, которая выпустила этот альбом).
Посчитайте количество таких альбомов. Сравните этот результат с количеством альбомов, полученным на шаге 2.
SELECT COUNT(*) FROM album WHERE name IN (SELECT name FROM band);
SELECT
(SELECT SUM(CASE WHEN a.name = b.name THEN 1 ELSE 0 END) as sum_res
FROM album as a
INNER JOIN band as b
ON a.band_id = b.band_id),
(SELECT COUNT(*)
FROM album
WHERE name IN (SELECT name FROM band));
12.3 Упражнение 12: вычисляем проценты
Используя данные таблицы ALBUM_SALES, найдите суммы продаж альбомов (колонка total_sales) отдельно для каждого календарного года. Оконные функции на этом шаге пока не понадобятся.
Отсортируйте данные таким образом, чтобы сверху списка оказались года с наибольшими суммами продаж.
SELECT year, SUM(total_sales) as sales FROM album_sales GROUP BY year ORDER BY sales DESC;
Возьмите запрос, написанный на предыдущем шаге, и на его основе напишите запрос, который для каждого года посчитает не только сумму продаж, но и какой процент эти продажи составляют от общей суммы продаж за все года.
Подсказка: используйте подзапрос.
Отсортируйте данные таким образом, чтобы сверху списка оказались года с наибольшими суммами продаж.
SELECT year, SUM(total_sales) as sales, ROUND(SUM(total_sales) * 100.00 / SUM(SUM(total_sales)) OVER(), 2) as perc FROM album_sales GROUP BY year ORDER BY sales DESC;
12.8 Упражнение 13: ABC-анализ
На основе таблицы ALBUM_PERCENTAGES вычислите кумулятивную сумму для колонки percentage. При вычислении кумулятивной суммы отсортируйте данные так, чтобы первыми оказались альбомы с наибольшими продажами. Назовите новую колонку cumulative_percentage.
Сам результат запроса тоже отсортируйте так, чтобы первыми оказались альбомы с наибольшими продажами.
SELECT album_name, sales, percentage,
SUM(percentage) OVER(ORDER BY percentage DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as cumulative_percentage
FROM album_percentages
ORDER BY percentage DESC;
Возьмите запрос, который Вы написали на предыдущем шаге, и добавьте в него дополнительную колонку category, которая будет вычисляться по следующему алгоритму:
- если колонка cumulative_percentage принимает значения от 0 до 80 включительно, то колонка category равна текстовой константе 'A'
- если колонка cumulative_percentage принимает значения от 80 до 95 включительно, то колонка category равна текстовой константе 'B'
- если колонка cumulative_percentage принимает значения от 95 до 100 включительно, то колонка category равна текстовой константе 'С' Подсказка: используйте подзапрос и CASE.
WITH table_1 as (
SELECT percentage,
SUM(percentage) OVER(ORDER BY percentage DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as cp
FROM album_percentages)
SELECT a_p.album_name, a_p.sales, a_p.percentage,
t1.cp as cumulative_percentage,
CASE WHEN t1.cp < 80 THEN 'A'
WHEN t1.cp BETWEEN 80 AND 95 THEN 'B'
WHEN t1.cp >= 95 THEN 'C' END as category
FROM album_percentages as a_p
LEFT JOIN table_1 as t1
ON a_p.percentage = t1.percentage
ORDER BY percentage DESC;
12.11 Упражнение 14: солнечные дни
Напишите запрос SELECT к таблице WEATHER, в котором для каждого дня выведите в результат запроса погоду в этот день, а также погоду, которая была в предыдущий день.
Отсортируйте результат по дням.
SELECT the_date, weather_type,
LAG(weather_type, 1) OVER(ORDER BY the_date)
as previous_weather_type
FROM weather
ORDER BY the_date
Возьмите запрос, который Вы написали на предыдущем шаге, и добавьте в него дополнительную колонку difference, которая будет вычисляться по следующему алгоритму:
- если погода в текущий день и предыдущий день отличается, то 1
- если погода в текущий день и предыдущий день совпадает, то 0 - если погода в предыдущий день неизвестна, то 0
Такая колонка по сути позволит увидеть дни, когда был переход от одного типа погоды к другому.
SELECT the_date, weather_type, previous_weather_type,
CASE WHEN weather_type <> previous_weather_type
THEN 1 ELSE 0 END as difference
FROM (
SELECT the_date, weather_type,
LAG(weather_type, 1) OVER(ORDER BY the_date)
as previous_weather_type
FROM weather
ORDER BY the_date)
ORDER BY the_date;
А теперь нестандартный шаг - просуммируйте полученную колонку difference, используя кумулятивную сумму. То есть, для каждой строки найдите сумму всех предыдущих значений колонки difference, плюс значение для текущей строки. При вычислениях отсортируйте данные по колонке the_date. Назовите новую колонку cumulative.
Подсказка: используйте ещё один подзапрос. Получится “матрёшка” из нескольких сложенных запросов.
SELECT the_date, weather_type, previous_weather_type, difference,
SUM(difference) OVER (ORDER BY the_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as cumulative
FROM (
SELECT the_date, weather_type, previous_weather_type,
CASE WHEN weather_type <> previous_weather_type
THEN 1 ELSE 0 END as difference
FROM (
SELECT the_date, weather_type,
LAG(weather_type, 1) OVER(ORDER BY the_date)
as previous_weather_type
FROM weather
ORDER BY the_date))
ORDER BY the_date;
В итоге мы получаем интересную картину - колонка cumulative содержит “номер” каждого из периодов времени, когда погода была постоянна. Сначала был один солнечный день, это период с номером 0. Затем был один снежный день, это период с номером 1. Далее три солнечных дня - период номер 2. И так далее.
Наиболее длинный период времени, когда погода была постоянна - это период номер 5, четыре снежных дня. Если же брать только солнечные дни, то самый длинный период - это период номер 2, три солнечных дня.
На предыдущем шаге мы с Вами нашли 5 периодов времени, когда погода была постоянна. Используя запрос с предыдущего шага, напишите запрос, который будет возвращать 5 строк - по 1 строке для каждого периода времени, когда погода была постоянна. Для каждого такого периода посчитайте количество дней.
Назовите колонку с количеством дней number_of_days. Отсортируйте данные по колонке cumulative (по номеру временного периода).
Подсказка: используйте подзапрос и GROUP BY.
SELECT DISTINCT cumulative, weather_type,
COUNT(*) as number_of_days
FROM (
SELECT the_date, weather_type, previous_weather_type, difference,
SUM(difference) OVER (ORDER BY the_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as cumulative
FROM (
SELECT the_date, weather_type, previous_weather_type,
CASE WHEN weather_type <> previous_weather_type
THEN 1 ELSE 0 END as difference
FROM (
SELECT the_date, weather_type,
LAG(weather_type, 1) OVER(ORDER BY the_date)
as previous_weather_type
FROM weather
ORDER BY the_date))
ORDER BY the_date)
GROUP BY cumulative, weather_type
ORDER BY cumulative;
И наконец последний шаг - с помощью условия WHERE отберите только периоды, когда было солнечно. И далее найдите максимум колонки number_of_days.
Вы получите 3 - максимальное количество подряд идущих солнечных дней.
SELECT MAX(number_of_days) FROM ( SELECT DISTINCT cumulative, weather_type, COUNT(*) as number_of_days FROM ( SELECT the_date, weather_type, previous_weather_type, difference, SUM(difference) OVER (ORDER BY the_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative FROM ( SELECT the_date, weather_type, previous_weather_type, CASE WHEN weather_type <> previous_weather_type THEN 1 ELSE 0 END as difference FROM ( SELECT the_date, weather_type, LAG(weather_type, 1) OVER(ORDER BY the_date) as previous_weather_type FROM weather ORDER BY the_date)) ORDER BY the_date) GROUP BY cumulative, weather_type ORDER BY cumulative) WHERE weather_type = 'Солнечно';
Цель этого упражнения была в том, чтобы показать Вам всю мощь оконных функций, если к ним ещё добавить CASE, подзапросы и сложную логику.
12.14 Упражнение 15: нумерация и ранжирование
Напишите запрос к таблице ALBUM, чтобы вывести все альбомы музыкальных групп Metallica (band_id=93) и Queen (band_id=192). Отсортируйте данные сначала по номеру музыкальной группы (band_id), а внутри каждой группы - по году выпуска альбома.
Добавьте в запрос дополнительную колонку album_num, в которой выведите порядковый номер выхода альбома, если упорядочить их по годам выпуска. Такой порядковый номер должен вычисляться отдельно для каждой музыкальной группы.
Если в какой-то год вышли два альбома или больше, то дайте таким альбомам разные порядковые номера - не важно, какой из них будет первым, а какой вторым.
SELECT a.*,
ROW_NUMBER() OVER(PARTITION BY band_id ORDER BY year) as album_num
FROM album as a
WHERE band_id IN (93, 192)
ORDER BY band_id, year
Далее для каждой из этих двух музыкальных групп выведите только первые три альбома. Для этого используйте колонку album_num, которую Вы вычислили на предыдущем шаге.
В результате запроса альбомы Sheer Heart Attack и Queen II могут идти в любом порядке - либо так, как показано в указанной ниже таблице, либо в обратном порядке. Потому что в нашей задаче мы считаем, что порядок сортировки этих двух альбомов может быть любым.
В итоге должно получиться шесть альбомов - три для одной музыкальной группы, и три для другой.
SELECT * FROM (
SELECT a.*,
ROW_NUMBER() OVER(PARTITION BY band_id ORDER BY year) as album_num
FROM album as a
WHERE band_id IN (93, 192)
ORDER BY band_id, year)
WHERE album_num IN (1, 2, 3)
ORDER BY band_id