分析関数

• Oracle

create table tab1(col1 int,col2 int);

insert into tab1 values(1,100);
insert into tab1 values(1,110);
insert into tab1 values(1,120);
insert into tab1 values(2,100);
insert into tab1 values(2,200);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,40);
insert into tab1 values(4,50);

select col1,col2,row_number() over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,row_number() over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,first_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,last_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,lag(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lag(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,lead(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lead(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,sum(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

• MySQL

(8.0)から実装

create table tab1(col1 int,col2 int);

insert into tab1 values(1,100);
insert into tab1 values(1,110);
insert into tab1 values(1,120);
insert into tab1 values(2,100);
insert into tab1 values(2,200);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,40);
insert into tab1 values(4,50);

select col1,col2,row_number() over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,row_number() over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,first_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,last_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,lag(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lag(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,lead(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lead(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,sum(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

• PostgreSQL

create table tab1(col1 int,col2 int);

insert into tab1 values(1,100);
insert into tab1 values(1,110);
insert into tab1 values(1,120);
insert into tab1 values(2,100);
insert into tab1 values(2,200);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,40);
insert into tab1 values(4,50);

select col1,col2,row_number() over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,row_number() over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,first_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,last_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,lag(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lag(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,lead(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lead(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,sum(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

• SQL Server

create table tab1(col1 int,col2 int);

insert into tab1 values(1,100);
insert into tab1 values(1,110);
insert into tab1 values(1,120);
insert into tab1 values(2,100);
insert into tab1 values(2,200);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,10);
insert into tab1 values(3,40);
insert into tab1 values(4,50);

select col1,col2,row_number() over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,row_number() over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,first_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,first_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,last_value(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,last_value(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;

select col1,col2,lag(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lag(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,lead(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,lead(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;

select col1,col2,sum(col2) over(order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2) col3 from tab1 order by col1,col2;
select col1,col2,sum(col2) over(partition by col1 order by col1,col2 rows between unbounded preceding and unbounded following ) col3 from tab1 order by col1,col2;