[3일차] python&sql

Fix Names In a Table

다음 테이블에서 이름을 첫 번째 문자만 대문자이고 나머지 문자는 소문자가 되도록 수정하시오.    (단, 최종 결과는 user_id 순으로 오름차순 정렬)

 

Users

 

Python

 

Users['name'] = Users['name'].str.capitalize()
print(Users.sort_values('user_id'))

 

• Users['name'].str.capitalize(): str.capitalize()는 문자열의 첫 글자를 대문자로 변환하고 나머지를 소문자로 변환하는 함수로, 각 사용자의 이름에서 첫 글자만 대문자로 변경
  
  

SQL

 

# MySQL
select user_id, concat(upper(substr(name,1)), substr(name,2)) as name
from Users
order by user_id

#SQLite
select user_id, upper(substr(name,1,1)) || substr(name,2) as name
from Users
order by user_id

 

• concat(): 두 개 이상의 문자열을 합치는 함수
• upper(substr(name,1)): 'name'의 첫 문자를 추출한 후, 이 문자를 대문자로 변환
• substr(name,2): 'name'의 두 번째 문자부터 끝까지의 문자열을 추출
• ||: SQLite에서 두 개의 문자열을 연결할 때 사용하는 연산자

---

Find Users With Valid E-Mails

다음 테이블에서 유효한 전자 메일을 가진 사용자를 찾으시오. (단, 이름은 숫자, 밑줄(_), 마침표(.), 대시(-)를 포함할 수 있으며, 제일 처음은 문자로 시작해야 하고, 도메인은 @leetcode.com을 사용)

 

Users

 

Python

 

Users[Users['mail'].str.match(r'^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode\.com$')].reset_index(drop=True)

 

• Users['mail'].str.match(): DataFrame의 'mail' 열의 각 값에 대해 정규식 패턴과 일치하는지 확인
• python에서 정규식 문자열 앞에 'r'을 붙이는 것은 문자열이 raw string임을 나타낸다. raw string에서는 백슬래시 '\'를 일반 문자로 취급하므로, '\'를 2번 사용하지 않아도 된다.
• 일반적으로 문자열에서 백슬래시를 표현하려면 escape 처리를 해야하기 때문에, 문자열 내에서 리터럴 백슬래시를 표현하려면 '\\'과 같이 작성해야 한다.
  
  

SQL

 

select user_id, name, mail
from Users
where mail regexp '^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode\.com$'

 

• mail regexp '정규식': SQL의 regexp 연산자를 사용하여 'mail' 컬럼의 값이 주어진 정규식 패턴과 일치하는지 확인
  
  

Solution을 구하기 위해 python과 sql에서 모두 정규식을 사용했다. 정규 표현식(Regular Expressions)은 문자열을 찾아내고, 추출하고, 변경하는 도구로, 메타문자 및 수량자를 사용해서 기능을 활용할 수 있다. 위의 내용을 이해하기 위해서, 먼저 메타문자와 정규식의 유형에 대해 알아보고 문제에서 사용한 정규식을 해석해보자.

 

메타문자(Meta-characters)는 특별한 의미를 갖는 문자로, 주요 메타 문자로는 . ^ $ * + ? { } [ ] \ | ( ) 등이 있다.

^ : 문자열의 시작을 나타낸다.
예제: ^Hello => "Hello World"와 일치하지만 "Say Hello"와는 일치하지 않음

$ : 문자열의 끝을 나타낸다.
예제: World$ => "Hello World"와 일치하지만 "World Tour"와는 일치하지 않음

.  : 어떤 한 문자와도 일치
예제: H.llo => "Hello", "Hxllo", "H1llo" 등과 일치

[...] : 대괄호 안에 있는 문자 중 어느 하나와 일치
예제: H[aeiou]llo => "Hallo", "Hello", "Hillo" 등과 일치

[^...] : 대괄호 안에 있는 문자를 제외한 나머지 문자 중 하나와 일치
예제: H[^aeiou]llo => "Hxllo", "Hyllo" 등과 일치

| : 두 패턴 중 하나와 일치(or)
예제: apple|banana => "apple" 또는 "banana"와 일치

() : 패턴을 그룹으로 결합
예제: (ab)+ => "ab", "abab", "ababab" 등과 일치

\(백슬래시) : 메타문자의 특별한 의미를 취소하고 해당 문자 자체를 나타냄
예제: \.com => ".com"과 일치

\d : 숫자와 일치하는 문자 클래스로, [0-9]와 동일한 의미
예제
d{3} : 세 자리의 숫자와 일치 => "123", "456"
-\d\d- : '-'로 둘러싸인 두 자리 숫자와 일치 => "-05-"

\w : 단어 문자와 일치하는 문자 클래스로, 알파벳, 숫자, 밑줄(_)을 포함한다. [a-zA-Z0-9_]와 동일한 의미를 가진다. 예제
\w+: 하나 이상의 단어 문자와 일치 =>  "apple", "banana_123"
a\wb: 'a'로 시작하고 'b'로 끝나는 세 문자와 일치 =>  "a1b", "a_b"

