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 = '田中';
氏名教科点数結果
田中国語80合格
田中数学65不合格
田中英語90合格
田中理科60不合格
田中社会85合格

検索条件

    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 テーブル名;

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

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

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

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


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

    DB=> select 列名, count(*) from テーブル名 group by 列名 having グループに適用される条件;
・例1
    DB=> select student as "名前", sum( score ) as "合計点"
                                            from テーブル名 group by student;
名前合計点
田中380
鈴木385
佐藤380
高橋335
山本345
・例2
    DB=> select round( avg( A.score_sum ), 1 ) as "合計点の平均"
                    from ( select sum( score ) as score_sum from test_tbl group by student ) as A;
合計点の平均
365.0
・例3
    DB=> select student as "名前", sum( score ) as "合計点" from テーブル名
                                            group by student having sum( score ) >= 350;
名前合計点
田中380
鈴木385
佐藤380
・例4
    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;
名前合計点結果
田中380合格
鈴木385合格
佐藤380合格
高橋335不合格
山本345不合格

偏差値

偏差値の算出方法
・プロシージャ
              作成              交換            プロシージャ                         引数
    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
スキーマ名前結果のデータ型引数のデータ型 タイプ
publichensachi_proc IN name character varyingプロシージャ
・実行
    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;