테이블 t 가 아래와 같이 주어질때,
row로 주어진 수들의 최대공약수(gcd, greatest common divisor)와 최소공배수(lcm, least common multiple)를 구해보자.
row의 수를 변화시키면서 속도를 고려하여 쿼리를 만들어 보자.
WITH t AS
(SELECT 15 num
FROM DUAL
UNION
SELECT 9
FROM DUAL
UNION
SELECT 42
FROM DUAL)
-- 최대공약수
SELECT level# gcd
FROM (SELECT min_num / LEVEL level#
FROM (SELECT MIN (num) min_num
FROM t)
WHERE TRUNC (min_num / LEVEL) = min_num / LEVEL
CONNECT BY LEVEL <= min_num)
WHERE EXISTS (SELECT 1
FROM t
HAVING SUM (num / level#) = SUM (TRUNC (num / level#)))
AND ROWNUM = 1
-- 최소공배수
SELECT level# lcm
FROM (SELECT max_num * LEVEL level#
FROM (SELECT MAX (num) max_num, EXP (SUM (LN (num))) prod
FROM t)
CONNECT BY LEVEL <= prod)
WHERE EXISTS (SELECT 1
FROM t
HAVING SUM (level# / num) = SUM (TRUNC (level# / num)))
AND ROWNUM = 1