Sequence of Right-handed helix

[원쿼리맨]

Sequence of Right-handed helix

built-in 함수만을 이용한 sequence를 만들기 위한 중간 과정이다.
(여기서는 line과 col중에 line만을 만들었다.)



-- 완성1
SELECT   ABS (lev_new - (1 * (n_new - 1) + 1)) / 2
       - ABS (lev_new - (2 * (n_new - 1) + 1)) / 2
       - ABS (lev_new - (3 * (n_new - 1) + 1)) / 2
       - lev_new / 2
       + (4 * n_new - 1) / 2
       + grp
       - 1 seq
  FROM (SELECT grp, n + 2 * (1 - grp) n_new,
               ROW_NUMBER () OVER (PARTITION BY grp ORDER BY 1) lev_new
          FROM (SELECT     n, CEIL ((n - SQRT (n * n - LEVEL)) / 2) grp
                      FROM (SELECT 5 n
                              FROM DUAL)
                CONNECT BY LEVEL <= n * n))

혹은

with t as (
select 5 n from dual)
SELECT   ABS (lev_new - (1 * (n_new - 1) + 1)) / 2
       - ABS (lev_new - (2 * (n_new - 1) + 1)) / 2
       - ABS (lev_new - (3 * (n_new - 1) + 1)) / 2
       - lev_new / 2
       + (4 * n_new - 1) / 2
       + grp
       - 1 seq
  FROM (SELECT grp, n + 2 * (1 - grp) n_new,
               ROW_NUMBER () OVER (PARTITION BY grp ORDER BY 1) lev_new
          FROM (SELECT     n, CEIL ((n - SQRT (n * n - LEVEL)) / 2) grp
                      FROM t
                CONNECT BY LEVEL <= n * n))






-- 중간결과1
SELECT       ABS (LEVEL - (1 * (n - 1) + 1)) / 2
           - ABS (LEVEL - (2 * (n - 1) + 1)) / 2
           - ABS (LEVEL - (3 * (n - 1) + 1)) / 2
           - LEVEL / 2
           + (4 * n - 1) / 2 a,
           CEIL ((n - SQRT (n * n - LEVEL)) / 2) b,
           ROW_NUMBER () OVER (PARTITION BY CEIL (  (n - SQRT (n * n - LEVEL)
                                                    )
                                                  / 2
                                                 ) ORDER BY LEVEL) c
      FROM (SELECT 5 n
              FROM DUAL)
CONNECT BY LEVEL <= n * n