[Oracle] 두 테이블 곱하기 : 길이가 다른 테이블을 from절에 두면, 두개의 테이블이 곱해진다 (랜덤 데이터 생성에 응용)

SQL developer 열어서 직접 확인하면서 하길 바람.

그래야 의미가 다가온다.

 

 

CREATE TABLE TMP_REG(
    REG VARCHAR2(10)
    ,RATE NUMBER(3,1)
);
DELETE FROM TMP_REG;
INSERT INTO TMP_REG
    SELECT '서울' AS REG,  18.8 AS RATE FROM DUAL UNION ALL
    SELECT '부산' , 6.6 AS RATE FROM DUAL UNION ALL
    SELECT '대구' , 4.8 AS RATE FROM DUAL UNION ALL
    SELECT '인천' , 5.7 AS RATE FROM DUAL UNION ALL
    SELECT '광주' , 2.9 AS RATE FROM DUAL UNION ALL
    SELECT '대전' , 2.9 AS RATE FROM DUAL UNION ALL
    SELECT '울산' , 2.4 AS RATE FROM DUAL UNION ALL
    SELECT '세종' , 0.7 AS RATE FROM DUAL UNION ALL
    SELECT '경기' , 25.2 AS RATE FROM DUAL UNION ALL
    SELECT '강원' , 3.0 AS RATE FROM DUAL UNION ALL
    SELECT '충북' , 3.1 AS RATE FROM DUAL UNION ALL
    SELECT '충남' , 4.1 AS RATE FROM DUAL UNION ALL
    SELECT '전북' , 3.5 AS RATE FROM DUAL UNION ALL
    SELECT '전남' , 3.5 AS RATE FROM DUAL UNION ALL
    SELECT '경북' , 5.1 AS RATE FROM DUAL UNION ALL
    SELECT '경남' , 6.5 AS RATE FROM DUAL UNION ALL
    SELECT '제주' , 1.3 AS RATE FROM DUAL;
    --SELECT '제주' , 7 AS RATE FROM DUAL;
    
SELECT * FROM TMP_REG;


-- 17(도시 개수) * 100 = 1700건 조회
SELECT * 
FROM TMP_REG,
            ( SELECT LEVEL AS LV 
            FROM DUAL 
            CONNECT BY LEVEL <= 100
            ) B;


SELECT * 
FROM TMP_REG,
            ( SELECT LEVEL AS LV 
            FROM DUAL 
            CONNECT BY LEVEL <= 100
            ) B
WHERE RATE >= LV;


SELECT * 
FROM TMP_REG,
            ( SELECT LEVEL AS LV 
            FROM DUAL 
            CONNECT BY LEVEL <= 1000 
            -- level 증가한 비율만큼 rate도 곱해줘야 의미있는듯!
            -- 단순히 level만 늘리면, 어차피 조회되는 건수는 level 100과 같게 나온다.
            ) B
WHERE RATE >= LV;


SELECT * 
FROM TMP_REG,
            ( SELECT LEVEL AS LV 
            FROM DUAL 
            CONNECT BY LEVEL <= 100
            ) B
WHERE RATE >= LV
ORDER BY DBMS_RANDOM.VALUE;
        
        


SELECT * FROM CARD_CONTRACT ORDER BY 1; -- 숫자만 쓰면 해당 인덱스의 컬럼을 기준으로 정렬

 

 

 

가중치를 둬서 지역 추출하는 쿼리

WITH T AS
(
SELECT '서울' AS REG,  18.8 AS RATE FROM DUAL UNION ALL
SELECT '부산' , 6.6 AS RATE FROM DUAL UNION ALL
SELECT '대구' , 4.8 AS RATE FROM DUAL UNION ALL
SELECT '인천' , 5.7 AS RATE FROM DUAL UNION ALL
SELECT '광주' , 2.9 AS RATE FROM DUAL UNION ALL
SELECT '대전' , 2.9 AS RATE FROM DUAL UNION ALL
SELECT '울산' , 2.4 AS RATE FROM DUAL UNION ALL
SELECT '세종' , 0.7 AS RATE FROM DUAL UNION ALL
SELECT '경기' , 25.2 AS RATE FROM DUAL UNION ALL
SELECT '강원' , 3.0 AS RATE FROM DUAL UNION ALL
SELECT '충북' , 3.1 AS RATE FROM DUAL UNION ALL
SELECT '충남' , 4.1 AS RATE FROM DUAL UNION ALL
SELECT '전북' , 3.5 AS RATE FROM DUAL UNION ALL
SELECT '전남' , 3.5 AS RATE FROM DUAL UNION ALL
SELECT '경북' , 5.1 AS RATE FROM DUAL UNION ALL
SELECT '경남' , 6.5 AS RATE FROM DUAL UNION ALL
SELECT '제주' , 1.3 AS RATE FROM DUAL
)
SELECT REG 
FROM ( 
        SELECT * FROM T A,
                ( SELECT LEVEL AS LV 
                    FROM DUAL 
                    CONNECT BY LEVEL <= 100) B
        WHERE RATE >= LV
        ORDER BY DBMS_RANDOM.VALUE
        
        )
WHERE ROWNUM <= 1;