\s : 공백 문자와 일치하는 문자 클래스로, 스페이스, 탭, 줄 바꿈 문자 등을 포함한다.
예제
\s{2,}: 두 개 이상의 연속된 공백 문자와 일치
hello\s\w+: "hello" 다음에 공백 한 개와 단어가 오는 경우와 일치 =>   "hello world", "hello there"

 

수량자(Quantifiers)는 바로 앞의 요소가 몇 번 나타나는지를 지정하는 데 사용되며, 주요 수량자로는 * + ? {n} {n,} {n,m} 등이 있다.

 

* : 앞선 패턴이 0번 이상 반복될 때 일치
예제: Ho*la => "Hola", "Hoola", "Hooooooola" 등과 일치

+ : 앞선 패턴이 1번 이상 반복될 때 일치
예제: Ho+la => "Hoola", "Hooooooola"와 일치하지만 "Hola"와는 일치하지 않음

? : 앞선 패턴이 0번 또는 1번 나타날 때 일치
예제: Ho?la => "Hla"와 "Hola"와 일치

{n} : 'n'이 정확히 x번 반복될 때 일치
예제: x가 3이면, "AAA" (일치), "AA" (불일치), "AAAA" (불일치, 그러나 "AAA" 부분은 일치)

{n,} : 'n'이 최소 x번 이상 반복될 때 일치
예제: x가 2이면, "AA" (일치), "AAA" (일치), "A" (불일치), "AAAAA" (일치)

{n,m} : 'n'이 최소 x번, 최대 y번 반복될 때 일치
예제: x=2, y=4 이면, "AA" (일치), "AAA" (일치), "AAAA" (일치), "A" (불일치), "AAAAA" (불일치, 그러나 "AAAA" 부분은 일치)

 

위의 글을 읽다보면 의문이 생길 수 있다. or는 있는데 and는 없고, {n,}는 있는데 {,n}은 없기 때문이다. 해당 개념은 표준 정규 표현식에서 지원되지 않기 때문에, 직접 메타문자와 수량자를 조합해서 사용해야 한다.

 

• 특정 문자나 패턴이 최대 x번까지 등장할 때 일치하는 정규식({,n} 역할)
  • 예시 : x가 3이면, ^(A{0,3})[^A]*$
  • 이 정규식은 'A'가 0에서 3번까지 등장하며, 그 이후에는 'A'문자 외의 다른 문자가 등장하는 문자열과 일치
    
    
• 두 패턴 모두와 일치하는 정규식(and 역할)
  • 예시 : apple.*banana => "apple" 다음에 0개 이상의 어떤 문자가 오고, 그 다음에 "banana"가 오는 문자열과 일치
    
    

정규식에 대해 간단하게 알아봤으니, 우리가 사용한 정규식을 확인해보자.

 

^[A-Za-z][A-Za-z0-9_\.\-]*@leetcode\.com$

 

• ^ : 문자열의 시작을 나타낸다.
• [A-Za-z] : 문자열의 첫 번째 문자는 알파벳 대문자 또는 소문자여야 한다.
• [A-Za-z0-9_\.\-]* : 이어서, 알파벳 대문자, 소문자, 숫자, 밑줄(_), 점(.), 또는 하이픈(-) 중 하나가 나올 수 있다. *는 이전 패턴이 0번 이상 반복될 수 있음을 나타낸다. 즉, 이메일 주소의 '@' 전까지의 부분에서 여러 문자 및 기호가 올 수 있다.
• @: 이메일 주소의 '@' 기호이다.
• leetcode\.com: "leetcode.com" 문자열과 일치합니다. 여기서 점(.) 앞에 백슬래시()가 있는 이유는 점이 메타문자로서 어떠한 문자와도 일치하는 특성을 가지기 때문에, 따라서 \.을 사용하여 리터럴 점 문자로 인식하게 만든다.
• $: 문자열의 끝을 나타낸다.
• 즉, 문제의 조건 3가지와 일치함을 확인할 수 있다.

---

Patients With a Condition

다음 테이블에서 제1형 당뇨병 환자의 id, 이름 및 상태를 찾으시오. (단, 제1형 당뇨병 상태는 항상 DIAB1으로 시작)

 

Patients

 

Python

 

print(Patients[Patients['conditions'].str.contains('DIAB1')].reset_index(drop=True))

Patients[Patients['conditions'].str.match('.*DIAB1.*')] # regex

 

• Patients['conditions'].str.contains('DIAB1'): Data frame의 'conditions' 컬럼에서 'DIAB1' 문자열을 포함하는 모든 행을 필터링
• 위에서 확인한 정규식을 활용해도 동일한 답안 추출 가능
  
  

SQL

 

select *
from Patients
where conditions like '%DIAB1%'

 

• where conditions like '%DIAB1%' : 'conditions' 컬럼에서 'DIAB1' 문자열을 포함하는 모든 행을 필터링
• where conditions regexp '.*DIAB1.*'  : python과 동일하게 정규식을 활용할 수 있음