PostgreSQL（検索）

検索

    DB=> select 列名, 列名, ...  from テーブル名;

結果から重複を取り除く

    DB=> select distinct * from テーブル名;

結果の制限

    指定行数だけ取得

    DB=> select * from テーブル名 limit 行数;

結果の並べ替え

・指定列に対して昇順

    DB=> select * from テーブル名 order by 列名;

・指定列に対して降順

    DB=> select * from テーブル名 order by 列名 desc;

・列を複数指定

    DB=> select * from テーブル名 order by 列名 desc, 列名;

連結

    DB=> select distinct student || ' 君' as "名前" from テーブル名;

           名前       
    ------------------
     田中 君
     鈴木 君
     佐藤 君
     高橋 君
     山本 君

select 文中の条件式

    DB=> select student as "名前", subject as "教科", score as "点数",
                case
                    when score >= 70 then '合格'
                    when score < 70 then '不合格'
                    else null
                end as "結果"
            from テーブル名 where student = '田中';

検索条件

    DB=> select * from テーブル名 where 条件式;

    where 句で使用できる演算子

    =, >, <, >=, <=, !=（<>）, and, or, not, in, like, between 最小値 and 最大値 など

IS NULL, IS NOT NULL

・IS NULL（空である）

    DB=> select * from テーブル名 where 列名 is null;

・IS NOT NULL（空でない）

    DB=> select * from テーブル名 where 列名 is not null;

NULL 値の変換

    DB=> select coalesce(列名, '空') from テーブル名;

IN, NOT IN, ALL

・IN（どれか）

    DB=> select * from テーブル名 where 列名 in (1, 2, 3);

・NOT IN（以外）

    DB=> select * from テーブル名 where 列名 not in (1, 2, 3);

・ALL（全て）

    DB=> select * from テーブル名 where 列名 != all (array[1,2,3]);

副問い合わせ（サブクエリ）

    最終レコードのデータを取得、（）の中が先に処理される

    DB=> select * from テーブル名 where no = ( select max(no) from テーブル名 );

あいまい検索

    DB=> select * from テーブル名 where 列名 like '%文字列%';

    '%文字列%'          含まれる
    '%文字列'           終わりが一致
    '文字列%'           始まりが一致
    '文字列_文字列'     _（アンダースコア）は、任意の一文字に一致

    _（アンダースコア）を文字として扱いたい場合は、\ を付けてエスケープする

正規表現

    DB=> select * from テーブル名 where 列名 ~ 'パターン';

集計関数

・行数をカウント

    DB=> select count(列名) from テーブル名;

・最大値

    DB=> select max(列名) from テーブル名;

・最小値

    DB=> select min(列名) from テーブル名;

・合計値

    DB=> select sum(列名) from テーブル名;

・平均

    DB=> select avg(列名) from テーブル名;

・合計値の平均

    DB=> select avg( A.score_sum ) 
                    from ( select sum( score ) as score_sum from test_tbl group by student ) as A;

・標準偏差

    DB=> select stddev(列名) from テーブル名;

整数同士の割り算

整数同士の割り算は、必ず結果が整数になる

    DB=> select 10 / 3;

      3

整数::numeric で整数を numeric 型に変換

    DB=> select 10 /  3::numeric;

      3.3333333333333333

    DB=> select round( 10 / 3::numeric, 1 );

      3.3

0 除算エラー回避

    DB=> select 10 / 0::numeric;

      ERROR:  division by zero

    DB=> select 10 / nullif( 0::numeric, 0 );

グループ化とグループに対する条件

group by は、指定した列の値をグループ化

    DB=> select 列名, count(*) from テーブル名 group by 列名;

複数指定する場合は、 group by 列名, 列名, ...;

having は where と同じ条件、where が行に適用されるのに対し、having はグループに適用される。

    DB=> select 列名, count(*) from テーブル名 group by 列名 having グループに適用される条件;

    DB=> select student as "名前", sum( score ) as "合計点"
                                            from テーブル名 group by student;

    DB=> select round( avg( A.score_sum ), 1 ) as "合計点の平均"
                    from ( select sum( score ) as score_sum from test_tbl group by student ) as A;

    DB=> select student as "名前", sum( score ) as "合計点" from テーブル名
                                            group by student having sum( score ) >= 350;

    DB=> select student as "名前", sum( score ) as "合計点",
                    case 
                            when sum( score ) >= 350 then '合格'
                            when sum( score ) < 350 then '不合格'
                            else null
                    end as "結果"
              from テーブル名 group by student;

偏差値

              作成              交換            プロシージャ                         引数
    DB=> CREATE OR REPLACE PROCEDURE hensachi_proc(name varchar(4))
         AS $$
         DECLARE
             kamoku_list varchar(4)[]; kamoku varchar(4);
             avg1 numeric(3,1); stddev1 numeric(3,1); score1 int;
             hensachi1 numeric(3,1);
             avg5 numeric(4,1); stddev5 numeric(3,1); score5 int;
             hensachi5 numeric(3,1);
         BEGIN
             kamoku_list := array['国語', '数学', '英語', '理科', '社会'];

             FOREACH kamoku IN ARRAY kamoku_list
             LOOP

                 RAISE INFO '%', kamoku;
                 select into avg1 round( avg( score ), 1 ) from test_tbl where subject = kamoku;
                 select into stddev1 round( stddev( score ), 1 ) from test_tbl where subject = kamoku;
                 select into score1 score from test_tbl where student = name and subject = kamoku;
                 select into hensachi1 round( 50 + 10 * ( (score1 - avg1) / stddev1 ), 1 );
                 RAISE NOTICE '平均 %, 標準偏差 %, % 点数 % 偏差値 %',
                                                        avg1, stddev1, name, score1, hensachi1;

             END LOOP;

             RAISE INFO '5科目';
             select into avg5 round( avg(A.score_sum), 1 )
                        from (select sum( score ) as score_sum from test_tbl group by student) as A;
             select into stddev5 round( stddev(B.score_sum), 1 )
                        from (select sum( score ) as score_sum from test_tbl group by student) as B;
             select into score5 sum( score ) from test_tbl where student = name group by student;
             select into hensachi5 round( 50 + 10 * ( (score5 - avg5) / stddev5 ), 1 );
             RAISE NOTICE '平均 %, 標準偏差 %, % 合計 % 偏差値 %',
                                                        avg5, stddev5, name, score5, hensachi5;

         END;
         $$
         LANGUAGE plpgsql;

    ※ RAISE INFO '%', 変数名;  # 変数の中身を表示（% に変数の値がセットされる）

    DB=> \df

    DB=> call hensachi_proc ('田中');

    INFO:  国語
    NOTICE:  平均 74.0, 標準偏差 11.4, 田中 点数 80 偏差値 55.3
    INFO:  数学
    NOTICE:  平均 68.0, 標準偏差 7.6, 田中 点数 65 偏差値 46.1
    INFO:  英語
    NOTICE:  平均 83.0, 標準偏差 8.4, 田中 点数 90 偏差値 58.3
    INFO:  理科
    NOTICE:  平均 65.0, 標準偏差 12.7, 田中 点数 60 偏差値 46.1
    INFO:  社会
    NOTICE:  平均 75.0, 標準偏差 9.4, 田中 点数 85 偏差値 60.6
    INFO:  5科目
    NOTICE:  平均 365.0, 標準偏差 23.2, 田中 合計 380 偏差値 56.5
    CALL

    DB=> drop procedure hensachi_proc;