원본 테이블의 데이터가 어떤 방식으로 저장되어 있나요?
랭킹을 매기는 기준이 어떻게 되나요?
5월은 31일까지 있는데? 30일까지만 조회하나요?

안녕하세요 마농님.

네 날짜는 임의로 1일부터~30일 or 31 상관없습니다.

랭킹 기준은 sum(컬럼값1) 으로 숫자가 제일높은 기준입니다.

답변 달아주셔서 감사합니다^^

WITH t0 AS
(
SELECT DENSE_RANK() OVER(ORDER BY test_date) dr
     , test_date
     , test_id
     , SUM(v) v
  FROM test_table
 WHERE test_date BETWEEN '20180501' AND '20180530'
 GROUP BY test_date, test_id
)
, t1 AS
(
SELECT dr
     , test_date
     , test_id
     , v
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0
 WHERE dr = 1
)
, t2 AS
(
SELECT *
  FROM t1
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 2
-- AND (SELECT COUNT(*) FROM t1 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t3 AS
(
SELECT *
  FROM t2
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 3
-- AND (SELECT COUNT(*) FROM t2 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t4 AS
(
SELECT *
  FROM t3
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 4
   AND (SELECT COUNT(*) FROM t3 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t5 AS
(
SELECT *
  FROM t4
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 5
   AND (SELECT COUNT(*) FROM t4 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t6 AS
(
SELECT *
  FROM t5
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 6
   AND (SELECT COUNT(*) FROM t5 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t7 AS
(
SELECT *
  FROM t6
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 7
   AND (SELECT COUNT(*) FROM t6 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t8 AS
(
SELECT *
  FROM t7
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 8
   AND (SELECT COUNT(*) FROM t7 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t9 AS
(
SELECT *
  FROM t8
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 9
   AND (SELECT COUNT(*) FROM t8 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t10 AS
(
SELECT *
  FROM t9
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 10
   AND (SELECT COUNT(*) FROM t9 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t11 AS
(
SELECT *
  FROM t10
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 11
   AND (SELECT COUNT(*) FROM t10 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t12 AS
(
SELECT *
  FROM t11
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 12
   AND (SELECT COUNT(*) FROM t11 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t13 AS
(
SELECT *
  FROM t12
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 13
   AND (SELECT COUNT(*) FROM t12 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t14 AS
(
SELECT *
  FROM t13
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 14
   AND (SELECT COUNT(*) FROM t13 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t15 AS
(
SELECT *
  FROM t14
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 15
   AND (SELECT COUNT(*) FROM t14 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t16 AS
(
SELECT *
  FROM t15
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 16
   AND (SELECT COUNT(*) FROM t15 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t17 AS
(
SELECT *
  FROM t16
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 17
   AND (SELECT COUNT(*) FROM t16 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t18 AS
(
SELECT *
  FROM t17
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 18
   AND (SELECT COUNT(*) FROM t17 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t19 AS
(
SELECT *
  FROM t18
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 19
   AND (SELECT COUNT(*) FROM t18 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t20 AS
(
SELECT *
  FROM t19
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 20
   AND (SELECT COUNT(*) FROM t19 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t21 AS
(
SELECT *
  FROM t20
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 21
   AND (SELECT COUNT(*) FROM t20 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t22 AS
(
SELECT *
  FROM t21
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 22
   AND (SELECT COUNT(*) FROM t21 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t23 AS
(
SELECT *
  FROM t22
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 23
   AND (SELECT COUNT(*) FROM t22 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t24 AS
(
SELECT *
  FROM t23
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 24
   AND (SELECT COUNT(*) FROM t23 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t25 AS
(
SELECT *
  FROM t24
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 25
   AND (SELECT COUNT(*) FROM t24 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t26 AS
(
SELECT *
  FROM t25
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 26
   AND (SELECT COUNT(*) FROM t25 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t27 AS
(
SELECT *
  FROM t26
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 27
   AND (SELECT COUNT(*) FROM t26 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t28 AS
(
SELECT *
  FROM t27
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 28
   AND (SELECT COUNT(*) FROM t27 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t29 AS
(
SELECT *
  FROM t28
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 29
   AND (SELECT COUNT(*) FROM t28 WHERE test_id = a.test_id AND rk <= 20) < 3
)
, t30 AS
(
SELECT *
  FROM t29
 WHERE rk <= 20
 UNION ALL
SELECT a.*
     , RANK() OVER(ORDER BY v DESC) rk
  FROM t0 a
 WHERE dr = 30
   AND (SELECT COUNT(*) FROM t29 WHERE test_id = a.test_id AND rk <= 20) < 3
)
SELECT *
  FROM t30
 WHERE rk <= 20
;

 감사합니다 마농님~

저는 한쿼리로 뽑아야되서..ㅋㅋ;;

앞의 결과가 나와야지만 뒤의 결과를 처리할 수 있는
절차적인 처리가 필요한 상황입니다.
구조적, 집합적 언어인 SQL 로는 절차적 처리가 불가능 합니다.
하루 하루 루프돌려가며 처리해야만 하는 상황입니다.

 마농님 정말 감사드립니다!!