Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 |
Tags
- plugin
- Android
- Express
- SSL
- Java
- JavaScript
- xPlatform
- react
- mapreduce
- MSSQL
- SPC
- 공정능력
- R
- hadoop
- es6
- Sqoop
- vaadin
- window
- 보조정렬
- Python
- GIT
- mybatis
- table
- Kotlin
- Spring
- NPM
- tomcat
- Eclipse
- IntelliJ
- SQL
Archives
- Today
- Total
DBILITY
데이터 분포에 따라 번호 배정하기 본문
반응형
현업 중 분석처리 서버를 지정해 줘야 하는 경우를 위해 산수를 이용해 봤습니다.
정답이라 말할 순 없습니다.
db call을 줄이는 방향과 시료수가 극단적으로 치우침이 발생한 경우등도 고려해야겠지만,그건 능력밖~그런다고 누가 아나? 정신승리뿐~
( 앞쪽 번호부터 큐형태로 채우는 경우는 큐가 비었다는 걸 알수 있는 subquery가 필요하겠네요,각 큐의 크기는?스케줄링을 통해 업데이트하라는데... )
WITH T1 ( SEQ,W_OBS ) AS
(
SELECT 1,25 FROM DUAL
UNION ALL
SELECT 2,28 FROM DUAL
UNION ALL
SELECT 3,30 FROM DUAL
UNION ALL
SELECT 4,40 FROM DUAL
UNION ALL
SELECT 5,100 FROM DUAL
UNION ALL
SELECT 6,55 FROM DUAL
UNION ALL
SELECT 7,83 FROM DUAL
UNION ALL
SELECT 8,75 FROM DUAL
UNION ALL
SELECT 9,175 FROM DUAL
UNION ALL
SELECT 10,575 FROM DUAL
UNION ALL
SELECT 11,10240 FROM DUAL
)
SELECT
SEQ
, W_OBS
, SUM(W_OBS) OVER(ORDER BY W_OBS ASC) AS W_SUM
, ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC),3)*100 AS W_CUM
, DECODE(FLOOR(ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC)/0.2)),0,1,FLOOR(ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC)/0.2))) SERVERSEQ
, CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/5)) AS SERVERSEQ1
, CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/DECODE(SIGN(5-COUNT(*) OVER()),-1,5,COUNT(*) OVER()))) AS SERVERSEQ2
, NTILE(5) OVER(ORDER BY W_OBS,SEQ) AS SERVERSEQ3
, CASE WHEN (MAX(W_OBS) OVER()-MIN(W_OBS) OVER() ) > (STDDEV_POP(W_OBS) OVER()+AVG(W_OBS) OVER()) THEN
NTILE(5) OVER(ORDER BY W_OBS,SEQ)
ELSE
CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/DECODE(SIGN(5-COUNT(*) OVER()),-1,5,COUNT(*) OVER())))
END AS SERVERSEQ4
, TO_CHAR( ( 3*(AVG(W_OBS) OVER()-MEDIAN(W_OBS) OVER()) ) /STDDEV(W_OBS) OVER(), '9,999,999,990.990') AS K1
, TO_CHAR( VAR_POP(W_OBS) OVER() ,'9,999,999,990.990') AS VAR
, TO_CHAR( STDDEV_POP(W_OBS) OVER() ,'9,999,999,990.990') AS STD_DEV
, TO_CHAR( ((MAX(W_OBS) OVER()+MIN(W_OBS) OVER())/2 )- (AVG(W_OBS) OVER()/((MAX(W_OBS) OVER()-MIN(W_OBS) OVER())/2)) ,'9,999,999,999.990') AS K2
, MAX(W_OBS) OVER()-MIN(W_OBS) OVER() AS "RANGE"
, TO_CHAR( AVG(W_OBS) OVER() ,'9,999,999,990.990') AS "AVG"
, TO_CHAR( (STDDEV_POP(W_OBS) OVER()+AVG(W_OBS) OVER()) ,'9,999,999,990.990') R1
--, TO_CHAR( 1/ ( STDDEV_POP(W_OBS) OVER()*SQRT(2*ACOS(-1)) ) ,'9,999,999,990.990')
, TO_CHAR( 1/ ( STDDEV_POP(W_OBS) OVER()*SQRT(2*ACOS(-1)) )*EXP( -POWER(W_OBS-AVG(W_OBS) OVER(),2) /(2*POWER(STDDEV_POP(W_OBS) OVER(),2) )) ,'990.9999999990') AS STD_RGL
FROM T1;
반응형
'database > sql' 카테고리의 다른 글
연산결과인 그룹핑 컬럼의 분모가 0이 되는 경우 data conversion error 대처방안 (0) | 2017.07.19 |
---|---|
oracle 입찰시 기준율을 적용 업체별 순위 표시 (0) | 2016.10.13 |
oracle unpivot column to row (열을 행으로) 변환하기 (0) | 2016.10.12 |
oracle 점이력 선분이력으로 만들기 (0) | 2016.10.10 |
현재시간 1초 전 구하기 (0) | 2016.10.10 |
Comments