--マテビューを作成する構文
CREATE MATERIALIZED VIEW マテビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

--マテビューにINDEXを定義する
CREATE UNIQUE INDEX ON マテビュー名(列1 , 列2 , ・・・);

--通常のリフレッシュ文
REFRESH MATERIALIZED VIEW マテビュー名;

--CONCURRENTLYをつけた方式、ただしこれにはINDEX作成が必要
REFRESH MATERIALIZED VIEW CONCURRENTLY マテビュー名;

SELECT 
	--教科
	subject_name, 
	
	--教科でWindowを区切る（PARTITION BY subject_name）
	--教科ごとに、点数の高い順（order by score desc）に生徒を表示する
	rank() OVER(PARTITION BY subject_name order by score desc) AS ranking
	
	--生徒,          点数
	, student_name , score
	
	--教科ごとに合計点を取得する
	, sum(score) OVER (PARTITION BY subject_name) 
FROM test_score;

SELECT 
	--生徒名
	student_name, 
	
	--生徒でWindowで区切る（PARTITION BY student_name）
	--生徒ごとに、点数の高い順（order by score desc）に教科を表示する
	rank() OVER(PARTITION BY student_name order by score desc) AS ranking
	
	--教科,           点数
	, subject_name , score
	
	--またその生徒ごとに合計点を取得する
	, sum(score) OVER (PARTITION BY student_name) 
FROM test_score;

--ビューを作成する
CREATE OR REPLACE VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名; 

--ビューを作成する例1
create view employee_vw as 
select employee_code , employee_name , department_code from employee;

--ビューを作成する例2(列の指定はアスタリスクでも可)
create view employee_vw as select * from employee;

--ビューを削除する
DROP VIEW ビュー名;

--ビューを削除する例
DROP VIEW employee_vw;

--ビューへのSELECT権限を付与する
GRANT SELECT ON ビュー名 TO ロール名;     --SELECT権限を付与する

--ビュー「employee_vw」へのSELECT権限を付与する例
GRANT SELECT ON employee_vw TO postgres;

--ビューへのSELECT権限を削除する
REVOKE ALL PRIVILEGES ON ビュー名 FROM ロール名;  --SELECT権限を削除する

--ビュー「employee_vw」へのSELECT権限を削除する例
REVOKE SELECT ON employee_vw FROM postgres;

列名 型 GENERATED ALWAYS AS 式 STORED

CREATE TABLE test
(   ・・・
    height numeric,   -- 身長
    weight numeric,   -- 体重
    --生成列 : BMI（ 体重　÷　身長（ｍ）の2乗）の作成
    bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
    ・・・
)

CREATE TABLE test
(   ・・・ , 
    --生成列の例1：列「category」による場合分け
    hoge text GENERATED ALWAYS AS (    -- この列の定義はここから
    CASE 
    WHEN category '1' THEN '一般職'
    WHEN category '2' THEN '管理職'
    WHEN category '3' THEN '派遣'
    WHEN category '4' THEN '再雇用' 
    ELSE 'それ以外' END
    ) STORED                          -- ここまでです
    
    , ・・・
    , 

    --生成列の例2 : 誕生日によるクラス分け
    class_name text GENERATED ALWAYS AS
    (
     CASE 
     WHEN birthday between '2016/4/1' and '2017/3/31' THEN 'Aクラス' 
     WHEN birthday between '2017/4/1' and '2018/3/31' THEN 'Bクラス' 
     WHEN birthday between '2018/4/1' and '2019/3/31' THEN 'Cクラス' 
     WHEN birthday between '2019/4/1' and '2020/3/31' THEN 'Dクラス' 
     WHEN birthday between '2020/4/1' and '2021/3/31' THEN 'Eクラス' 
     ELSE '対象外' END) STORED

    , ・・・
)

--列を追加する構文
ALTER TABLE テーブル名 add 列名 型;

--例：「m_school」テーブルに列「address」を追加する
ALTER TABLE m_school add address character varying(100);


--列を削除する構文
ALTER TABLE テーブル名 DROP COLUMN 列名;

--例：「m_school」テーブルの列「address」を削除する
ALTER TABLE m_school DROP COLUMN address;

--桁数を変更する
ALTER TABLE テーブル名 ALTER COLUMN 列名 型;

--例：「student」テーブルの列「height」の定義をnumeric(3,1)に変更する
ALTER TABLE student ALTER COLUMN height TYPE numeric(3,1);

--データベースへコメントを追加する
COMMENT ON DATABASE データベース名 IS 'コメントです'; 

--テーブルへコメントを追加する
COMMENT ON TABLE テーブル名 IS 'コメントです';

--列へコメントを追加する
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです';

--データベースのコメントを削除する
COMMENT ON DATABASE データベース名 IS NULL;

--テーブルのコメントを削除する
COMMENT ON TABLE テーブル名 IS NULL;

--列へコメントのコメントを削除する
COMMENT ON COLUMN テーブル名.列名 IS NULL;

--年月日時分の書式
select to_char(now(),'YYYY/MM/DD HH24:MI:SS'); --2020/04/01 22:34:56

--年月日の書式
select to_char(now(),'YYYYMMDD');       --20200401
select to_char(now(),'YYYY/MM/DD');     --2020/04/01
select to_char(now(),'YYYY.MM.DD');     --2020.04.01
select to_char(now(),'YYYY年MM月DD日');  --2020年04月01日

--月日の書式
select to_char(now(),'MMDD');           --0401
select to_char(now(),'MM/DD');          --04/01
select to_char(now(),'MM.DD');          --04.01
select to_char(now(),'MM月DD日');       --04月01日

--時刻の書式
select to_char(now(), 'HH:MI:SS');      --10:34:56（12時間表記）
select to_char(now(), 'HH24:MI:SS');    --22:34:56（24時間表記）
select to_char(now(), 'HH24時MI分SS秒'); --22時34分56秒

--今日の日付の取得
select current_date;      --"2020-10-01"

--現在時刻の取得（時刻のみ）
select current_time;      --"22:00:00.000000+09"

--現在日時の取得   ※now()とcurrent_timestampの違いはありません
select now();             --"2020-10-01 22:00:00.000000+09"
select current_timestamp; --"2020-10-01 22:00:00.000000+09"

--'YYYY/MM/DD'形式で表示する
select to_char(now(), 'YYYY/MM/DD');  --2020/10/01

--'YYYY/MM/DD HH24:MI:SS'形式で表示する
select to_char(now(), 'YYYY/MM/DD HH24:MI:SS'); --2020/10/01 12:34:56

--ランダム値を生成する（引数は必要ありません）
random() 

--範囲を指定したランダム値を生成する方法
--最小値と最大値のところに値を当てはめて実行して下さい
select round(( random() * (最小値 - 最大値) )::numeric, 0) + 最大値;

select * from random(); --結果例:0.13224（実行する度異なる値が生成）

--範囲指定：最小値=1 , 最大値=100
select round(( random() * (1 - 100) )::numeric, 0) + 100; --結果例:77

--範囲指定：最小値=1 , 最大値=10,000
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --結果例:7000

--余りを計算する構文
mod( 数値，割る数 ) 

--余りを求める
select mod(10,3);  -- 1（ 10 ÷ 3 の余り ）
select mod(5,3);   -- 2（ 5 ÷ 3 の余り ）
select mod(6,3);   -- 0（ 6 ÷ 3 の余り ）

--偶数の時、奇数の時と処理を分ける例
select case when mod(2,1) = 0 then '偶数の時の処理' 
else '奇数の時の処理' end;