SHOW:
|
|
- or go back to the newest paste.
1 | - | /*Посчитайте среднюю стоимость аренды фильма каждого возрастного рейтинга. Среди них найдите рейтинг с самыми дорогими для аренды фильмами. |
1 | + | /*Составьте сводную таблицу. Посчитайте заказы, оформленные за каждый месяц в течение нескольких лет: с 2011 по 2013 год. Итоговая таблица должна включать четыре поля: invoice_month, year_2011, year_2012, year_2013. Поле invoice_month должно хранить месяц в виде числа от 1 до 12. |
2 | - | Выведите на экран названия категорий фильмов с этим рейтингом. Добавьте второе поле со средним значением продолжительности фильмов категории. |
2 | + | Если в какой-либо месяц заказы не оформляли, номер такого месяца всё равно должен попасть в таблицу. |
3 | В этом задании не будет подсказок. Используйте любые методы, которые посчитаете нужными. | |
4 | */ | |
5 | - | SELECT c.name, |
5 | + | |
6 | - | AVG(m.length) |
6 | + | SELECT i_month.invoice_month, |
7 | - | FROM movie AS m |
7 | + | inv_2011.year_2011, |
8 | - | INNER JOIN film_category AS fc ON m.film_id = fc.film_id |
8 | + | inv_2012.year_2012, |
9 | - | INNER JOIN category AS c ON fc.category_id = c.category_id |
9 | + | inv_2013.year_2013 |
10 | - | WHERE rating IN |
10 | + | FROM |
11 | - | (SELECT rating |
11 | + | (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month |
12 | - | FROM movie |
12 | + | FROM invoice |
13 | - | GROUP BY rating |
13 | + | GROUP BY invoice_month |
14 | - | ORDER BY AVG(rental_rate) DESC |
14 | + | ORDER BY invoice_month) AS i_month |
15 | - | LIMIT 1) |
15 | + | |
16 | - | GROUP BY c.name; |
16 | + | |
17 | LEFT JOIN | |
18 | (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, | |
19 | COUNT(invoice_id) AS year_2011 | |
20 | FROM invoice | |
21 | WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2011 | |
22 | GROUP BY invoice_month) AS inv_2011 ON i_month.invoice_month = inv_2011.invoice_month | |
23 | ||
24 | ||
25 | LEFT JOIN | |
26 | (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, | |
27 | COUNT(invoice_id) AS year_2012 | |
28 | FROM invoice | |
29 | WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2012 | |
30 | GROUP BY invoice_month) AS inv_2012 ON i_month.invoice_month = inv_2012.invoice_month | |
31 | ||
32 | ||
33 | LEFT JOIN | |
34 | (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS invoice_month, | |
35 | COUNT(invoice_id) AS year_2013 | |
36 | FROM invoice | |
37 | WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2013 | |
38 | GROUP BY invoice_month) AS inv_2013 ON i_month.invoice_month = inv_2013.invoice_month |