LoginSignup
0
0

More than 3 years have passed since last update.

@ukijumotahaneniarukenia

sql oracle 回転クエリの作成

Last updated at Posted at 2019-11-21

まえがき

oracleのsqlで回転クエリ作成した。X環境の絵文字周りがちょっと難しかった。GUIで表示できた絵文字とそれ以外の絵文字の2つで挑戦した。

環境

[oracle❤af009cfe1ee6 (木 11月 21 23:32:50) ~/script_scratch/oracle]$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 木 11月 21 23:32:53 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 木 11月 21 2019 23:24:39 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。

データ準備

絵文字適当にチョイス。コメントアウトしているinsert文はX環境のsqldevloperでも表示できた。それ以外はコンソール用。

$cat emoji.sql 
--echo -e '\U1f4a'{0..9} | tr ' ' '\n' | xargs -I@ bash -c 'paste <(echo -ne @) <(base64<<<@) <(echo -ne @ | nkf -W8 -w8B0 | xxd -ps -c49) <(echo -ne @ nkf -W8 -w16B0 | xxd -ps -c48 ) <(echo -ne @ | nkf -W8 -w32B0 | xxd -ps -c48 | sed "s;^0*;;")' | awk 'BEGIN{SQT="\x27";print "drop table test_tbl purge;\n""create table test_tbl(emoji nvarchar2(1000),base64 varchar2(100),utf8 varchar2(100),utf16 varchar2(100),utf32 varchar2(100));"}{print "INSERT INTO test_tbl VALUES ("SQT$1SQT","SQT$2SQT","SQT$3SQT","SQT$4SQT","SQT$5SQT");"}END{print "commit;"}'

drop table emoji purge;
drop table tmp purge;
create table tmp(emo nvarchar2(1000));
insert into tmp values ('💠');
insert into tmp values ('💡');
insert into tmp values ('💢');
insert into tmp values ('💣');
insert into tmp values ('💤');
insert into tmp values ('💥');
insert into tmp values ('💦');
insert into tmp values ('💧');
insert into tmp values ('💨');
insert into tmp values ('💩');

--insert into tmp values ('🐭');
--insert into tmp values ('🐮');
--insert into tmp values ('🐱');
--insert into tmp values ('🐵');
--insert into tmp values ('🐿');
--insert into tmp values ('👀');
--insert into tmp values ('👆');
--insert into tmp values ('👇');
--insert into tmp values ('👈');
--insert into tmp values ('👉');
--insert into tmp values ('👊');
commit;
create table emoji as select listagg(emo,',')within group (order by rownum) as emo from tmp;
select * from emoji;
desc emoji;

USER01@ORCLPDB01> @emoji.sql
USER01@ORCLPDB01> column emo format a100
USER01@ORCLPDB01> select * from emoji;

EMO
----------------------------------------------------------------------------------------------------
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩

1行が選択されました。

経過: 00:00:00.00

USER01@ORCLPDB01> desc emoji;
名前  Nullかどうか タイプ                 
--- -------- ------------------- 
EMO          VARCHAR2(4000 CHAR)

スクリプト

回転クエリで使用するファンクション

perlの配列操作で用意されている関数をあとで操作しやすくするためにファンクションとして作成した。

create or replace type liz is table of varchar2(4000);
/

create or replace function split(rsv_args varchar2)
return liz
as
rt liz;
begin
    select
        cast(collect(substr(rsv_args,decode(level-1,0,0,instr(rsv_args,',',1,level-1))+1,decode(instr(rsv_args,',',1,level),0,4000,instr(rsv_args,',',1,level))-decode(level-1,0,0,instr(rsv_args,',',1,level-1))-1)) as liz)
    into rt
    from
        dual
    connect by
        level <=length(rsv_args) - length(replace(rsv_args,',',''))+1;
    return rt;
end;
/

create or replace function rev_split(rsv_args varchar2)
return liz
as
rt liz;
begin
    select
        cast(collect(substr(rsv_args,instr(rsv_args,',',-1,level)+1,decode(level-1,0,4000,instr(rsv_args,',',-1,level-1))-instr(rsv_args,',',-1,level)-1)) as liz)
    into rt
    from
        dual
    connect by
        level <=length(rsv_args) - length(replace(rsv_args,',',''))+1;
    return rt;
end;
/

