トップページに戻る    PostgreSQL window関数メモ

PostgreSQLメモ

MySQLのGroup_Concat関数を配列型で模倣する方法(ソート機能あり)

create table stringTable(ID,Val) as
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'d' union all
select 2,'e' union all
select 2,'e';

-- マニュアル 4.2.10. 配列コンストラクタ
select ID,
array_to_string
(array(select b.Val
         from stringTable b
        where b.ID = a.ID
       order by b.Val),',') as strAgg
  from stringTable a
group by ID;

 ID | stragg
----+--------
  1 | a,b,c
  2 | d,e,e


MySQLのGroup_Concat関数をXML関数で模倣する方法(ソート機能なし)

-- マニュアル 9.14. XML関数
select ID,
replace(
replace(
        replace(xmlagg(xmlforest(val))::text,'</val><val>',','),
'</val>',''),
'<val>' ,'') as strAgg
  from stringTable
group by ID;


MySQLのGroup_Concat関数を再帰with句で模倣する方法(ソート機能あり)

with RECURSIVE RowN(ID,Val,rn,TreeHeight) as(
select ID,Val,
Row_Number() over(partition by ID order by Val) as rn,
count(*) over(partition by ID) as TreeHeight
  from stringTable),
w(ID,Val,rn,TreeHeight,sysConn) as(
select ID,Val,rn,TreeHeight,Val
  from RowN
 where rn = 1
union all
select w.ID,w.Val,b.rn,w.TreeHeight,
w.sysConn || ',' || b.Val
  from w,RowN b
 where w.ID = b.ID
   and w.rn = b.rn-1)
select ID,sysConn from w
 where rn = TreeHeight;


MySQLのGroup_Concat関数をarray_agg関数で模倣する方法(ソート機能なし)

Aggregate Functions

select ID,
array_to_string(array_agg(Val),',') as strAgg
  from stringTable a
group by ID;

distinct onの使用例

select distinct on (ColA,ColB) ColA,ColB,Val
from (select 1 as ColA,1 as ColB,100 as Val union
      select 1 as ColA,1 as ColB,200 as Val union
      select 1 as ColA,1 as ColB,300 as Val union
      select 2 as ColA,1 as ColB,400 as Val union
      select 2 as ColA,1 as ColB,500 as Val union
      select 2 as ColA,2 as ColB,600 as Val) a;

ColA  ColB  Val
----  ----  ---
   1     1  100
   2     1  400
   2     2  600

select distinct on(ID) Val
from (select 1 as ID,9 as Val union all
      select 1 as ID,9 as Val union all
      select 1 as ID,6 as Val union all
      select 2 as ID,3 as Val union all
      select 2 as ID,3 as Val union all
      select 2 as ID,5 as Val) a
order by ID,Val;

 val
-----
   6
   3

PostgreSQLのCUIツール PSQL

PostgreSQLのCUIツールのPSQLでは、
EmEditorでSQLを作成してコピー
PSQLで右クリックでペーストして実行
といったことが可能(SQLが複数行でもOK)

PSQLのコマンド
\z
テーブル一覧を表示

\d TableName
テーブル構造を表示

\pset null 'null'
nullをnullと表示

\h
SQLのヘルプ

\h select
select文のヘルプ

\r
入力途中のSQLの入力中止

bool_and関数とbool_or関数

PostgreSQLは、全称肯定命題と存在肯定命題に使える集合関数を持ってることに驚きました。
is not true述語を使えば、全称否定命題と存在否定命題にもなりますね。

window関数としても使えます。
bool型を返すので、where句,having句,検索case式のwhen句,order by句で直接使うとよさげだ。

with w(ID,val) as(
select 1,10 union all
select 1,20 union all
select 1,30 union all
select 2,10 union all
select 2,20 union all
select 3,20 union all
select 3,20)
select ID,bool_and(val=20) as IsAll20
  from w
group by ID
having bool_or(val=10);

 ID | IsAll20
----+---------
  1 | f
  2 | f

