PostgresSQL


###GROUP BY と　PARTITION BY
・GROUP BY 演算子とは「複数の行を 1 つにまとめたうえ で、各グループについて 1 行を返す」
・PARTITION BY 演算子とは 「もとのテーブルを複数の部分集合に切り分ける」
GROUP BY 句がグループを 1 行に集約するのに対して 
PARTITION BY 句では単にパーティションに分割するだけで集約せず、入力と出力の行数は同一となる
→適宜DISTINCTなどを使用すればGROUP BYと同じ結果が得られる
※ただし処理に時間がかかる

~~~SQL
--同じ結果を返す

SELECT event,
       COUNT(event)
FROM test.click
GROUP BY event
;

SELECT DISTINCT event,
       COUNT(event) OVER (PARTITION BY event)
FROM test.click
;
~~~

###データのインポート（ファイルとテーブルの間でデータをコピーする）
~~~SQL
COPY
  table_name
FROM
  'faile_name'
WITH
--FORMATとしてtxtやcsv,binaryを指定できる
  CSV
--HEADERと書くと先頭行を取り込める
  HEADER
;

COMMIT;
~~~
###データのエクスポート
~~~SQL
COPY
  table_name
TO
  '/users/kaede/Desktop/file_name.csv'
--csv形式のエクスポート
WITH
 CSV
 HEADER
 DELIMITER ','
;
~~~

###列名を変更する

~~~SQL
ALTER TABLE 
  table_name 
RENAME COLUMN 
  colum_name 
TO 
  new_colum_name;
~~~
###複数の条件を指定する
~~~SQL
SELECT  
  colum_name
FROM
  table_name
WHERE
  table_name 比較演算子 条件値
OR 
  table_name 比較演算子 条件値
;
~~~

###SELECTした結果で新しい表を作成する
~~~SQL
CREATE TABLE 
  new_table_name
AS
SELECT *
FROM   
  table_name
;
~~~

###データ型を変更する
~~~SQL
SELECT
　　CAST(colum_name AS データ型)
FROM
　　table_name
;
~~~

###WITH句
~~~SQL
WITH A AS(
  SELECT
    *
  FROM
    table_name
  )
    ,B AS(
  SELECT 
	*
  FROM
    A
  )
SELECT
  *
FROM
  B
;
~~~

###累積関数
~~~SQL
SELECT
  *
  ,SUM(Value) OVER(PARTITION BY 累計にしたいグループ ORDER BY  順序を決めたいグループ) 
  AS colum_name
FROM 
  table_name
ORDER BY 
  colum_name
;
~~~
###データの最初からN行を取り出す
~~~SQL
SELECT
  * 
FROM  
  uk_driver_deaths 
ORDER BY
  year_month 
LIMIT N
~~~
###データの最後からN行を取り出す
~~~SQL
SELECT
  * 
FROM  
  uk_driver_deaths 
ORDER BY
  year_month 
DESC LIMIT N
~~~

###12ヶ月移動平均
~~~SQL
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
;
~~~

###テーブルからいらない行を削除
~~~SQL
DELETE FROM
  table_name
WHERE
  条件式
;
~~~

##結合


###JOIN
#####INNER JOIN 
内部結合では左右それぞれのテーブルの指定したカラムの値が一致するレコードだけを取得する
#####OUTER JOIN(LEFT JOIN/RIGHT JOIN/FULL JOIN)
外部結合は左右それぞれのテーブルの指定したカラムの値が一致するレコードに加えてどちらかのテーブルにしか存在しないデータについても取得する

SELECTの後の列指定で違うテーブルの列を選ぶこともできる

~~~SQL
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を使う

~~~SQL
SELECT
  *
FROM
  table_name1
UNION ALL
SELECT
  *
FROM
  table_name2
;
~~~

###文字列〜を含む
~~~SQL
WHERE colum_name
LIKE 'XXX%YYY'(LIKE '%AAA%')
~~~

###複数条件指定
~~~SQL
WHERE colum_name IN (条件1,条件2,条件3)
~~~

###階級で分けて新しい列として階級を追加
~~~SQL
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
~~~
###タイムスタンプ型の内容をあいまい検索するとき
~~~SQL
where
to_char(joindate,'yyyymmdd') like '201209%'
~~~
###重複を削除
~~~SQL
SELECT DISTINCT
  colum_name
FROM
  table_name
;
~~~
JOINであてがうﾃｰﾌﾞﾙをSELECTの結果ｾｯﾄにすることができる。

~~~SQL
SELECT *
FROM
[ﾃｰﾌﾞﾙA]
INNER JOIN 
　　　 (SELECT [項目B] 
　　　 FROM XX
　　　 WHERE XX )AS [結果ｾｯﾄC]
ON [ﾃｰﾌﾞﾙA].[項目C] = [結果ｾｯﾄC].[項目B] 
WHERE
[ﾃｰﾌﾞﾙA].[項目D] = 'XX' 
~~~

###文字列連結
~~~SQL
SELECT '文字列1' || '文字列2' || ...
~~~

~~~SQL
--行ごとで足し算できる
SUM(colum_name1+colum_name2)
--空白を取り出す
WHERE 
colum_name IS NULL
--空白以外を取り出す
WHERE
colum_name IS NOT NULL
~~~

~~~SQL
CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;
~~~

~~~SQL
--降順
ORDER BY
colum_name DESC
~~~

###豆知識

####同じ階層のクエリで付けた別名を、条件式で指定することはできません

####⇨その際はサブクエリ（副問い合わせ）を使用する

FROM内のサブクエリはaliasつまり別名をつけないと使えない



###整数同士の割り算

~~~SQL
--どちらかの数字に小数点以下をつける
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文

~~~SQL
CREATE TABLE table_name AS
WITH A AS...
~~~
CAST関数で型変換
参考URL
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CAST_function.html

~~~SQL
SELECT
 CAST(column_name as numeric(変換したい型))
FROM
 table_name

--下記のように集計関数と一緒に変換もできる
AVG(columname::numeric)
--整数同士の割り算で小数点を出すこともできる
 CAST(con_count AS FLOAT)
~~~

IN演算子
「1または２または３」というような複数の値の条件指定ができるもの

~~~SQL
IN(1,2,3)
--OR演算子を用いて書き換えられる
rank='1' OR rank ='2' OR rank ='3'
~~~