닥터노아바이오텍 인턴쉽 - 4

 새로 업데이트된 질병 데이터의 수 추가하기 

CREATE TABLE `2020_NoteP`.`GEODATA_List_2`
SELECT `GEODATA_List`.`Category`, 
`GEODATA_List`.`DescriptorUI`, 
`GEODATA_List`.`MeSH_Disease_Term`, 
`GEODATA_List`.`Disease_Name`, 
`GEODATA_List`.`Dataset_Possess`, 
`GEODATA_List`.`Dataset_Possess_P`, 
`GEODATA_List`.`Dataset_Possess_New`,
IFNULL(`MeSH_Count`, 0) AS Dataset_Possess_Update,
`GEODATA_List`.`TreeNumbers`
FROM `GEODATA_List`
LEFT JOIN `Disease_Count` 
ON `GEODATA_List`.`MeSH_Disease_Term` = `Disease_Count`.`MeSH_Disease_Term`
ORDER BY `GEODATA_List`.`Category`;

새로 업데이트된 질병 데이터의 수를 세어 업데이트한다.

새로 업데이트가 되지 않은 질병도 있으므로 NULL 값으로 나타난 값을 0으로 바꿔준다. 

 

GEODATA_List 테이블에 있는 MeSH_Disease_Term 과

Disease_Count 테이블에 있는 MeSH_Disease_Term 에 맞춰서 (A와 B의 교집합을 MeSH_Disease_Term으로 한다)

 

GEODATA_List 테이블을 기준으로 새로 업데이트된 질병 데이터 수(MeSH_Count)를 추가한 테이블을 만든다.

 

1. 새로운 테이블 생성하기

CREATE TABLE `2020_NoteP`.`GEODATA_List_2`

 

2. 어떠한 순서로 테이블을 만들것인가?

SELECT `GEODATA_List`.`Category`, 
`GEODATA_List`.`DescriptorUI`, 
`GEODATA_List`.`MeSH_Disease_Term`, 
`GEODATA_List`.`Disease_Name`, 
`GEODATA_List`.`Dataset_Possess`, 
`GEODATA_List`.`Dataset_Possess_P`, 
`GEODATA_List`.`Dataset_Possess_New`,
IFNULL(`MeSH_Count`, 0) AS Dataset_Possess_Update,
`GEODATA_List`.`TreeNumbers`
FROM `GEODATA_List`

 

3. 어떤 컬럼을 넣을까?

FROM `GEODATA_List`
LEFT JOIN `Disease_Count` 
ON `GEODATA_List`.`MeSH_Disease_Term` = `Disease_Count`.`MeSH_Disease_Term`

GEODATA_List 에다가 

Disease_Count 과 겹치는 컬럼을 넣는다.

 

어떤 컬럼을 넣을까?

두 테이블 사이에 겹치는 MeSH_Disease_Term 이라는 컬럼을 넣는다.

 

4. 나열 순서는?

ORDER BY `GEODATA_List`.`Category`;

 

---

 새로 업데이트된 질병 데이터의 수 추가하기 - 2 

SELECT l.*,IFNULL(r.`NewDataCnt`, 0) AS `NewDataCnt`
FROM `2020_NoteP_IS`.`2020_GEODataList` AS l
LEFT JOIN 
(SELECT `Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,COUNT(*) AS NewDataCnt 
FROM `2020_NoteP`.`ALL_NOTE_P_2020`
GROUP BY `MeSH_Descriptor`) AS r
ON l.`DescriptorUI` = r.`MeSH_Descriptor`;

SELECT의 뜻이 "난 이걸 보고싶어" 이므로 가장 나중에 실행된다.

따라서 밑에서 AS 1이라고 명명해줘도 실행이 가능하다.

 

1.

SELECT l.*,IFNULL(r.`NewDataCnt`, 0) AS `NewDataCnt`

1의 전체와 `NewDataCnt`라는 컬럼을 하나 만들어서 보자.

그리고 `NewDataCnt` 컬럼은 r이라는 테이블의 `NewDataCnt` 만 보는데, 여기서 NULL 값이 있으면 0으로 해서 본다.

 

2.

FROM `2020_NoteP_IS`.`2020_GEODataList` AS l

2020_GEODataList를 1로 명명한다.

 

3.

FROM `2020_NoteP_IS`.`2020_GEODataList` AS l
LEFT JOIN 
(SELECT `Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,COUNT(*) AS NewDataCnt 
FROM `2020_NoteP`.`ALL_NOTE_P_2020`
GROUP BY `MeSH_Descriptor`) AS r

 

 

SELECT : `Disease`, `MeSH_Disease_Term`, `MeSH_Descriptor` 컬럼을 뽑고, 숫자를 세는 'NewDataCnt`라는 컬럼도 만들어준다.

FROM : ALL_NOTE_P_2020 테이블에서 

GROUP BY : 수를 세는 컬럼은 `MeSH_Descriptor` 를 기준으로 센다.

AS : 그리고 이렇게 SELECT한 테이블을 r 이라고 명명한다.

 

LEFT JOIN : r 을 LEFT JOIN 한다. 어디로?

FROM : 2020_GEODataList 인 1로

 

4.

ON l.`DescriptorUI` = r.`MeSH_Descriptor`;

ON : 교집합의 기준은 1 의 `DescriptorUI` 와 r 의 `MeSH_Descriptor` 이다.

 

---

쿼리를 하다 보니 COUNT() 함수를 사용하여 개수를 가져와야 하는 경우가 있습니다. 그런데 값이 없는 NULL 도 COUNT() 함수 실행 결과에 포함 되어 있는지 궁금해 졌습니다. LEFT JOIN을 하게 되면 NULL인 값들이 생기는데 그것은 제외하고 몇 개가 있는지 알아야 하는 상황이 발생했거든요. 그래서 여기저기 찾아 봤더니 아래와 같이 포함 되거나 포함되지 않거나 하더군요.

 

1. 포함되는 경우 : COUNT(*)
2. 포함되지 않는 경우: COUNT(ColumnName)

COUNT(컬럼명)을 사용하면 NULL 값은 제외하고 COUNT 합니다. COUNT(*)를 사용하면 NULL도 포함하여 전부 COUNT 합니다.

하지만, 빈 문자열은 COUNT() 결과에 포함됩니다. 빈 문자열을 COUNT() 결과에서 빼려면 빈 문자열을 NULL로 바꾸고 COUNT()를 하면 됩니다.

mysql : COUNT( IF (ColumnName=”, NULL, ColumnName) )
mssql : COUNT( CASE WHEN ColumnName=” THEN NULL ELSE ColumnName END )

TRIM() 함수를 사용하면 스페이스, 탭, 줄바꿈 등의 의미 없는 문자만 있는 것도 COUNT 되지 않게 됩니다.

COUNT( DISTINCT( ColumnName ) )으로 하면 중복되지 않고 NULL은 제외되는 값들만 COUNT 하게 됩니다.

---

UPDATE `2020_NoteP_IS`.`2020_GEODataList` AS a
SET `NewCnt` = 
(SELECT COUNT(*) AS NewDataCnt 
FROM `2020_NoteP`.`ALL_NOTE_P_2020`
WHERE `MeSH_Descriptor` = a.`DescriptorUI`
GROUP BY `MeSH_Descriptor`
);