with w(ID,val) as(
select 1,10 union all
select 1,20 union all
select 1,30 union all
select 2,10 union all
select 2,20 union all
select 3,20 union all
select 3,20 union all
select 4,90)
select ID,val,
case when bool_and(val=20) over(partition by ID) then 'ALL20'
     when bool_or(val=20)  over(partition by ID) then 'Latest one is 20'
     else 'There is no 20' end as res
  from w;

 ID | val |       res
----+-----+------------------
  1 |  10 | Latest one is 20
  1 |  20 | Latest one is 20
  1 |  30 | Latest one is 20
  2 |  10 | Latest one is 20
  2 |  20 | Latest one is 20
  3 |  20 | ALL20
  3 |  20 | ALL20
  4 |  90 | There is no 20

Limit句つきのorder by句でwindow関数

select ID,Val
  from (select 1 as ID,2 as Val union all
        select 1 as ID,3 as Val union all
        select 2 as ID,6 as Val union all
        select 2 as ID,8 as Val union all
        select 3 as ID,0 as Val union all
        select 3 as ID,9 as Val) a
order by sum(Val) over(partition by ID) desc,Val desc Limit 1;

 ID | Val
----+-----
  2 |   8

OverLaps述語

select count(*),
count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
count(nullif("OVERLAPS1","OVERLAPS5")) as OL5,
count(nullif("OVERLAPS1","OVERLAPS6")) as OL6,
count(nullif("OVERLAPS1","OVERLAPS7")) as OL7
from (select d1,d2,d3,d4,
      (d1,d2) OVERLAPS (d3,d4) as "OVERLAPS1",
      d3 <  d2 and d1 <  d4 as "OVERLAPS2",
      d3 <  d2 and d1 <= d4 as "OVERLAPS3",
      d3 <= d2 and d1 <  d4 as "OVERLAPS4",
      d3 <= d2 and d1 <= d4 as "OVERLAPS5",
      d3 <  d2 and d1 <  d4 or
      d1=d3 and d1=d4 as "OVERLAPS6",
      d3 <  d2 and d1 <  d4 or
      d1=d3 and d1=d4 or
      d3=d1 and d3=d2 as "OVERLAPS7"
from (select Least(d1,d2) as d1,
      greatest(d1,d2) as d2,
      Least(d3,d4) as d3,
      greatest(d3,d4) as d4
      from (select
            date_trunc('day',now()+random()*50 * (interval '1 day')) as d1,
            date_trunc('day',now()+random()*50 * (interval '1 day')) as d2,
            date_trunc('day',now()+random()*50 * (interval '1 day')) as d3,
            date_trunc('day',now()+random()*50 * (interval '1 day')) as d4
            from generate_series(1,200000)) a) b) c;

 count  | ol2 | ol3  | ol4  | ol5  | ol6 | ol7
--------+-----+------+------+------+-----+-----
 200000 | 175 | 2612 | 2655 | 5089 |  69 |   0

OracleSQLパズル 10-226  OverLaps述語

age関数

select extract(year from age(date '1993-03-01',date '1992-02-29'));
 date_part
-----------
         1

select extract(year from age(date '1993-02-28',date '1992-02-29'));
 date_part
-----------
         0

RegExp_Replace関数

select RegExp_Replace('ababaabbaa','a(?=b)','X') as R1,
       RegExp_Replace('ababaabbaa','a(?=b)','X','g') as R2,
       RegExp_Replace('ababaabbaa','a(?!b)','X','g') as R3;

     r1     |     r2     |     r3
------------+------------+------------
 Xbabaabbaa | XbXbaXbbaa | ababXabbXX

9.7. パターンマッチ

*************************************************************
先読みのネストも可能

select RegExp_Replace('abaabbbaaa','a(?!b*(?!a))','*','g');

 regexp_replace
----------------
 ab*abbb**a

前後60日を表示

select to_char(now()+
               ((-30+Row_Number() over()) * (interval '1 day'))
              ,'yyyy-mm-dd day') as d
  from generate_series(1,60);

