まえがき
分析関数のexclude current rowオプションを使ったら、回転クエリが出来上がった。
環境
$psql --version
psql (PostgreSQL) 12.0
$bash --version
GNU bash, バージョン 5.0.0(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2019 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL バージョン 3 またはそれ以降 <http://gnu.org/licenses/gpl.html>
This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law
スクリプト
標準入力から与えるデータは以下。
$paste <(seq 1 10) <(printf "%s\n" $(echo {a..j}))
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
このデータを分析関数で扱いやすいようにハンドリングした結果が以下。
$psql -q -v deno=3 -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo {a..j})))'" -U postgres < <(cat<<EOS
with sub as(
select
regexp_split_to_array(s0.ary[s0.ind],'\t')as done
,s0.*
from(
select regexp_split_to_table(:tgt,'\n') as map
,regexp_split_to_array(:tgt,'\n') as ary
,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
)s0
),subb as(
select
s0.ind as seq
,s0.done[2] as str
from sub s0
)select * from subb;
EOS
)
seq │ str
─────┼─────
1 │ a
2 │ b
3 │ c
4 │ d
5 │ e
6 │ f
7 │ g
8 │ h
9 │ i
10 │ j
(10 rows)
これを元にsubbbサブクエリまで実行した結果が以下。
先頭行で生じるNULLを空文字にハンドリングして文字列結合できるようにする。
$psql -q -v deno=3 -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo {a..j})))'" -U postgres < <(cat<<EOS
with sub as(
select
regexp_split_to_array(s0.ary[s0.ind],'\t')as done
,s0.*
from(
select regexp_split_to_table(:tgt,'\n') as map
,regexp_split_to_array(:tgt,'\n') as ary
,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
)s0
),subb as(
select
s0.ind as seq
,s0.done[2] as str
from sub s0
),subbb as(
select
seq
,str
,string_agg(str,',')over(order by seq range between current row and unbounded following ) as ptn1_lft
,string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) as ptn1_rgt
,string_agg(str,',')over(order by seq range between current row and unbounded following )
||case when seq=1 then '' else ',' end
||case when seq=1 then '' else string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) end as ptn1
,reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) as ptn2_lft
,string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2_rgt
,case when seq=1 then '' else reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) end
||case when seq=1 then '' else ',' end
|| string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2
from subb
)select * from subbb;
EOS
)
seq │ str │ ptn1_lft │ ptn1_rgt │ ptn1 │ ptn2_lft │ ptn2_rgt │ ptn2
─────┼─────┼─────────────────────┼───────────────────┼─────────────────────┼───────────────────┼─────────────────────┼─────────────────────
1 │ a │ a,b,c,d,e,f,g,h,i,j │ │ a,b,c,d,e,f,g,h,i,j │ │ j,i,h,g,f,e,d,c,b,a │ j,i,h,g,f,e,d,c,b,a
2 │ b │ b,c,d,e,f,g,h,i,j │ a │ b,c,d,e,f,g,h,i,j,a │ a │ j,i,h,g,f,e,d,c,b │ a,j,i,h,g,f,e,d,c,b
3 │ c │ c,d,e,f,g,h,i,j │ a,b │ c,d,e,f,g,h,i,j,a,b │ b,a │ j,i,h,g,f,e,d,c │ b,a,j,i,h,g,f,e,d,c
4 │ d │ d,e,f,g,h,i,j │ a,b,c │ d,e,f,g,h,i,j,a,b,c │ c,b,a │ j,i,h,g,f,e,d │ c,b,a,j,i,h,g,f,e,d
5 │ e │ e,f,g,h,i,j │ a,b,c,d │ e,f,g,h,i,j,a,b,c,d │ d,c,b,a │ j,i,h,g,f,e │ d,c,b,a,j,i,h,g,f,e
6 │ f │ f,g,h,i,j │ a,b,c,d,e │ f,g,h,i,j,a,b,c,d,e │ e,d,c,b,a │ j,i,h,g,f │ e,d,c,b,a,j,i,h,g,f
7 │ g │ g,h,i,j │ a,b,c,d,e,f │ g,h,i,j,a,b,c,d,e,f │ f,e,d,c,b,a │ j,i,h,g │ f,e,d,c,b,a,j,i,h,g
8 │ h │ h,i,j │ a,b,c,d,e,f,g │ h,i,j,a,b,c,d,e,f,g │ g,f,e,d,c,b,a │ j,i,h │ g,f,e,d,c,b,a,j,i,h
9 │ i │ i,j │ a,b,c,d,e,f,g,h │ i,j,a,b,c,d,e,f,g,h │ h,g,f,e,d,c,b,a │ j,i │ h,g,f,e,d,c,b,a,j,i
10 │ j │ j │ a,b,c,d,e,f,g,h,i │ j,a,b,c,d,e,f,g,h,i │ i,h,g,f,e,d,c,b,a │ j │ i,h,g,f,e,d,c,b,a,j
(10 rows)
最後に上段、下段を作成しておしまい。
$psql -q -v deno=3 -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo {a..j})))'" -U postgres < <(cat<<EOS
with sub as(
select
regexp_split_to_array(s0.ary[s0.ind],'\t')as done
,s0.*
from(
select regexp_split_to_table(:tgt,'\n') as map
,regexp_split_to_array(:tgt,'\n') as ary
,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
)s0
),subb as(
select
s0.ind as seq
,s0.done[2] as str
from sub s0
),subbb as(
select
seq
,str
,string_agg(str,',')over(order by seq range between current row and unbounded following ) as ptn1_lft
,string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) as ptn1_rgt
,string_agg(str,',')over(order by seq range between current row and unbounded following )
||case when seq=1 then '' else ',' end
||case when seq=1 then '' else string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) end as ptn1
,reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) as ptn2_lft
,string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2_rgt
,case when seq=1 then '' else reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) end
||case when seq=1 then '' else ',' end
|| string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2
from subb
),subbbb as(
select 1 as grp,seq as seq,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
union all select 2 as grp,-seq as seq ,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
)select pic from subbbb order by grp,seq
;
EOS
);
pic
─────────────────────────────────────────
a,b,c,d,e,f,g,h,i,j,j,i,h,g,f,e,d,c,b,a
b,c,d,e,f,g,h,i,j,a,a,j,i,h,g,f,e,d,c,b
c,d,e,f,g,h,i,j,a,b,b,a,j,i,h,g,f,e,d,c
d,e,f,g,h,i,j,a,b,c,c,b,a,j,i,h,g,f,e,d
e,f,g,h,i,j,a,b,c,d,d,c,b,a,j,i,h,g,f,e
f,g,h,i,j,a,b,c,d,e,e,d,c,b,a,j,i,h,g,f
g,h,i,j,a,b,c,d,e,f,f,e,d,c,b,a,j,i,h,g
h,i,j,a,b,c,d,e,f,g,g,f,e,d,c,b,a,j,i,h
i,j,a,b,c,d,e,f,g,h,h,g,f,e,d,c,b,a,j,i
j,a,b,c,d,e,f,g,h,i,i,h,g,f,e,d,c,b,a,j
j,a,b,c,d,e,f,g,h,i,i,h,g,f,e,d,c,b,a,j
i,j,a,b,c,d,e,f,g,h,h,g,f,e,d,c,b,a,j,i
h,i,j,a,b,c,d,e,f,g,g,f,e,d,c,b,a,j,i,h
g,h,i,j,a,b,c,d,e,f,f,e,d,c,b,a,j,i,h,g
f,g,h,i,j,a,b,c,d,e,e,d,c,b,a,j,i,h,g,f
e,f,g,h,i,j,a,b,c,d,d,c,b,a,j,i,h,g,f,e
d,e,f,g,h,i,j,a,b,c,c,b,a,j,i,h,g,f,e,d
c,d,e,f,g,h,i,j,a,b,b,a,j,i,h,g,f,e,d,c
b,c,d,e,f,g,h,i,j,a,a,j,i,h,g,f,e,d,c,b
a,b,c,d,e,f,g,h,i,j,j,i,h,g,f,e,d,c,b,a
(20 rows)
最後に絵文字で。標準入力から与える文字列を変更。適当に入れて遊んでみてもいいかも。^^
$psql -q -v deno=3 -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo -e '\U1f4a'{0..9})))'" -U postgres < <(cat<<EOS
with sub as(
select
regexp_split_to_array(s0.ary[s0.ind],'\t')as done
,s0.*
from(
select regexp_split_to_table(:tgt,'\n') as map
,regexp_split_to_array(:tgt,'\n') as ary
,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
)s0
),subb as(
select
s0.ind as seq
,s0.done[2] as str
from sub s0
),subbb as(
select
seq
,str
,string_agg(str,',')over(order by seq range between current row and unbounded following ) as ptn1_lft
,string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) as ptn1_rgt
,string_agg(str,',')over(order by seq range between current row and unbounded following )
||case when seq=1 then '' else ',' end
||case when seq=1 then '' else string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) end as ptn1
,reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) as ptn2_lft
,string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2_rgt
,case when seq=1 then '' else reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) end
||case when seq=1 then '' else ',' end
|| string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2
from subb
),subbbb as(
select 1 as grp,seq as seq,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
union all select 2 as grp,-seq as seq ,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
)select pic from subbbb order by grp,seq
;
EOS
);
pic
─────────────────────────────────────────
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
(20 rows)
これもいける。大迫力。
$psql -q -v deno=3 -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo -e '\U1f4a'{{0..9},{A..F}} '\U1f4b'{{0..9},{A..F}} )))'" -U postgres < <(cat<<EOS
with sub as(
select
regexp_split_to_array(s0.ary[s0.ind],'\t')as done
,s0.*
from(
select regexp_split_to_table(:tgt,'\n') as map
,regexp_split_to_array(:tgt,'\n') as ary
,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
)s0
),subb as(
select
s0.ind as seq
,s0.done[2] as str
from sub s0
),subbb as(
select
seq
,str
,string_agg(str,',')over(order by seq range between current row and unbounded following ) as ptn1_lft
,string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) as ptn1_rgt
,string_agg(str,',')over(order by seq range between current row and unbounded following )
||case when seq=1 then '' else ',' end
||case when seq=1 then '' else string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row) end as ptn1
,reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) as ptn2_lft
,string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2_rgt
,case when seq=1 then '' else reverse(string_agg(str,',')over(order by seq range between unbounded preceding and current row exclude current row)) end
||case when seq=1 then '' else ',' end
|| string_agg(str,',')over(order by seq desc range between unbounded preceding and current row ) as ptn2
from subb
),subbbb as(
select 1 as grp,seq as seq,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
union all select 2 as grp,-seq as seq ,ptn1,ptn2,ptn1||','||ptn2 as pic from subbb
)select pic from subbbb order by grp,seq
;
EOS
);
pic
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡
💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢
💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣
💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤
💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥
💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦
💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧
💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨
💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩
💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪
💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫
💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬
💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭
💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮
💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯
💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰
💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱
💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲
💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳
💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴
💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵
💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶
💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷
💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸
💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹
💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺
💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻
💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼
💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽
💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾
💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿
💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿
💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾
💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽
💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼
💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻
💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺
💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹
💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸
💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷
💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶
💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵
💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴
💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳
💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲
💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱
💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰
💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯
💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮
💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭
💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬
💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫
💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪
💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩
💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨
💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧
💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦
💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥
💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💣,💣,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤
💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💢,💢,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣
💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💡,💡,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢
💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💠,💠,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💪,💫,💬,💭,💮,💯,💰,💱,💲,💳,💴,💵,💶,💷,💸,💹,💺,💻,💼,💽,💾,💿,💿,💾,💽,💼,💻,💺,💹,💸,💷,💶,💵,💴,💳,💲,💱,💰,💯,💮,💭,💬,💫,💪,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
(64 rows)
あとがき
こういうオプションはマニアックかもしれないけど、使いどころいろいろありそう。
partition byでグルーピングごとに作成したら、もっと細かく制御できそう。いろいろ試していこうと思う。
以上、ありがとうございました。