ORA-30076: invalid extract field for extract source
상황
프로그래머스 입양 시각 구하기(1) 풀이 중 EXTRACT로 HOUR를 추출할려고 써보는데 해당 에러 발생
SELECT EXTRACT(HOUR FROM DATETIME)
FROM ANIMAL_OUTS
원인
데이터타입이 안맞아서 나는 오류
EXTRACT 함수를 사용할때, 2가지 데이터타입을 받을 수 있는데 하나는 DATE 다른 하나는 TIMESTAMP 1)DATE(DATETIME) : YEAR, MONTH, DAY 2)TIMESTAMP : HOUR, MINUTE, SECOND 등등
※DATETIME 데이터타입 DATE + TIMESTAMP = DATETIME DATETIME 자체가 DATE 타입과 TIMESTAMP을 합쳐 놓은 데이터타입
SELECT SYSDATE,
SYSTIMESTAMP,
EXTRACT (YEAR FROM SYSDATE),
EXTRACT (MONTH FROM SYSDATE),
EXTRACT (DAY FROM SYSDATE),
EXTRACT (HOUR FROM SYSTIMESTAMP),
EXTRACT (MINUTE FROM SYSTIMESTAMP),
EXTRACT (SECOND FROM SYSTIMESTAMP)
FROM DUAL
해결
CAST()로 TIMESTAMP형으로 파싱해주면 된다
SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) HOUR
FROM ANIMAL_OUTS
GROUP BY + 서브쿼리로 이용하여, 여러 날짜(DAY)의 HOUR를 그루핑해서 조회할려다가 에러 발생
SELECT HOUR, DATETIME, COUNT(DATETIME) AS COUNT -- FM 공백제거, HH24 시간만 24시제로 표시
FROM (
SELECT TO_CHAR(DATETIME,'FMHH24') AS HOUR, DATETIME
FROM ANIMAL_OUTS
**GROUP BY DATETIME** -- ORA-00937: not a single-group group function
)
ORDER BY DATETIME
원인
GROUP BY 형식을 잘몰라서 생기는 오류 GROUP BY에 지정한 컬럼과 SELECT의 지정한 컬럼이 같아야한다. 한쪽에 컬럼 세팅이 빠져서 생기는 오류 집계함수를 제외하면 컬럼명을 양 쪽에 모두 공유해야한다
해결
위에서는 별칭인 HOUR로는 연계가 안되기 때문에 컬럼을 직접적으로 선언하던가 또는 DATETIME으로 별칭을 짜면 해결 가능
SELECT HOUR, DATETIME -- COUNT(DATETIME) AS COUNT를 지우면 ORA-00937 해결
FROM (
SELECT TO_CHAR(DATETIME,'FMHH24') AS HOUR, DATETIME
FROM ANIMAL_OUTS
GROUP BY DATETIME
)
ORDER BY DATETIME
AND TWIN1.BIRTH_YMD = TWIN2.BIRTH_YMD -- 생년월일이 같을 조건
) TWIN
WHERE EMP.EMP_NO = FAM.EMP_NO
AND -- --> ★★★ 현재 재직자만 추출 되도록 조건을 넣으세요.
AND FAM.REL_TYPE_CD= 'A27'
AND -- --> ★★★ 2012년 1월 1일 부터 2015년 12월 31일 생년월일 조건을 넣으세요.
AND EMP.EMP_NO = FAMR.EMP_NO (+)
AND FAMR.REL_TYPE_CD (+) IN ('A02','A18')
AND EMP.EMP_NO = TWIN.EMP_NO (+)
AND FAM.FAM_NM = TWIN.TWIN1 (+)
;
조건 brainstorming 재직중 : RETIRE 99/12/31 조건 자녀 생년월일 2012.01.01~2015.12.31 : BETWEEN 20120101 AND 20151231 쌍둥이의 경우 두명 모두 추출 : FAM_C SELF JOIN FAM_NM을 F1,F2 따로 조회 및 조건에 NM <>추가 쌍둥이 여부 표시 'Y' : 리터럴 작성 사내부부의 경우, 상대 배우자의 직원번호 추출 : ? 조회 목록 : EMP_NO, EMP_NM, CASE(SPOUSE_EMP_NO 사내부부일때,일반일때), CHILD_NM, CHILD_GENDER, CHILD_BIRTH_YMD, TWIN_YN (직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부)
--- 추출조건1
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;
--- 추출조건2
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
--- 추출조건1 + 추출조건2
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
-- FROM절의 AND조건들을 WHERE절로 돌려서 넣으면 뭐가 다를까?
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
WHERE F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
2) LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL을 작성해 보세요
--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용
2.문장에서 쿼리 조건 리스트화
-- 재직중, 현재 날짜 기준, 이씨성, 자녀수
3.각 조건마다 예상되는 쿼리 짜기
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수 : COUNT()
3-2) 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
힌트: substr , count(*)를 사용
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수
-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;
-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상
SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27' -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요.
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2;
3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)
힌트 group by org_cd, org_nm
-- 현재 기준 : ?
-- 부서별 직원 : GROUP BY ORG_NM
-- 부서별 직원 수 : COUNT(*)
SELECT
org_nm 조직분류,
COUNT(*) 직원수
FROM
emp_c e
JOIN org_c o USING ( org_cd )
GROUP BY
org_nm;
;
전체 쿼리
--3)20210321 기준으로 재직중인 임직원이며,사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.
--HINT : EMP_C와 FAM_REL_C를 조인해야 합니다.
-- 조회 : 직원번호
-- 재직중 : RETIRE 99/12/31 조건
-- 사내부부 : ? // 일단 EMP_NO = EMP_REL_NO 아님
-- 직원번호와 직원가족번호 =조건 해봤으나 X
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO -- 직원번호와 직원가족번호 =조건 해봤으나 X
;
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO -- 직원번호와 직원가족번호 =조건 해봤으나 X
;
SELECT *
FROM EMP_C E
JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO
AND REL_TYPE_CD IN('A02', 'A18')
;
SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;
SELECT *
FROM EMP_C
WHERE EMP_NO = 11501824;
SELECT *
FROM FAM_C
WHERE EMP_NO = 11501824;
SELECT *
FROM FAM_REL_C
WHERE EMP_NO = 11501824;
--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수
-- 조건 : 재직중+이씨성
SELECT *
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
;
-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;
-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;
--3-3) 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.(직원번호?/?성명?/?자녀명수?)
--힌트 having
-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상
-- A27타입인 자녀수만 조회
SELECT COUNT(F.REL_TYPE_CD)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27'
;
SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E
JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27' -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요.
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2
-- WHERE절에서 자녀있는 직원만 골라낸 후, HAVING으로 2명이상 조건 적용
-- 이후 GROUP BY 직원번호와 성명을 묶어서 조회
;
SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;
--3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)
--힌트 group by org_cd, org_nm
-- 현재 기준 : ?
-- 부
-- 부서별 직원 수 : COUNT(*)서별 직원 : GROUP BY ?(ORG..?)
SELECT
org_nm 조직분류,
COUNT(*) 직원수
FROM
emp_c e
JOIN org_c o USING ( org_cd )
GROUP BY
org_nm;
;
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
ROWS : 물리적인 ROW 단위로 행 집합을 지정 ( 현재행을 기준으로 몇개의 행을 포함하는지 )
RANGE : 논리적인 상대번지로 행 집합을 지정 ( 현재행을 기준으로 어떤 값의 범위를 포함하는지 )
BETWEEN~ AND 절 : 윈도우의 시작과 끝 위치를 지정UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작
업무분석을 통해 바로 정의한 속성을 기본속성(Basic Attribute), 원래 업무상 존재하지는 않지만 설계를 하면서 도출해내는 속성을 설계속성(Designed Attribute), 다른 속성으로부터 계산이나 변형이 되어 생성되는 속성을 파생속성(Derived Attribute)이라고 한다.
1.기본속성
업무로부터 추출한 모든 속성이 여기에 해당하며 엔터티에 가장 일반적이고 많은 속성을 차지한다. 코드성 데이터, 엔터티를 식별하기 위해 부여된 일련번호, 그리고 다른 속성을 계산하거나 영향을 받아 생성된 속성을 제외한 모든 속성은 기본속성이다. 주의해야 할 것은 업무로부터 분석한 속성이라도 이미 업무상 코드로 정의한 속성이 많다는 것이다. 이러한 경우도 속성의 값이 원래 속성을 나타내지 못하므로 기본속성이 되지 않는다
2.설계속성
업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성이다. 대개 코드성 속성은 원래 속성을 업무상 필요에 의해 변형하여 만든 설계속성이고 일련번호와 같은 속성은 단일(Unique)한 식별자를 부여하기 위해 모델 상에서 새로 정의하는 설계속성이다.
3.파생속성
다른 속성에 영향을 받아 발생하는 속성으로서 보통 계산된 값들이 이에 해당된다. 다른 속성에 영향을 받기 때문에 프로세스 설계 시 데이터 정합성을 유지하기 위해 유의해야 할 점이 많으며 가급적 파생속성을 적게 정의하는 것이 좋다.