create or replace function shift(rsv_args liz)
return varchar2
as
rt varchar2(4000);
begin
    select
        case
            when 0<(select count(*) from table(rsv_args)) then (with sub as(select rownum as seq,first_value(column_value)over(order by rownum) as ele from table(rsv_args))select ele from sub where seq=1)
            else null
        end
    into rt from dual;       
    return rt;
end;
/

create or replace function pop(rsv_args liz)
return varchar2
as
rt varchar2(4000);
begin
    select
        case
            when 0<(select count(*) from table(rsv_args)) then (with sub as(select rownum as seq,first_value(column_value)over(order by rownum desc) as ele from table(rsv_args))select ele from sub where seq=1)
            else null
        end
    into rt from dual;       
    return rt;
end;
/

create or replace function unshift(tgt_ele varchar2,rsv_args liz)
return liz
as
rt liz;
begin
    select liz(tgt_ele) multiset union all rsv_args into rt from dual;
    return rt;
end;
/

create or replace function push(tgt_ele varchar2,rsv_args liz)
return liz
as
rt liz;
begin
    select rsv_args multiset union all liz(tgt_ele) into rt from dual;
    return rt;
end;
/

create or replace function del_ele(tgt_ele varchar2,rsv_args liz)
return liz
as
rt liz;
begin
    select
        case
            when trim(tgt_ele) is not null and exists(select 1 from table(rsv_args) where column_value = tgt_ele) then (select cast(collect(column_value)as liz) from table(rsv_args) where column_value <> tgt_ele)
            else rsv_args
        end
    into rt
    from dual;
    return rt;
end;
/

create or replace function cnt(rsv_args liz)
return number
as
rt number;
begin
    select count(*) into rt from table(rsv_args);
    return rt;
end;
/

create or replace function strliz(rsv_args liz)
return varchar2
as
rt varchar2(4000);
begin
    select listagg(column_value,',')within group(order by rownum) into rt from table(rsv_args);
    return rt;
end;
/

--雑にテスト。
select shift(split(' , ')) from dual;
select pop(split('')) from dual;
select unshift('zzzz',(split('a,bb,ccc,dddd'))) from dual;
select unshift('zzzz',(rev_split('a,bb,ccc,dddd'))) from dual;
select push(null,(split('a,bb,ccc,dddd'))) from dual;
select del_ele(null,split('a,bb,ccc,dddd')) from dual;
select cnt(split('')) from dual;

回転クエリ

column vals format a100
with left_stair_rec(seq,rotate_liz)as(
    select 1,push(shift(split(emo)),del_ele(shift(split(emo)),split(emo))) from emoji
    union all
    select s1.seq+1,push(shift(rotate_liz),del_ele(shift(rotate_liz),rotate_liz)) from left_stair_rec s1
    where s1.seq+1 <= cnt(split((select emo from emoji)))
)
,left_stair_tac_rec(seq,rotate_liz)as(
    select 1,push(shift(split(emo)),del_ele(shift(split(emo)),split(emo))) from emoji
    union all
    select s1.seq+1,push(shift(rotate_liz),del_ele(shift(rotate_liz),rotate_liz)) from left_stair_tac_rec s1
    where s1.seq+1 <= cnt(split((select emo from emoji)))
)
,right_stair_rev_rec(seq,rotate_liz)as(
    select 1,unshift(pop(rev_split(emo)),del_ele(pop(rev_split(emo)),rev_split(emo))) from emoji
    union all
    select s1.seq+1,unshift(pop(rotate_liz),del_ele(pop(rotate_liz),rotate_liz)) from right_stair_rev_rec s1
    where s1.seq+1 <= cnt(split((select emo from emoji)))
)
,right_stair_rev_tac_rec(seq,rotate_liz)as(
    select 1,unshift(pop(rev_split(emo)),del_ele(pop(rev_split(emo)),rev_split(emo))) from emoji
    union all
    select s1.seq+1,unshift(pop(rotate_liz),del_ele(pop(rotate_liz),rotate_liz)) from right_stair_rev_tac_rec s1
    where s1.seq+1 <= cnt(split((select emo from emoji)))
)
select
    VALS
