This page looks best with JavaScript enabled
⚠️

SQL勉強会をやりました【レポ】

 ·   ·  ☕ 5 分で読めます
✏️

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'
  • 今回は7.00$も入れていいかなという。

日本人客の住んでいる場所で方言を特定したいです。‘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'
  • string_aggというものがあるのか……!
Share on

END
END
@aiandrox

 
目次