最近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 の既存データもそのまま残す