from
    (select
        s1.seq
        ,strliz(s1.rotate_liz multiset union all s3.rotate_liz) as upper
        ,strliz(s2.rotate_liz multiset union all s4.rotate_liz) as lower
    from left_stair_rec s1
    inner join (select row_number()over(order by seq desc) as seq ,rotate_liz from left_stair_tac_rec) s2 on s1.seq=s2.seq
    inner join right_stair_rev_rec s3 on s1.seq=s3.seq
    inner join (select row_number()over(order by seq desc) as seq ,rotate_liz from right_stair_rev_tac_rec) s4 on s1.seq=s4.seq
    )unpivot (vals for cols in (upper,lower))
order by
    case when cols='UPPER' then 1 else 2 end
    ,seq
;

image.png

USER01@ORCLPDB01> column vals format a100
VALS
----------------------------------------------------------------------------------------------------
🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮
🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱
🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵
🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿
👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀
👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆
👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇
👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈
👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉
👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭
👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊
👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👈,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉
👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👇,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈
👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👆,👆,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇
👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,👀,👀,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆
👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐿,🐿,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀
🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐵,🐵,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿
🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐱,🐱,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵
🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐮,🐮,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱
🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊,🐭,🐭,👊,👉,👈,👇,👆,👀,🐿,🐵,🐱,🐮

22行が選択されました。

経過: 00:00:00.02

いつもの。いつもよりしっかりしている。（しっかり作ったつもり。）

USER01@ORCLPDB01> @square.sql

VALS
----------------------------------------------------------------------------------------------------
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡

20行が選択されました。

経過: 00:00:00.02

あとがき

行列の楽しさを思い出すいいきっかけになった。^^

いろいろ組み合わせてパタン生成してアート作成できそう。

oracleは面白い!

以上、ありがとうございました。

20191123追記

tlokwengさんがすごい文字列操作投下してきたので、分解してみた。デバッグ用に適当に列増やした。

文字列を逆順に並べるときの操作。イメージはこんな感じ。

後ろカンマつけているのはsubstrの処理内容を統一するため。

$echo a,b,c,d,e | rev
e,d,c,b,a
$echo a,b,c,d,e | rev | rev
a,b,c,d,e

単一行の場合

with sub as(
    select 'a,b,c,d,e,' as tgt from dual
),rec(rn,tgt,tgtgt,tgtgtgt,ele1,ele2,ele3,ele4,ele5,src)as(
    select
        1
        ,tgt
        ,tgt
        ,tgt
        ,substr(tgt, 1, 0)
        ,substr(tgt, -1, 1)
        ,substr(tgt, -1, 1)
        ,substr(tgt, 1, 1)
        ,substr(tgt, 1+1)
        ,tgt
    from sub s1
    union all
    select
        s0.rn+1
        ,substr(s0.tgt, 1, s0.rn-1)||substr(s0.src, -s0.rn, 1)||substr(s0.tgt, s0.rn+1)
        ,substr(s0.tgt, 1, s0.rn-1)||substr(s0.src, -s0.rn, 1)
        ,substr(s0.tgt, 1, s0.rn-1)
        ,substr(s0.tgt, 1, s0.rn-1)
        ,substr(s0.src, -s0.rn, 1)
        ,substr(s0.tgt, -(s0.rn+1), 1)
        ,substr(s0.tgt, s0.rn+1, 1)
        ,substr(s0.tgt, s0.rn+1)
        ,s0.src
    from rec s0
    where s0.rn<=length(s0.src)
)select * from rec
;

最大のRNの行でSRC列の文字列とTGT列の文字列が反転していることがわかる。

image.png

複数行の場合

with sub as(
    select rownum as grp,ele as tgt from (select 'a,b,c,d,e,'  as ele from dual union all select 'e,d,c,b,a,' from dual)
),rec(rn,grp,tgt,tgtgt,tgtgtgt,ele1,ele2,ele3,ele4,ele5,src)as(
    select
        1
        ,grp
        ,tgt
        ,tgt
        ,tgt
        ,substr(tgt, 1, 0)
        ,substr(tgt, -1, 1)
        ,substr(tgt, -1, 1)
        ,substr(tgt, 1, 1)
        ,substr(tgt, 1+1)
        ,tgt
    from sub s1
    union all
    select
        s0.rn+1
        ,grp
        ,substr(s0.tgt, 1, s0.rn-1)||substr(s0.src, -s0.rn, 1)||substr(s0.tgt, s0.rn+1)
        ,substr(s0.tgt, 1, s0.rn-1)||substr(s0.src, -s0.rn, 1)
        ,substr(s0.tgt, 1, s0.rn-1)
        ,substr(s0.tgt, 1, s0.rn-1)
        ,substr(s0.src, -s0.rn, 1)
        ,substr(s0.tgt, -(s0.rn+1), 1)
        ,substr(s0.tgt, s0.rn+1, 1)
        ,substr(s0.tgt, s0.rn+1)
        ,s0.src
    from rec s0
    where s0.rn<=length(s0.src)
)select case when max(rn)over(partition by grp) = s1.rn then 1 else 0 end as flg,max(rn)over(partition by grp) as mx_rn,s1.* from rec s1
;

