상쾌한 수요일 아침 SQL 다지기

[ppabam]

WITH domains AS (
SELECT 
    COALESCE(SPLIT(domain, '\\\\.')[0], 'NA') AS domain
FROM t_raw
)
SELECT 
    COUNT(DISTINCT domain) cnt, 
    COUNT(DISTINCT UPPER(domain)) ucnt 
FROM domains

• 위와 같은 상황에서 cnt, ucnt 값이 다르다면 ?
• 어떤 문제가 있는지 파악 하는 쿼리를 만들어 보세요.

[cho6019]

[vhzkclq0705]

• UPPER(domain) 시 domain이 NA, na 값을 모두 가지고 있는 것을 보아, CNT와 UCNT의 차이는 여기서 발생한다.

[stundrg]

%sql
WITH domains AS (
    SELECT 
        COALESCE(SPLIT(domain, '\\\\.')[0], 'NA') AS domain
    FROM t_raw
),
domain_grouped AS (
    SELECT 
        UPPER(domain) AS upper_domain,
        COLLECT_SET(domain) AS original_domains,
        COUNT(DISTINCT domain) AS var_count
    FROM domains
    GROUP BY UPPER(domain)
)
SELECT *
FROM domain_grouped
WHERE var_count > 1;

[heejin131]

%sql

WITH domains AS (
    SELECT 
        COALESCE(SPLIT(domain, '\\\\.')[0], 'NA') AS domain
    FROM t_raw
)
SELECT UPPER(domain) AS upper_domain,
       COUNT(DISTINCT domain) AS cnt,
       COUNT(DISTINCT UPPER(domain)) AS ucnt
FROM domains
GROUP BY UPPER(domain)
HAVING COUNT(DISTINCT domain) != COUNT(DISTINCT UPPER(domain));

[Jacob-53]

TroubleShooting

• 큰 데이터 처리 - wikimedia #110 (comment) 파티셔닝 문제 해결 하였습니다

[jiwon1118]

%sql

WITH domains AS (
    SELECT 
        COALESCE(SPLIT(domain, '\\\\.')[0], 'NA') AS domain
    FROM temp_wiki
)
SELECT 
    UPPER(domain) AS upper_domain,
    COUNT(DISTINCT domain) AS cnt,
    COUNT(DISTINCT UPPER(domain)) AS ucnt
FROM domains
GROUP BY UPPER(domain)
HAVING COUNT(DISTINCT domain) != COUNT(DISTINCT UPPER(domain));

[sgcho0907]

• except로 NA만 남기는 쿼리를 짜고 싶었는데 생각대로 되지 않았습니다 ㅠ

[ppabam]

방법 1

(SELECT 
    LOWER(domain) l_domain, 
    COUNT(DISTINCT domain) cnt 
FROM temp_domains
GROUP BY LOWER(domain)
)
EXCEPT ALL
(SELECT 
    LOWER(domain) l_domain, 
    COUNT(DISTINCT LOWER(domain)) cnt 
FROM temp_domains
GROUP BY LOWER(domain)
)

방법 2

SELECT 
    LOWER(domain) l_domain, 
    COUNT(DISTINCT domain) cnt 
FROM temp_domains
GROUP BY LOWER(domain)
HAVING cnt > 1