Edited at

SQLクエリについて


初めてのSQLコマンド

Working with Amazon Redshift(日本語版)

今まで、SQLコマンドを書いてこなかったのですが、LABでSQLクエリの書き方を学んだので、覚書程度に残しておきます。

DBの詳細はQwiklabで確認してください。

ちなみに、RedshiftのQuery editorからクエリを送りました。

色々なコマンドを使いながら任意のデータを検索できるようにチャレンジしました。

コマンドを書く上で一番重要なポイントはSELECT文の評価順序だと感じました。

SELECT文の評価順序の話

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP BY

6.HAVING

7.SELECT

8.DISTINCT

9.ORDER BY

10.TOP(LIMIT)


SELECT文をSELECT

Groupごとの、上位3つのレコードの取得をしたいと思いましたが、FROM句は最初に処理をされるため、Groupごとの取得条件げ付けられないため、このように、SELECT文をSELECTすることになりました。

別の手段としてはAS句を利用してSELECT文で新しいテーブルを作り、2つのクエリに分けてレコードの取得をすることも可能です。

各年の、キャリアごとの、都市間の移動距離の合計の 上位3つのキャリアを取得する、SELECT文です。


xxx.sh

SELECT

*
FROM
(
SELECT
year,
sum_mile,
row_number() over(partition by year order by sum_mile desc) AS num
FROM
(
SELECT
carrier,
year,
SUM(miles) AS sum_mile
FROM
flights
GROUP BY
carrier,
year
ORDER BY
year,
sum_mile DESC
)
) rank
WHERE
rank.num BETWEEN 1 AND 3
;
ORDER BY
year

キャプチャ2.PNG

SELECT文を評価順序に沿ってみていきます。

FROM内のFROMでflightテーブルを利用することを宣言します。

GROUP BYでcarrier,yearでグループを作成します。

SELECTのSUM(miles)でGROUPごとの都市間移動距離を取得します。

そしてyear(ASC),sum_mileでソートされます。

SELECT文のrow_number関数によって、yearごとのsum_mileの大きい順にランク付けがされ、出力されます。

その出力をWHERE句で上位3位に条件付けを行う

ORDER句でyearを昇順にソートし、表示する


色々なコマンドを組み合わせて使ってみる

頭文字が'A'または、頭文字がBCの3文字のフライトの出発空港名で、かつ都市間の移動距離が500mil以上の、各年、キャリア、フライトの出発空港ごとの出発する便数合計が、1000以上のレコードを取得する


xxx.sh

select

year,
MAX(departuresyear) as departuresyear
from
(
SELECT
year,
carrier,
origin,
sum(departures) AS departuresyear
FROM
flights
WHERE
(
origin LIKE 'A%'
OR origin LIKE 'BC_'
)
AND miles >= 500
GROUP BY
year,
carrier,
origin
HAVING sum(departures) > 10000
ORDER BY
1 ASC,
departuresyear DESC
)
GROUP BY
year
ORDER BY
year ASC
;

キャプチャ3.PNG

FROMでflightテーブルを利用することを宣言し、

WHERE句で頭文字が'A'または、頭文字がBCの3文字のフライトの出発空港名で、かつ都市間の移動距離が500mil以上レコードに限定する

GROUP句で、year,carria,originのグルループを作成する

HAVINGでグループに対する条件指定を行い、合計出発便数が1000以上のレコードに限定する

SELECT文でテーブルを作成し、ORDER句でyearの昇順、年間合計出発便数の降順にソートして、出力する

yearでグループ化を行い、各年の最大年間出国便数を取得し、SELECT文で出力テーブルを作成し、

ORDER句でyearの降順でソートして、出力する


まとめ

まだ、実際にSQLコマンドと利用したことはありませんが、なるべく使いまわせるテンプレートになるようにコマンドを利用しました。

なにか、改善案等あれば、ご教示ください。