RUNTEQの有志でSQL勉強会をしました。
これはその記録です。
題材
PostgreSQL Sample Database
このDBで、1人1問ずつ用意して15分で解こうといったものでした。
問題・解答例
この解答例は、みんなの案をすり合わせた結果、私が個人的に「この書き方ええな」と思ったものを選びました。
支払い金額が高い10人のcustomer_idと合計金額を高い順に出力する
1
2
3
4
5
6
|
select customer_id,
sum(amount)
from payment
group by customer_id
order by sum desc
limit 10;
|
- selectで使っている
sum(amount)と同じものをsumと省略できる。
2005年5月25日に借りられた映画をカテゴリーごとに集計(映画の数が多い順)
1
2
3
4
5
6
7
8
9
|
select c.name,
count(c.name)
from rental r
join inventory i on r.inventory_id = i.inventory_id
join film_category fc on fc.film_id = i.film_id -- ポイント
join category c on c.category_id = fc.category_id
where date(r.rental_date) = '2005-05-25'
group by c.name
order by count desc
|
between A and Bは「A以上、B以下」で、5/26 0:00も含んでしまうので使わないほうがいい。date_trunc('day', r.rental_date)だと「月」のときもきれいに整形しやすいけど、値としてきれいになるのはdate(r.rental_date)。- ポイント：
filmテーブルを飛ばすことで、joinを一つ減らせる。
Joe Swankが出演している映画の中で、120分以下の放映時間のものだけ見たい
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select film_id, title, length from
(
select f.film_id,
f.title,
f.length,
f.first_name || ' ' || f.last_name as actor_name
from film f
join film_actor fa on f.film_id = fa.film_id
join actor a on fa.actor_id = a.actor_id
where length <= 120
) as t1
where actor_name = 'Joe Swank'
order by length desc;
|
- 素直な書き方なら副問い合せはいらないけど、「Joe Swankが出演している」をいう条件をテキスト通りに表記していたのが好みだった。
concat(a.first_name, ' ', a.last_name)とa.first_name || ' ' || a.last_nameは同義。
Jun Stephensが貸し出したDVDの金額が5.00$~7.00$のお客さんのメールアドレスを教えてください。お願いします。
1
2
3
4
5
6
7
8
9
|
select distinct c.email
from (
select p.customer_id, first_name || ' ' || last_name as staff_name
from staff s
join payment p on s.staff_id = p.staff_id
where p.amount between 5.00 and 7.00
) as t1
join customer c on t1.customer_id = c.customer_id
where staff_name = 'Jon Stephens'
|
日本人客の住んでいる場所で方言を特定したいです。‘Tokyo-to’, ‘Saitama’, ‘Kanagawa’, ‘Chiba’に住んでいるものは「標準語」、‘Osaka’, ‘Mie’, ‘Shiga’に住んでいるものは「関西弁」、その他は「田舎者」として表示してください。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
select cu.first_name,
cu.last_name,
cu.email,
a.district,
case
when a.district in ('Tokyo-to', 'Saitama', 'Kanagawa', 'Chiba')
then '標準語'
when a.district in ('Osaka', 'Mie', 'Shiga')
then '関西弁'
else '田舎者'
end
from customer cu
join address a on cu.address_id = a.address_id
join city ci on ci.city_id = a.city_id
join country co on co.country_id = ci.country_id
where co.country = 'Japan'
|
- 今回初のcase文です。
- 「田舎者」は方言ではなくね？というツッコミは黙殺します。
「年月」ごとの総レンタル本数
1
2
3
4
5
|
select to_char(rental_date, 'YYYY-MM') as month,
count(*)
from rental
group by month
order by month
|
rental_dateはtimestamp型なので、to_char(rental_date, 'YYYY-MM')でキャストする必要がある。
その中で最も多くレンタルされた映画のタイトル、そのレンタル本数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
select DISTINCT(title), *
from (
select monthly,
title,
month_count,
month_title,
rank() over (PARTITION by monthly order by month_title desc)
from (
select count(*) over (PARTITION by monthly) as month_count,
monthly,
count(*) over (PARTITION by monthly, title) as month_title,
title
from (
select title,
to_char(rental_date, 'YYYY-MM') as monthly
from rental
join inventory on rental.inventory_id = inventory.inventory_id
join film on inventory.film_id = film.film_id
) as t1
) as t2
) as t3
where rank = 1
order by monthly;
|
- 後半は考えなしに出しました。自分もできていなかったですすいません。
- ついでに言うと同率一位があります。本当に申し訳ないです。
国ごとに持っているcityの数を出力する
1
2
3
4
5
6
|
select distinct co.country,
count(*)
from country co
join city ci on co.country_id = ci.country_id
group by co.country_id
order by count desc
|
1
2
3
4
5
|
select co.country,
count(co.country_id) over (partition by co.country_id)
from country co
join city ci on co.country_id = ci.country_id
order by count desc
|
partition byの練習をしました。
- select時に、選んだカラムのみを別データ？として抜き出している。
- group byと違って、共通の値のレコードがきゅっとなるわけではないので、手動で
distinctを入れる必要がある。
- partition byの後に、普段group byで指定しているもの、overの前にselectで指定しているもの。
映画のタイトル名「Date Speed」と「Attraction Newton」の総レンタル売上は？
1
2
3
4
5
6
7
|
select f.film_id, f.title, sum(p.amount)
from film f
join inventory i on f.film_id = i.film_id
join rental r on r.inventory_id = i.inventory_id
join payment p on p.rental_id = r.rental_id
where f.title in ('Date Speed', 'Attraction Newton')
group by f.film_id
|
1
2
3
4
5
6
7
8
|
select distinct f.film_id,
f.title,
sum(p.amount) over (partition by f.title)
from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on r.inventory_id = i.inventory_id
join film f on f.film_id = i.film_id
where f.title in ('Date Speed', 'Attraction Newton')
|
- この頃になると、join, where, group byくらいはお手の物になってきた。
スタッフ「Mike Hillyer」が貸し出した、映画一覧と映画それぞれの女優・俳優一覧を,続きで出してください。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
select title, staff_name, string_agg(actor_name, ',') as actor_list
from (
select film.title,
staff.first_name || ' ' || staff.last_name as staff_name,
actor.first_name || ' ' || actor.last_name as actor_name
from rental
join staff on rental.staff_id = staff.staff_id
join inventory on rental.inventory_id = inventory.inventory_id
join film on inventory.film_id = film.film_id
join film_actor on film.film_id = film_actor.film_id
join actor on film_actor.actor_id = actor.actor_id
) as t1
group by title, staff_name
having staff_name = 'Mike Hillyer'
|