GRP列ごとの最大のRNを各行にもたせ(MX_RN)、最大値と同じ行番号を保持している行にFLG１を立てている。当該行がSRC列の文字列とTGT列の文字列が反転している。

image.png

みたことない。凄すぎ。

20191123追記その２

これを機に絵文字をUTF16エンコーディングで符号化する変換クエリ作成した。前適当に書いていた記事に元ネタ転がっていたので、流用。

一つのクエリから得られることが多い。ほんとすごいなー。

select * from emoji;

USER01@ORCLPDB01> column EMO for a100
USER01@ORCLPDB01> select * from emoji;

EMO
----------------------------------------------------------------------------------------------------
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊

1行が選択されました。


drop table emoji_enc_done purge;
create table emoji_enc_done as
with sub as(
select
    rownum as grp
    ,emo
    ,replace(regexp_substr(dump(convert(emo,'AL16UTF16'),1016),':.*'),': ','') as liz
    ,split(replace(regexp_substr(dump(convert(emo,'AL16UTF16'),1016),':.*'),': ','')) as liz_done
from (select column_value as emo from emoji,split(emo))
)
,subb as(
select
    grp
    ,row_number()over(partition by grp order by rownum) as grpseq
    ,case when 3>row_number()over(partition by grp order by rownum) then 1 else 2 end as surrogate_grp
    ,emo
    ,liz
    ,liz_done
    ,column_value as item
    ,lpad(column_value,2,0) as item_done
from
    sub,table(liz_done)
)
,subbb as(
select
    grp,surrogate_grp,emo,liz
    ,listagg(item_done)within group (order by grpseq) as surrogate_pair
from
    subb
group by
    grp,surrogate_grp,emo,liz
),subbbb as(
select
    grp,emo
    ,'\'||listagg(surrogate_pair,'\')within group (order by surrogate_grp) as utf16_item_encode
    ,unistr('\'||listagg(surrogate_pair,'\')within group (order by surrogate_grp)) as utf16_item_decode
from
    subbb
group by
    grp,emo
)select 
    listagg(utf16_item_decode,',')within group (order by grp) as emo_dec
    ,listagg(utf16_item_encode,',')within group (order by grp) as emo_enc
from subbbb
;

USER01@ORCLPDB01> column EMO_DEC for a50
USER01@ORCLPDB01> column EMO_ENC for a130
USER01@ORCLPDB01> select * from emoji_enc_done;

EMO_DEC                                            EMO_ENC
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊                    \d83d\dc2d,\d83d\dc2e,\d83d\dc31,\d83d\dc35,\d83d\dc3f,\d83d\dc40,\d83d\dc46,\d83d\dc47,\d83d\dc48,\d83d\dc49,\d83d\dc4a

1行が選択されました。

経過: 00:00:00.00

desc emoji_enc_done;

名前      Nullかどうか タイプ                 
------- -------- ------------------- 
EMO_DEC          VARCHAR2(4000 CHAR) 
EMO_ENC          VARCHAR2(4000)

unistr関数の使用感


drop table non_cast_emoji purge;
create table non_cast_emoji as
select unistr('\d83d\dc2d,\d83d\dc2e,\d83d\dc31,\d83d\dc35,\d83d\dc3f,\d83d\dc40,\d83d\dc46,\d83d\dc47,\d83d\dc48,\d83d\dc49,\d83d\dc4a') as emo from dual;

select table_name, column_name, data_type, data_length,char_length from user_tab_cols where table_name = upper('non_cast_emoji');

desc non_cast_emoji;
名前  Nullかどうか タイプ           
--- -------- ------------- 
EMO          NVARCHAR2(32) 

USER01@ORCLPDB01> select * from non_cast_emoji;

EMO
----------------------------------------------------------------------------------------------------
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊

1行が選択されました。

経過: 00:00:00.01

image.png

cast後

1CHARで1文字分。

drop table cast_done_emoji purge;
create table cast_done_emoji as
select cast(unistr('\d83d\dc2d,\d83d\dc2e,\d83d\dc31,\d83d\dc35,\d83d\dc3f,\d83d\dc40,\d83d\dc46,\d83d\dc47,\d83d\dc48,\d83d\dc49,\d83d\dc4a') as varchar2(22 CHAR)) as emo from dual;

select table_name, column_name, data_type, data_length,char_length from user_tab_cols where table_name = upper('cast_done_emoji');

desc cast_done_emoji;
名前  Nullかどうか タイプ               
--- -------- ----------------- 
EMO          VARCHAR2(22 CHAR) 


USER01@ORCLPDB01> select * from cast_done_emoji;

EMO
----------------------------------------------------------------------------------------------------
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊

1行が選択されました。

経過: 00:00:00.01

image.png

20191123追記その３

コレクションでunion allによる行複写が良くないかも。unpivotで行複写をコレクションでも再現。

厳密には再現は微妙にできていない。まぁいいや。

with sub as(
    select split(emo) as emo from emoji
),rec(seq,pre_lft_liz,pre_rgt_liz)as(
    select 1,emo,rev(emo) from sub
    union all
    select s0.seq+1
    ,push(shift(s0.pre_lft_liz),del_ele(shift(s0.pre_lft_liz),s0.pre_lft_liz))
    ,unshift(pop(s0.pre_rgt_liz),del_ele(pop(s0.pre_rgt_liz),s0.pre_rgt_liz))
    from rec s0,sub s1
    where s0.seq+1 <= cnt(split((select emo from emoji)))
)
select
    dense_rank()over(order by case when cols ='UPPER' then 1 else 2 end) as grp
    ,cols
    ,case when cols ='UPPER' then to_number(s1.seq) else -to_number(s1.seq) end as seq
    ,s1.vals as rotate
from
    (select 
        lpad(seq,length(cnt(split((select emo from emoji)))),0) as seq
        ,strliz(pre_lft_liz)||','||strliz(pre_rgt_liz) as upper
        ,strliz(pre_lft_liz)||','||strliz(pre_rgt_liz) as lower
        from rec
    )unpivot(vals for cols in (upper,lower)) s1
order by
    case when s1.cols ='UPPER' then 1 else 2 end
    ,case when s1.cols ='UPPER' then to_number(s1.seq) else -to_number(s1.seq) end
;

image.png

んー、真ん中に来てくれなかった。悲しい。

USER01@ORCLPDB01> column grp for 9
USER01@ORCLPDB01> column cols for a10
USER01@ORCLPDB01> column seq for 99
USER01@ORCLPDB01> column rotate for a130

GRP COLS       SEQ ROTATE
--- ---------- --- ----------------------------------------------------------------------------------------------------------------------------------
  1 UPPER        1 💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
  1 UPPER        2 💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
  1 UPPER        3 💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
  1 UPPER        4 💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
  1 UPPER        5 💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
  1 UPPER        6 💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
  1 UPPER        7 💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
  1 UPPER        8 💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
  1 UPPER        9 💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
  1 UPPER       10 💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
  2 LOWER      -10 💩,💠,💡,💢,💣,💤,💥,💦,💧,💨,💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
  2 LOWER       -9 💨,💩,💠,💡,💢,💣,💤,💥,💦,💧,💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
  2 LOWER       -8 💧,💨,💩,💠,💡,💢,💣,💤,💥,💦,💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
  2 LOWER       -7 💦,💧,💨,💩,💠,💡,💢,💣,💤,💥,💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
  2 LOWER       -6 💥,💦,💧,💨,💩,💠,💡,💢,💣,💤,💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
  2 LOWER       -5 💤,💥,💦,💧,💨,💩,💠,💡,💢,💣,💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
  2 LOWER       -4 💣,💤,💥,💦,💧,💨,💩,💠,💡,💢,💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
  2 LOWER       -3 💢,💣,💤,💥,💦,💧,💨,💩,💠,💡,💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
  2 LOWER       -2 💡,💢,💣,💤,💥,💦,💧,💨,💩,💠,💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
  2 LOWER       -1 💠,💡,💢,💣,💤,💥,💦,💧,💨,💩,💩,💨,💧,💦,💥,💤,💣,💢,💡,💠

20行が選択されました。
0
0
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0