database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41574 게시물 읽기
No. 41574
쿼리 질문좀 드려도 될까요?
작성자
yub
작성일
2018-05-28 10:08
조회수
4,920

1~30일 중 랭킹20등 안에 3번이 되는 아이디는 다음일자부터 랭킹에 안올라가게 쿼리를 짜려고 합니다.

그래서 1~30일 전체날짜 데이터를 일단 불러온후 랭킹20위까지의 쿼리는 만들었지만,  마지막3번 중복으로 들어가는 아이디 처리를 못하겠습니다.

1일 id: yub

5일 id: yub

7일 id : yub

15일 id:yub

예를들어서 yub이라는 id가 랭킹에 4일동안 20등안에 들어서 4개가 출력이 되는데 처음 7일까지 3번이 랭킹에 등록이 되어있고 15일부터는 출력이 안되게

하려고합니다. 도와주시면 감사하겠습니다.

select test_id from (select row_number() over  (order by crt_dt desc) as rn,test_id,crt_dt from test_table
where test_date between '20180501' and '20180530'
group by crt_dt,test_id
) aa
where aa.rn <=20

 

이 글에 대한 댓글이 총 6건 있습니다.

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

마농(manon94)님이 2018-05-28 11:36에 작성한 댓글입니다.

안녕하세요 마농님.

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

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

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

yub님이 2018-05-28 11:46에 작성한 댓글입니다. Edit

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
;

마농(manon94)님이 2018-05-28 15:24에 작성한 댓글입니다.

 감사합니다 마농님~

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

yub님이 2018-05-28 15:50에 작성한 댓글입니다. Edit

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

마농(manon94)님이 2018-05-28 15:56에 작성한 댓글입니다.
이 댓글은 2018-05-28 15:57에 마지막으로 수정되었습니다.

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

yub님이 2018-05-29 11:01에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41577안녕하세요...질문이있습니다~~ [1]
윤지환
2018-06-11
4436
41576테이블 스페이스에 데이터파일을 추가하려고 합니다. [1]
선이
2018-05-29
4759
41575sql 문제 ..도와주세요...ㅜㅜ [15]
도와주세요
2018-05-28
5070
41574쿼리 질문좀 드려도 될까요? [6]
yub
2018-05-28
4920
41573update set 컴럼 조건 주기 가능한가요? [1]
안정수
2018-05-26
4877
41571디비링크 가능여부 문의합니다. [2]
김기훈
2018-05-24
4784
41570view 속도 개선 문제 답변 부탁드립니다. [3]
컴맹
2018-05-24
6495
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다