In [2]:
%load_ext sql
%sql sqlite:///dataset_1.db


Out[2]:
'Connected: None@dataset_1.db'

Задание #1

Рассмотрим набор таблиц описывающая индустрию бубликов (bagel) bagel, типы бубликов и компании их делающие:

  • name STRING
  • price FLOAT
  • made_by STRING

И purchase:

  • bagel_name STRING
  • franchise STRING
  • date INT
  • quantity INT
  • purchaser_age INT

Где purchase.bagel_name ссылается bagel.name т purchase.franchise на/ bagel.made_by:


In [7]:
%sql SELECT * FROM bagel LIMIT 3;


Done.
Out[7]:
name price made_by
Plain with shmear 1.99 Bobs Bagels
Egg with shmear 2.39 Bobs Bagels
eBagel Drinkable Bagel 27.99 eBagel

In [8]:
%sql SELECT * FROM purchase LIMIT 3;


Done.
Out[8]:
bagel_name franchise date quantity purchaser_age
Plain with shmear Bobs Bagels 1 12 28
Egg with shmear Bobs Bagels 2 6 47
Plain with shmear BAGEL CORP 2 12 24

Напишите запрос для получение суммарного дохода для каждого типа бубликов, для которых средний возраст покупателя больше 18


In [ ]:

Exercise #2

Воспользуемся упрощенной версией precipitation_full таблицы, которая имеет только дневные daily осадки только в CA, и имеет следующую схему:

  • station_id
  • day
  • precipitation

In [9]:
%sql SELECT * FROM precipitation LIMIT 5;


Done.
Out[9]:
station_id day precipitation
16102 1 10
16102 4 10
16102 24 30
21201 1 0
21201 20 10

Верните id станции, у которых средние количество осадков > 75. (попробуйте написать это вложенным запросом):


In [ ]:

перепишите в GROUP BY:


In [ ]:

Посмотрим на время выполнения


In [12]:
%time %sql SELECT DISTINCT p.station_id FROM precipitation p WHERE (SELECT AVG(precipitation) FROM precipitation WHERE station_id = p.station_id) > 75;


Done.
Wall time: 20.5 ms
Out[12]:
station_id
88302
250002
335701
357302
488301

In [13]:
%time %sql SELECT p.station_id FROM precipitation p GROUP BY p.station_id HAVING AVG(p.precipitation) > 75;


Done.
Wall time: 2 ms
Out[13]:
station_id
88302
250002
335701
357302
488301

An ~ 10-20x разница во времени!