[SQL2017] 그룹 조건별로 랜덤 행 추출

/*
-- Title : [SQL2017] 그룹 조건별로 랜덤 행 추출
-- Reference : 디비랑
-- Tag : newid()
*/

---

■ 데이터 준비(Data Preparation)

DROP TABLE #ttt;

CREATE TABLE #ttt
( id INT NOT NULL identity
, grp varchar(1) NOT NULL
, val INT NOT NULL
);

INSERT INTO #ttt (grp, val)
SELECT 'a', 10 UNION ALL
SELECT 'a', 20 UNION ALL
SELECT 'a', 30 UNION ALL
SELECT 'a', 40;

INSERT INTO #ttt (grp, val)
SELECT 'b', 50 UNION ALL
SELECT 'b', 60 UNION ALL
SELECT 'b', 70 UNION ALL
SELECT 'b', 80 UNION ALL
SELECT 'b', 90 UNION ALL
SELECT 'b', 100;

INSERT INTO #ttt (grp, val)
SELECT 'c', 110 UNION ALL
SELECT 'c', 120;

SELECT *
FROM #ttt

 

■ 그룹별 조건에 따라 랜덤 행 추출

• 그룹수가 1~5개 인 경우 : 랜덤으로 3개 추출
• 그룹수가 6~10개 인 경우 : 랜덤으로 4개 추출

SELECT grp, count(*) "cnt"
FROM #ttt
GROUP BY grp

SELECT *
FROM 
(
     SELECT *                 /*조건에 맞는 대상 선정*/
          , CASE WHEN (grp_cnt BETWEEN 1 AND 5)  AND rnum <= 3 THEN 1
                 WHEN (grp_cnt BETWEEN 6 AND 10) AND rnum <= 4 THEN 1
                 ELSE 0
            END "target"
     FROM 
     (
          SELECT *            /*rnum(nid)를 통한 랜덤 정렬 생성*/
               , ROW_NUMBER() OVER(PARTITION BY grp_cnt ORDER BY nid) "rnum"
          FROM
          (
               SELECT *       /*그룹별 전체 건수 조회*/
                    , count(*) OVER(PARTITION BY grp) "grp_cnt"
                    , newid() "nid"
               FROM #ttt
          ) a
     ) aa
) aaa
WHERE target = 1
ORDER BY grp, val