리트코드 : 183. Customers Who Never Order

리트코드 : 183. Customers Who Never Order

• https://leetcode.com/problems/customers-who-never-order/description/
  

---

문제

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID and name of a customer.


Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
customerId is a foreign key (reference columns) of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.


Write a solution to find all customers who never order anything.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

문제 풀이

MySQL

# SOLUTION 1
SELECT NAME AS CUSTOMERS
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.ID = O.CUSTOMERID
WHERE O.CUSTOMERID IS NULL

# SOLUTION 2
SELECT NAME AS CUSTOMERS
FROM CUSTOMERS
WHERE ID NOT IN (
    SELECT CUSTOMERID
    FROM ORDERS)

• 1번처럼 테이블을 LEFT JOIN을 시켜서 NULL값인 경우를 뽑는 경우
• 2번처럼 서브쿼리를 사용해서 유무를 확인하는 경우
• 1번 케이스가 필터링 할 요소가 적어서 더 빠르다.

Pandas

# SOLUTION 1
import pandas as pd

def find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    join = pd.merge(customers, orders, left_on='id', right_on='customerId', how='left')
    return join[join['customerId'].isnull()][['name']].rename(columns={'name':'Customers'})

# SOLUTION 2
def find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    answer = customers[~customers['id'].isin(orders['customerId'])][['name']]
    answer.rename(columns={'name': 'Customers'}, inplace=True)
    return answer

• 1번 풀이처럼 LEFT JOIN 해주기.
  • NULL값을 찾을 때 isnull()을 사용하기
• 2번 풀이처럼 isin을 이용해서 주어진 요소가 isin의 파라미터 안에 있는지 확인하기.
  • ~을 통해서 불리언 인덱스들을 반전시킨다.

코멘트

• 풀이가 같은데도 동작시간이 좀 차이가 나는듯?