この記事は約8分20秒で読むことができます。

割り算を視覚的に表現する

参考文献

qiitaにもupしてみた

pivot句のinにインライン埋め込めない(埋め込みたい)

ハードで数値リテラル列挙(したくない)

コード表示

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
            )
        )
ORDER BY
    grp
;

in句にインライン(埋め込みたい)

コード表示

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (
                SELECT
                    level
                FROM
                    dual
                CONNECT BY
                    level <=:divisor
                )
        )
ORDER BY
    grp
;

妥協して組み立てた。

コード表示

--divisor:19
--dividend:78

WITH presni AS (
    SELECT
        'with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in ('
AS pre_sni
    FROM
        dual
), liz AS (
    SELECT
        LISTAGG(rn, ',') WITHIN GROUP(
            ORDER BY
                ROWNUM
        ) AS liz
    FROM
        (
            SELECT
                level AS rn
            FROM
                dual
            CONNECT BY
                level <=:divisor
        )
), postsni AS (
    SELECT
        ')) order by grp;' AS post_sni
    FROM
        dual
) SELECT
    s1.pre_sni
    || s2.liz
    || s3.post_sni AS build_sql
  FROM
    presni s1
    , liz s2
    , postsni s3;


with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)) order by grp;

Leave a Reply

Your email address will not be published. Required fields are marked *