select to_char(generate_series,'yyyy-mm-dd day') as d
  from generate_series(now() - interval '30 day',
                       now() + interval '30 day',
                       interval '1 day');

配列の比較に関する実験

9.17. 配列関数と演算子

select array[1,2] <@ array[2,3,1] as tes1,
       array[1,2] @> array[2,3,1] as tes2;

 tes1 | tes2
------+------
 t    | f

@が大きい○だとイメージすると分かりやすそう。

*************************************************************
select array[1,2,3] <@ array[2,3,1] as tes3,
       array[1,2,3] @> array[2,3,1] as tes4,
       array[1,2,3]  = array[2,3,1] as tes5,
       array[1,2,3]  = array[1,2,3] as tes6;

 tes3 | tes4 | tes5 | tes6
------+------+------+------
 t    | t    | f    | t

@では、順序は問わないようですが、
=では、順序を問うようです。

*************************************************************
select array[1,2,3] <@ array[1,1,1,2,2,3] as tes7,
       array[1,2,3] @> array[1,1,1,2,2,3] as tes8;

 tes7 | tes8
------+------
 t    | t

@では、要素の重複は問わないようです。

Valuesリストの使用例

7.7. VALUESリスト

create table valuesT1(ID,Val) as
select 10,100 union
select 10,200 union
select 10,300;

create table valuesT2(ID,Val) as
values(10,100),
      (10,200),
      (10,300);

with W(ID,Val) as(
Values(111,222),
      (555,666),
      (777,888),
      (333,444),
      (999,000))
select*from W;

sum関数の引数にinterVal型

9.18. 集約関数

select extract(hour from sum(Val)) as hour
from (select interval ' 5 minute' as Val union all
      select interval '30 minute' union all
      select interval '25 minute') a;

 hour
------
    1

集約関数での、複数列のdistinctなcount

with tmp(ID,Val1,Val2) as(
values('AA',1,1),
      ('AA',1,1),
      ('AA',2,2),
      ('BB',1,2),
      ('BB',2,1),
      ('BB',2,1),
      ('BB',3,1))
select ID,count(distinct Row(Val1,Val2)) as disCnt
  from tmp
group by ID;

 id | discnt
----+--------
 AA |      2
 BB |      3

array_agg関数でexists述語での自己結合の代用 (orのケース)

create table PosGra(StaPos,EndPos) as(
values( 1, 2),
      ( 3, 4),
      ( 4, 7),
      ( 6, 7),
      ( 7, 8),
      ( 8, 9),
      (10,12),
      (30,40));

select *
  from PosGra a
 where not exists(select 1 from PosGra b
                   where b.StaPos = a.EndPos
                      or b.EndPos = a.StaPos);

select StaPos,EndPos
from (select StaPos,EndPos,
      Not(StaPos = any(array_agg(EndPos) over())
       or EndPos = any(array_agg(StaPos) over())) as willOut
      from PosGra) a
where willOut;

array_agg関数でexists述語での自己結合の代用 (andのケース)

create table findRev(Key1,Key2,Val) as(
values(1,4,'AAA'),
      (1,3,'BBB'),
      (2,3,'CCC'),
      (2,4,'DDD'),
      (3,1,'EEE'),
      (3,9,'FFF'),
      (4,1,'GGG'),
      (6,8,'HHH'),
      (7,2,'III'));

select *
  from findRev a
 where exists(select 1 from findRev b
               where b.Key1 = a.Key2
                 and b.Key2 = a.Key1);

select Key1,Key2,Val
from (select Key1,Key2,Val,
      array_agg(Row(Key1,Key2)) over() as ArrRow
        from findRev) a
where Row(Key2,Key1) = any(ArrRow);

PostgreSQL9.4で追加された、列なしselect文の使用例

Exists述語で使い道がありそう

with t(Val) as(values('A'))
select *
  from t
 where exists(select from t);

val
---
  A