最近SQLの勉強をしており、分析関数の多さに覚えきれないので、記録として残しておきます。
集計関数
SUM 合計
値の合計に使用します。
SELECT SUM(int)
FROM table
AVG 平均
値の平均を求める時に使用します。
SELECT AVG(int) AS 平均
FROM table
COUNT 件数取得
件数を数えたいときに使用します。
SELECT COUNT(int) AS 件数
FROM table
COUNT(DISTINCT) 重複除いた件数
重複したデータを除いた件数を取得したいときに使用します。
SELECT COUNT(DISTINCT int) AS 重複除いた件数
FROM table
ROW_NUMBER 連番
指定したカラムを順番に数字を表示する時に使用します。
重複削除やランキング表示する時によく使われます。
SELECT ROW_NUMBER() OVER(PARTITION BY char ORDER BY int) AS 連番
EXTRACT(年月日 FROM 日付カラム) 年月日どれか抽出
年月日どれかだけ値を取得したいときに使用します。
SELECT EXTRACT(YEAR FROM DATE) AS 年
FROM table
SELECT EXTRACT(MONTH FROM DATE) AS 月
FROM table
SELECT EXTRACT(DAY FROM DATE) AS 日
FROM table
SELECT EXTRACT(DAYOFWEEK FROM date) AS 曜日
FROM table
ROUND 少数切り捨て
少数を切り捨てる場合に使用します。
SELECT ROUND(int,0) AS 少数切り捨て
FROM table
SELECT ROUND(int,1) AS 少数第2位切り捨て
FROM table
FLOOR 整数に切り捨て
整数を表示したい場合に使用します。
SELECT FLOOR(int) AS 整数に切り捨て
POW 乗算
値を乗算したいときに使用します。
SELECT POW(int,2) AS 2乗
FROM table
SELECT POW(int,3) AS 3乗
FROM table
RANK ランキング
値をランク付けしたい時に使用します。
SELECT RANK() OVER(PARTITION BY char ORDER BY int DESC)
FROM table
APPROX_QUANTILES 中央値
中央値を取得したいときに使用します。
SELECT APPROX_QUANTILES(int,100)[OFFSET(50)] AS 中央値
FROM table
その他
DISTINCT 重複行をまとめる
値が重複しないように抽出する際に使用します。
SELECT DISTINCT int
STDDEV 標準偏差
標準偏差を求める時に使用します。
SELECT
ABS 絶対値取得
マイナスの値を正の値で取得したいときに使用します。
SELECT ABS(int) AS 絶対値
FROM table
番外編
同じカラム同士で、古いデータのテーブルから最新のデータに入れ替えたいとき
2つのテーブルがあり、1つが決算の日が古いデータだとします。
その時、最新のデータが入っている2つ目のテーブルに入れ替えたい時はフラグを使用して以下のクエリを行うと入れ替えることができます。
WITH t3 AS (
SELECT
t1.id,
t1.kessan,
t1.data,
CASE
WHEN t1.kessan = t2.kessan THEN 1 --同じ決算日同士を除外
ELSE 0
END AS teisei
FROM a AS t1
LEFT JOIN b AS t2
ON t1.id = t2.id
)
SELECT
id,
kessan,
data,
teisei
FROM t3
WHERE teisei = 0 -- teisei=0 のデータだけ残す
UNION ALL -- 最新のデータをすべて取り込む
SELECT
id,
kessan,
hyouji,
1 AS teisei
FROM b; -- b の既存データもそのまま残す