데이터베이스 질문과 답변

퍼센트 구하는 쿼리문 질문입니다. 0 1 1,023

by 한결 [SQL Query] [2016.01.25 10:28:12]

WITH T AS (
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'X' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'X' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'X' AS OX_2 FROM DUAL
)
SELECT OX_1, OX_2 FROM T 

 

1 O O
2 X O
3 O X
4 O O
5 X O
6 O X
7 O O
8 O O
9 X X
10 ? ?

 

10번로우 ?에 0나 X가 나올 확률을 구하고 싶은데,

어떤식으로 쿼리를 해야될지요,,,??

by 아발란체 [2016.01.25 11:09:44] 
--UNION ALL로 별도 집계 이용
WITH T AS (
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'X' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'X' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'O' AS OX_1, 'O' AS OX_2 FROM DUAL UNION ALL
     SELECT 'X' AS OX_1, 'X' AS OX_2 FROM DUAL
)
SELECT
  OX_1, OX_2
FROM
  T 
UNION ALL
SELECT
  ROUND(AVG(DECODE(OX_1, 'O', 1, 0)) * 100, 2)||'%',
  ROUND(AVG(DECODE(OX_2, 'O', 1, 0)) * 100, 2)||'%'
FROM
  T

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입