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 | 31 |
Tags
- maVen
- tomcat
- 공정능력
- react
- Spring
- MSSQL
- mybatis
- SQL
- GIT
- Java
- mapreduce
- window
- Android
- IntelliJ
- Sqoop
- Kotlin
- vaadin
- JavaScript
- 정렬
- SPC
- Express
- hadoop
- 보조정렬
- R
- es6
- Eclipse
- xPlatform
- table
- Python
- NPM
Archives
- Today
- Total
DBILITY
독거 가능성 100% 노후에 라면값이라도 하게 센스를 발휘합시다!😅
Please click on the ad so that I can pay for ramen in my old age!
点击一下广告,让老后吃个泡面钱吧!
老後にラーメン代だけでもするように広告を一回クリックしてください。
데이터 분포에 따라 번호 배정하기 본문
반응형
현업 중 분석처리 서버를 지정해 줘야 하는 경우를 위해 산수를 이용해 봤습니다.
정답이라 말할 순 없습니다.
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