###GROUP BY と PARTITION BY
・GROUP BY 演算子とは「複数の行を 1 つにまとめたうえ で、各グループについて 1 行を返す」
・PARTITION BY 演算子とは 「もとのテーブルを複数の部分集合に切り分ける」
GROUP BY 句がグループを 1 行に集約するのに対して
PARTITION BY 句では単にパーティションに分割するだけで集約せず、入力と出力の行数は同一となる
→適宜DISTINCTなどを使用すればGROUP BYと同じ結果が得られる
※ただし処理に時間がかかる
--同じ結果を返す
SELECT event,
COUNT(event)
FROM test.click
GROUP BY event
;
SELECT DISTINCT event,
COUNT(event) OVER (PARTITION BY event)
FROM test.click
;
###データのインポート（ファイルとテーブルの間でデータをコピーする）
COPY
table_name
FROM
'faile_name'
WITH
--FORMATとしてtxtやcsv,binaryを指定できる
CSV
--HEADERと書くと先頭行を取り込める
HEADER
;
COMMIT;
###データのエクスポート
COPY
table_name
TO
'/users/kaede/Desktop/file_name.csv'
--csv形式のエクスポート
WITH
CSV
HEADER
DELIMITER ','
;
###列名を変更する
ALTER TABLE
table_name
RENAME COLUMN
colum_name
TO
new_colum_name;
###複数の条件を指定する
SELECT
colum_name
FROM
table_name
WHERE
table_name 比較演算子 条件値
OR
table_name 比較演算子 条件値
;
###SELECTした結果で新しい表を作成する
CREATE TABLE
new_table_name
AS
SELECT *
FROM
table_name
;
###データ型を変更する
SELECT
CAST(colum_name AS データ型)
FROM
table_name
;
###WITH句
WITH A AS(
SELECT
*
FROM
table_name
)
,B AS(
SELECT
*
FROM
A
)
SELECT
*
FROM
B
;
###累積関数
SELECT
*
,SUM(Value) OVER(PARTITION BY 累計にしたいグループ ORDER BY 順序を決めたいグループ)
AS colum_name
FROM
table_name
ORDER BY
colum_name
;
###データの最初からN行を取り出す
SELECT
*
FROM
uk_driver_deaths
ORDER BY
year_month
LIMIT N
###データの最後からN行を取り出す
SELECT
*
FROM
uk_driver_deaths
ORDER BY
year_month
DESC LIMIT N
###12ヶ月移動平均
SELECT
*
,AVG(uk_driver_deaths)
OVER (
ORDER BY year_month
ROWS BETWEEN 11 preceding AND current row
) AS new_colum_name
FROM
table_name
ORDER BY
year_month
;
###テーブルからいらない行を削除
DELETE FROM
table_name
WHERE
条件式
;
##結合
###JOIN
#####INNER JOIN
内部結合では左右それぞれのテーブルの指定したカラムの値が一致するレコードだけを取得する
#####OUTER JOIN(LEFT JOIN/RIGHT JOIN/FULL JOIN)
外部結合は左右それぞれのテーブルの指定したカラムの値が一致するレコードに加えてどちらかのテーブルにしか存在しないデータについても取得する
SELECTの後の列指定で違うテーブルの列を選ぶこともできる
SELECT
columname_table1
,columname_table2
FROM
table_name1
INNER JOIN
table_name2
ON
条件式
;
--同じテーブルで結合したい時
SELECT
*
FROM
tablename (as) newname --asは無くても良い
LEFT JOIN
tablename newname2
ON
newname.columname = newname2.columname2
;
###UNION
テーブル1とテーブル2を縦につなげる
重複行を削除したい場合はUNIONのみ、すべてのデータを残した場合はUNION ALLを使う
SELECT
*
FROM
table_name1
UNION ALL
SELECT
*
FROM
table_name2
;
###文字列〜を含む
WHERE colum_name
LIKE 'XXX%YYY'(LIKE '%AAA%')
###複数条件指定
WHERE colum_name IN (条件1,条件2,条件3)
###階級で分けて新しい列として階級を追加
SELECT
name
,CASE
WHEN colum_name >= 100 THEN 'expensive'
WHEN colum_name <= 100 THEN 'cheap'
END AS new_colum_name
FROM
table_name
;
--条件分岐
CASE
ELSE
###タイムスタンプ型の内容をあいまい検索するとき
where
to_char(joindate,'yyyymmdd') like '201209%'
###重複を削除
SELECT DISTINCT
colum_name
FROM
table_name
;
JOINであてがうﾃｰﾌﾞﾙをSELECTの結果ｾｯﾄにすることができる。
SELECT *
FROM
[ﾃｰﾌﾞﾙA]
INNER JOIN
(SELECT [項目B]
FROM XX
WHERE XX )AS [結果ｾｯﾄC]
ON [ﾃｰﾌﾞﾙA].[項目C] = [結果ｾｯﾄC].[項目B]
WHERE
[ﾃｰﾌﾞﾙA].[項目D] = 'XX'
###文字列連結
SELECT '文字列1' || '文字列2' || ...
--行ごとで足し算できる
SUM(colum_name1+colum_name2)
--空白を取り出す
WHERE
colum_name IS NULL
--空白以外を取り出す
WHERE
colum_name IS NOT NULL
CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;
--降順
ORDER BY
colum_name DESC
###豆知識
####同じ階層のクエリで付けた別名を、条件式で指定することはできません
####⇨その際はサブクエリ（副問い合わせ）を使用する
FROM内のサブクエリはaliasつまり別名をつけないと使えない
###整数同士の割り算
--どちらかの数字に小数点以下をつける
SELECT
3.0 / 2
SELECT
3 / 2.0
--型変換を行う
SELECT
CONVERT(float, 3) / 2
SELECT
3 / CONVERT(float, 2)
--小数を掛けて、整数型の演算ではなくする
SELECT
1.0 * 3 / 2
--CASTを使うやり方
CAST(con_count AS FLOAT)
WITH句を用いたcreate文
CREATE TABLE table_name AS
WITH A AS...
CAST関数で型変換
参考URL
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CAST_function.html
SELECT
CAST(column_name as numeric(変換したい型))
FROM
table_name
--下記のように集計関数と一緒に変換もできる
AVG(columname::numeric)
--整数同士の割り算で小数点を出すこともできる
CAST(con_count AS FLOAT)
IN演算子
「1または２または３」というような複数の値の条件指定ができるもの
IN(1,2,3)
--OR演算子を用いて書き換えられる
rank='1' OR rank ='2' OR rank ='3'