[PostGRES] NDTP-DTP 를 갈라내보자.

네이버 API 검색이 끝났다.

 

import pandas as pd
from sqlalchemy import create_engine
from glob import glob

filenames = glob('./api_crawl_1/*.csv')
dfs = []

for file in filenames:
    t = pd.read_csv(file)
    dfs.append(t)

filenames = glob('./api_crawl_2/*.csv')
for file in filenames:
    t = pd.read_csv(file)
    dfs.append(t)

df_concat = pd.concat(dfs)

engine = create_engine('postgresql+psycopg2://???:???@???:5432/???')

df_concat.to_sql('api_crawled', con = engine, chunksize = 1000)

 

crawling 결과를 수집했던 2개의 폴더에서부터 csv 파일을 읽어와 데이터베이스로 보냈다.

앞선 알고리즘을 통해서 2개의 폴더가 disjoint 한 건 확실히 알겠다.

 

이제 api_crawled 와 permit_place 의 비교를 통해서 

"아무리 해도 검색이 안되는 녀석들" 을 골라낼 차례.

 

CREATE MATERIALIZED VIEW NDTP AS --이것도 일단 materialized view로 만들자.
(
WITH cralwed AS
(SELECT pmid, p_title, n_title, similarity(p_title, n_title), 
	split_part(p_addr, ' ', 2) p_SIG_NM,
	split_part(p_addr, ' ', 3) p_RD_NM,
	split_part(n_addr, ' ', 2) n_SIG_NM,
	split_part(n_addr, ' ', 3) n_RD_NM

	FROM api_crawled

	WHERE split_part(p_addr, ' ', 2) = split_part(n_addr, ' ', 2) AND
	split_part(p_addr, ' ', 3) = split_part(n_addr, ' ', 3) --주소는 도로명까지 똑같고
	and similarity(p_title, n_title) > 0.15) --이름은 0.15 이상 유사한 녀석들
	
SELECT pp.* FROM crawl_permit_place pp
LEFT JOIN cralwed cc
ON pp.pmid = cc.pmid
WHERE cc.pmid is NULL)

 

 

그 결과,

ndtp 는 총 56,464개가 나왔다.

총 인허가 데이터가 208,664 개이므로, DTP 는 NDTP랑 확실히 disjoint 한 것 같다.

 

그리고 또 하나의 이슈 사항...

내가 지난번 인허가 데이터를 받은 시점은 2023년 중순...

그리고 지금 NAVER 데이터로 다시 점검한 건 2024년 11월...그러니까

"2023년 중순 이후에 폐업한 애들은 NAVER에 안 나올 수도 있음!!" 이라는 거다...

 

최신 인허가 데이터를 기준으로 혹시 그런 녀석들이 있는지 체크해보자...

SELECT n.title, per.영업상태명, per.폐업일자 FROM ndtp n
JOIN permit_place_2024 per --2024년11월7일 (NAVER Crawling 진행) 기준 인허가 상태
ON n.title = per.사업장명 AND n.addr = per.도로명전체주소
WHERE 영업상태명 LIKE '폐업';

 

 

 

아니나다를까 8,136 개의 음식점들은 현재 폐업상태다. 그러니까,

"이전에 NAVER에 등록 되었었는지 아닌지 알 수 없는, 지금은 문 닫은 음식점" 이 8,316개나 된다는 거...

Third Place의 정의를 따지자면 "영업중인" 으로 감안하였으니 굳이 뺄 이유는 없긴 한데,

DTP, NDTP의 정의를 따지자면 "플랫폼에서 검색 가능한"으로 비교하면 모호하다는 게 판단이다.

 

무턱대고 NDTP에 넣기엔, NDTP가 총 56,464 개니까, 거의 13%정도 되는 수준이니

1. 모두 NDTP에 포함시킨다:

"만약 얘들이 사실 전부 검색 가능했었던" 최악의 상황이라면, 분석 결과에 영향을 미칠 수 있는 수준.

 

2. 모두 DTP에 포함시킨다:

이건 아예 성립이 안된다. 검색 가능했음을 검증할 수 있는 방법이 없기에.

 

3. 제외시킨다.

그런데 이걸 또 2024년 11월 기준으로 NDTP를 체크해보고 제외시키는 건 맞는 건가.

앞의 두 상황이랑 비교하면 빼는 게 맞지.

 

CREATE Materialized VIEW ndtp_2024 AS ( --자꾸 늘어나는 게 맘에 안들지만 어떡하겠어
SELECT ond.* FROM ndtp ond
LEFT JOIN 
	(SELECT n.pmid, n.title, per.영업상태명, per.폐업일자 FROM ndtp n
	JOIN permit_place_2024 per
	ON n.title = per.사업장명 AND n.addr = per.도로명전체주소
	WHERE 영업상태명 LIKE '폐업') n_2024
ON ond.pmid = n_2024.pmid
WHERE n_2024.영업상태명 IS NULL);

 

이렇게 총 48,431개의 NDTP...를 추려냈다...

 

그리고 다시 DTP를 추려내보자...

CREATE materialized view dtp_2024 AS
(SELECT dtp.* FROM 
	(SELECT pp.* FROM permit_place pp
	LEFT JOIN ndtp_2024 nd
	ON pp.pmid = nd.pmid
	WHERE nd.title is null) dtp --인허가에서 NDTP 빼고
LEFT JOIN 
	(SELECT n.pmid, n.title, per.영업상태명, per.폐업일자 FROM ndtp n
	JOIN permit_place_2024 per
	ON n.title = per.사업장명 AND n.addr = per.도로명전체주소
	WHERE 영업상태명 LIKE '폐업') n_2024 --그리고 폐업해서 사라진 녀석들 빼고
ON dtp.pmid = n_2024.pmid
WHERE n_2024.영업상태명 IS NULL);

총 152,201개의 dtp를...얻었다...

 

---

 

<DTP에서 ADTP를 골라내기 위한 코드.>

 

WITH dtp_adtp AS
	(SELECT p.* FROM permit_place p
	LEFT JOIN ndtp n
	ON p.pmid = n.pmid
	WHERE n.pmid is null)
SELECT da.title, adtp.title FROM dtp_adtp da
JOIN (SELECT * FROM naver_place_raw WHERE book LIKE 'htt%') adtp
ON da.sig_nm = adtp.sig_nm AND da.rd_nm = adtp.rd_nm
AND da.bd_bon = adtp.bd_bon AND da.bd_bu = adtp.bd_bu
AND similarity(da.title, adtp.title) > 0.15;

 

SELECT d.pmid, d.title, d.addr, ad.* FROM dtp_2024 d
JOIN (SELECT * FROM naver_place_raw WHERE book LIKE 'htt%') ad
ON d.title = ad.title AND d.sig_nm = ad.sig_nm AND
d.rd_nm = ad.rd_nm AND d.bd_bon = ad.bd_bon AND d.bd_bu = ad.bd_bu;