oil_price 데이터도 없었고, 서브쿼리에 대한 이해가 낮다고 생각되어 문제를 추가로 찾아봤다.
파이썬으로 테이블 자동 생성하는 파일을 만들어 봤다. 조금 시간이 걸렸지만 작동이 되니 좋았다.
필요한 분 있을 수 있으니 다음주 쯤 배포예정
예제 테이블 생성 및 데이터 입력
# SQL 쿼리 리스트
sql_commands = [
"CREATE table paintings ( id int, name varchar(32), artist_id int, listed_price float)",
"CREATE table artists ( id int, first_name varchar(16), last_name varchar(16))",
"CREATE table collectors ( id int, first_name varchar(16), last_name varchar(16))",
"CREATE table sales ( id int, date date, painting_id int, artist_id int, collector_id int, sales_price float)"
]
# 데이터베이스 커서 생성
cur = conn.cursor()
# 각 SQL 쿼리를 순차적으로 실행
for sql in sql_commands:
cur.execute(sql)# 데이터베이스 커서 생성
cur = conn.cursor(buffered=True)
name = ['paintings', 'artists','collectors', 'sales']
csv_name = ['paintings.csv', 'artists.csv','collectors.csv', 'sales.csv']
col = [4,3,3,6]
# 테이블 이름, CSV 파일 이름, 컬럼 수를 동시에 순회
for table_name, csv_file, num_cols in zip(name, csv_name, col):
# 데이터 불러오기
df = pd.read_csv(csv_file)
print(df)
print(table_name, csv_file, num_cols)
# SQL 쿼리 생성
sql = 'INSERT INTO {} VALUES ({})'.format(table_name, ', '.join(['%s'] * num_cols))
print(sql)
# 각 행을 데이터베이스에 삽입
for _, row in df.iterrows():
print(sql)
cur.execute(sql, tuple(row))
conn.commit()_, 은 뭐니?
for , row in df.iterrows(): 이 구문에서 는 'throwaway' 변수를 의미합니다.
df.iterrows()는 인덱스와 행을 튜플로 반환하는데, 이 때 인덱스는 로 받아서 사용하지 않겠다는 의미입니다. 즉, 는 반복문에서 사용되지 않고 무시되는 값을 받는 변수입니다.
파이썬에서는 일반적으로 _를 사용하여 필요하지 않거나 사용되지 않는 값을 처리합니다. 이렇게 하면 코드의 가독성을 높이고, 실수로 사용되지 않아야 할 값을 사용하는 것을 방지할 수 있습니다.
Examples 1 - Scalar Subquery
We want to list paintings that are priced higher than the average. Basically, we want to get painting names along with the listed prices, but only for the ones that cost more than average. That means that we first need to find this average price; here’s where the scalar subquery comes into play:
Q. 평균보다 높은 가격의 그림 리스트
select name, listed_price from paintings where listed_price > (select avg(listed_price) from paintings); +---------------+--------------+
| name | listed_price |
+---------------+--------------+
| Pretty woman | 2800 |
| Handsome man | 2300 |
| Cool painting | 5000 |
+---------------+--------------+
select avg(listed_price) from paintings;
+-------------------+
| avg(listed_price) |
+-------------------+
| 1587.5 |
+-------------------+
Examples 2 – Multirow Subquery
Now let’s look into subqueries that return one column with multiple rows. These subqueries are often included in the WHERE clause to filter the results of the main query.
Suppose we want to list all collectors who purchased paintings from our gallery. We can get the necessary output using a multirow subquery. Specifically, we can use an inner query to list all collectors’ IDs present in the sales table – these would be IDs corresponding to collectors who made at least one purchase with our gallery. Then, in the outer query, we request the first name and last name of all collectors whose ID is in the output of the inner query. Here’s the code:
Q. 갤러리로부터 구매한 모든 콜렉터 리스트
a.
select id, first_name f, last_name l from collectors c where c.id in (select collector_id from sales);+------+-----------+-----------+
| id | f | l |
+------+-----------+-----------+
| 102 | Laura | Fisher |
| 103 | Christina | Buffet |
| 104 | Steve | Stevenson |
+------+-----------+-----------+
sales 내역에 collector_id = 101은 없다.
subquery를 공부하면서 inner join으로도 뽑을 수 있는 문제들이 많다고 생각했는데, 이번 문제 답에서는 두가지 방법을 모두 보여주었다.
Note: I have also used the DISTINCT keyword here to remove duplicates from the output
select distinct c.id, c.first_name, c.last_name from collectors c
-> INNER JOIN sales
-> ON c.id = sales.collector_id;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 104 | Steve | Stevenson |
| 102 | Laura | Fisher |
| 103 | Christina | Buffet |
+------+------------+-----------+
Example 3 – Multirow Subquery with Multiple Columns
When a subquery returns a table with multiple rows and multiple columns, that subquery is usually found in the FROM or JOIN clause. This allows you to get a table with data that was not readily available in the database (e.g. grouped data) and then join this table with another one from your database, if necessary.
Let’s say that we want to see the total amount of sales for each artist who has sold at least one painting in our gallery.
We may start with a subquery that draws on the sales table and calculates the total amount of sales for each artist ID. Then, in the outer query, we combine this information with the artists’ first names and last names to get the required output:
Q. 적어도 한개 이상의 그림이 팔렸던 아티스트들의 총 매출
이건 INLINE VIEW로 만든 내정답
select a.id, a.first_name, a.last_name, s.t_sales
-> from artists a,
-> (select artist_id, sum(sales_price) t_sales
-> from sales
-> group by artist_id) as s
-> where a.id = s.artist_id;+------+------------+-----------+---------+
| id | first_name | last_name | t_sales |
+------+------------+-----------+---------+
| 1 | Thomas | Black | 300 |
| 2 | Kate | Smith | 4800 |
| 3 | Natali | Wein | 4250 |
+------+------------+-----------+---------+
이건 홈페이지 정답
SELECT a.id, a.first_name, a.last_name, s.t_sales -> FROM artists a -> JOIN ( -> SELECT artist_id, SUM(sales_price) AS t_sales -> FROM sales -> GROUP BY artist_id -> ) s ON a.id = s.artist_id;
+------+------------+-----------+---------+
| id | first_name | last_name | t_sales |
+------+------------+-----------+---------+
| 1 | Thomas | Black | 300 |
| 2 | Kate | Smith | 4800 |
| 3 | Natali | Wein | 4250 |
+------+------------+-----------+---------+
CHAT GPT said JOIN 서브쿼리가 좀 더 현대적이고 가독성이 높다고 한다. 앞으로 JOIN 으로 시도해보아야겠다.
Example 4 – Correlated Subquery
The following example will demonstrate how subqueries:
Can be used in the SELECT clause, and
Can be correlated (i.e. the main or outer query relies on information obtained from the inner query).
For each collector, we want to calculate the number of paintings purchased through our gallery. To answer this question, we can use a subquery that counts the number of paintings purchased by each collector. Here’s the entire query:
Q. 각각의 콜렉터가 갤러리로부터 몇개의 작품을 구매했는지 계산
select first_name, last_name,
(select count(*) as paintings from sales where collectors.id = sales.collector_id) purchase_num
from collectors;값을 여러개 반환
Example 5 – Correlated Subquery
This time, we want to show the first names and the last names of the artists who had zero sales with our gallery. Let’s try to accomplish this task using a correlated subquery in the WHERE clause:
Q. 한개도 못 판 아티스트의 이름을 보여라
select a.first_name, a.last_name from artists a where not a.id = any (select artist_id from sales);+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Francesco | Benelli |
+------------+-----------+
기타
Q. 서브쿼리 중 SINGLE ROW, MULTI ROW, MULTI COLUMN 어떤 것이 더 자주 쓰이니?
'SINGLE ROW' IS USED MORE OFTEN
CHAT GPT said
SQL 서브쿼리 사용 빈도에 대한 정확한 통계는 없지만, 일반적으로 싱글 로우(single row), 멀티 로우(multi row), 멀티 컬럼(multi column) 서브쿼리의 사용 빈도는 쿼리의 복잡성, 데이터베이스의 구조, 그리고 특정 작업을 수행하는 데 필요한 요구 사항에 따라 다를 수 있습니다.
싱글 로우(single row) 서브쿼리: 이 유형의 서브쿼리는 하나의 로우만 반환합니다. 이 경우는 비교 연산자 (=, <>, >, <, >=, <=)와 함께 사용되며, 가장 간단한 형태의 서브쿼리이므로 매우 자주 사용됩니다.
멀티 로우(multi row) 서브쿼리: 이 유형의 서브쿼리는 여러 로우를 반환합니다. 이 경우는 IN, ANY, ALL 등의 연산자와 함께 사용됩니다. 이 유형의 서브쿼리는 쿼리가 조금 더 복잡해질 때 주로 사용됩니다.
멀티 컬럼(multi column) 서브쿼리: 이 유형의 서브쿼리는 여러 컬럼을 반환합니다. 이 경우는 비교 연산자와 함께 사용되며, 반환된 각 컬럼은 주 쿼리에서 비교되는 각 컬럼과 일치해야 합니다. 이 유형의 서브쿼리는 쿼리가 매우 복잡하거나, 여러 열을 동시에 비교해야 할 때 사용됩니다.
위에서 설명한 것처럼, 각 유형의 서브쿼리는 특정 상황과 요구 사항에 따라 선택되므로, 그 사용 빈도는 다양할 수 있습니다. 그러나 일반적으로 싱글 로우 서브쿼리가 가장 간단하고 직관적이므로, 가장 자주 사용되는 경향이 있을 수 있습니다. 이에 반해, 멀티 로우와 멀티 컬럼 서브쿼리는 보다 복잡한 상황에서 사용되므로, 그 사용 빈도는 상대적으로 낮을 수 있습니다.
