SQL勉強記録4

window関数　PARTITION BY

window関数 OVER(
PARTITION BY 対象
ORDER BY 対象
RANGE|ROW 集計範囲
)

OVER(PARTITION BY 〇〇)でレコードを〇〇ごとにまとめて集計することができる
ex.. AVG(age)OVER(PARTITION BY age)
COUNT(*)OVER(PARTITION BY department_id)

-- window関数
select *, AVG(age)over() -- over()になにもいれないと全行に対してageの平均をだす
from employees;

select *,
AVG(age)over(PARTITION BY department_id) AS avg_age,
count(*)over(PARTITION BY department_id) AS count_department -- department_idによってageの平均と数を数えられる
from employees;

-- group byでもできそう
select department_id,count(*),avg(age) from employees group by department_id;

-- 20代、30代などその年代が何人いるのかを出す(カラム以外に対してまとめることもできる)
select distinct CONCAT(count(*)over(PARTITION BY FLOOR(age/10)),'人') as age_count, FLOOR(age/10)*10
from employees;

-- 日付ごとに売り上げを集計
select *,SUM(order_amount*order_price)OVER(PARTITION BY order_date)

-- DATE_FORMATを使って月毎に集計
select *,SUM(order_amount*order_price)OVER(PATITION BY DATE_FORMAT(order_date,'%Y/%m'));
from orders;

パーティション、フレームとは

パーティション・・・PARTITION BY によって分割される行の塊のこと
フレーム・・・パーティションの中で集計の対象とするさらに小さな集合

フレームのORDER BY

ORDER BYはパーティションの中でパーティションの始めのレコードから
そのカレントレコードまでのレコードまでを集計対象とする
(20代が4人、30代が７人いるなら20代のレコードは４、３０代のレコードでは11と表示するなど)
(日付ごとに売上があり、次の日はその前までの売上が足されたレコードを表示するなど)

-- ORDER BY
/*
COUNT(*)OVER()だとすべての行をカウントするが
COUNT(*)OVER(ORDER BY age)とすると始めの行からそのカレントレコードまでをカウントする
 */
select *,COUNT(*)OVER(ORDER BY age)
from employees;

-- その日付までのorder_priceの合計を表示できる
select *,SUM(order_price)OVER(ORDER BY order_date) from orders;

-- ORDER BYに２つカラムを設定することもできてcustomer_idごとに足したorder_priceも表示できる
select *,SUM(order_price)OVER(ORDER BY order_date,customer_id) from orders;

PARTITION BY とORDER BYを併用する

PARTITION BYでパーティションに分割して、その中で　ORDER BYで並び替えて集計する

SUM(給料)OVER(PATITION BY 部署 ORDER BY 年齢)
-- 部署ごとに分割して年齢で並び替えて集計する

フレーム内で集計する範囲を決定する（ROW BETWEEN, RANGE BETWEEN）

例えばその行と一つ前の行の合計を集計するなら下記のようにする

SUM(給料)OVER(ROWS BETWEEN 1PRECEDING AND CURRENT ROW)

ROWSで指定する式の一覧

UNBOUNDED PRECEDING -- 一番頭の行
n PRECEDING -- 現在の行よりn行前の行
CURRENT ROW -- 現在の行
n FOLLOWING -- 現在の行よりn行後の行
UNBOUNDED FOLLOWING -- 一番最後の行

-- ROWS 2PRECEDING だけでも２つ前から現在の行という式になる

RANGE BETWEEN はその現在の行の値に対して一つ小さいのをまとめたりといった使い方をする
（ORDER BY と一緒に使う、それでまとめたものに対して発動）
ex)現在の行と年齢が１つ小さいものの給料をまとめる

SUM(給料)OVER(ORDER BY 年齢 RANGE BETWEEN 1PRECEDING AND CURRENT ROW)

-- 同じくRANGE 2PRECEDINGでも可(CURRENT ROWの省略)

イメージとしてはOVER(ORDER BY)で本来はパーティションの初めからその現在のレコードまでを集計するところをROWS BETWEEN やRANGE BETWEENで範囲を制御するイメージ

-- 7日間の平均
-- 手順：まずは日付ごとの合計を出し、前6日を含めた平均を出す
with daily_summary as(
select order_date,SUM(order_price*order_amount) as sum_order
from orders
group by order_date
) -- まずは日付ごとに合計をだす。それをwithで一時的なテーブルとして用意
select 
*,
AVG(sum_order)OVER(ORDER BY order_date ROWS BETWEEN 6PRECEDING AND CURRENT ROW)
from daily_summary; 
-- OVER(ORDER BY)によってパーティションの始めから現在のレコードまでを平均するところをROWS BETWEENによって6日前までと制御することができる


select * from salaries;

select
	employee_id,
	SUM(payment) as payment
from salaries
group by employee_id; -- 従業員1人の年収

-- 副問い合わせでemployeeテーブルに年収のカラムを追加する
select * from employees as emp
inner join 
(select
	employee_id,
	SUM(payment) as payment
from salaries
group by employee_id) AS summary_salary　-- 副問い合わせ部（with使ったほうが簡単）
ON emp.id = summary_salary.employee_id;

ウィンドウ関数その他

ROW_NUMBER・・パーティション内での行番号（重複してもカウントアップする）
RANK・・パーティション内で重複する値は同じ値にして重複分はカウントアップしてランキング表示(1が３つあったら次は４から)
DENSE_RANK・・パーティション内で重複する値は同じ値にして重複分は1だけカウントアップしてランキング表示(1が３つあっても次は２)
LAG・・現在の行の一つ前を取得
LEAD・・現在の行の一つ後を取得
FIRST_VALUE・・パーティションの対象フレームの一番最初の行の値を取り出す
LAST_VALUE・・パーティションの対象フレームの一番最後の行の値を取りだす
NTILE・・・NTILE(10)とするとそのフレーム内を10分割した数値を割り当てられる

SQLの論理的な順序

FROM や　JOINには実行される順序があるため
エラーや実行できなかったりするときはこれらが意識できていないパターンがあるという

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. 集計関数(SUMなど)
5. HAVING
6. ウィンドウ関数
7. SELECT
8. DISTINCT
9. UNION/INTERSECT/EXCEPT
10. ORDER BY
11. OFFSET
12. LIMIT