SQL 공부 (leetcode 7편)

군대 활동/공부

SQL 공부 (leetcode 7편)

맹글봄 2022. 10. 22. 23:32

오늘은 3가지 공부 중 마지막 SQL에 대해서 문장을 공부하고 추후 개인 프로젝트로 SQL을 이용하여 데이터를 추출하고자 하는 데이터 분석가에 대해 준비하려고 한다.

그러므로 추출하기 위해 효율적으로 SQL문을 짜기 위해 오늘도 Union,Funtion 기능 위주로 문제를 풀 예정이다.

1148. Article Views I

Table: Views

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date. 
Note that equal author_id and viewer_id indicate the same person.

Write an SQL query to find all the authors that viewed at least one of their own articles.

Return the result table sorted by id in ascending order.

The query result format is in the following example.

Example 1:

Input: 
Views table:
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
Output: 
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

이 문제는 소유자가 자신의 기사를 하나라도 본 값을 출력하는 문제이다.

이젠 이런 간단한 문제는 쉽게 풀 수 있다.

필자는 group by , distnct 2가지 방법으로 풀었고 Leetcode 기준 group by 가 실행 속도가 더 빨랐다.

select  author_id as 'id' from Views where author_id=viewer_id group by author_id order by author_id asc;
/* 위 문장 Group by */
select distinct author_id as 'id' from Views where author_id=viewer_id order by author_id asc;

197. Rising Temperature

SQL Schema

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature on a certain day.

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
Output: 
+----+
| id |
+----+
| 2  |
| 4  |
+----+
Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

이 문제는 다음날이 전날보다 온도가 높게 나온 Id 값을 가지고오는 문제이다.

처음 필자는 Inner Join 고 조건절로만 하면 해결 할 수 있다 판단하였다.

select w2.id from Weather as w1 join Weather as w2 on w2.temperature>w1.temperature

하지만 실행결과 내 생각과는 완전히 달랐고 이는 곧 날짜 차이에 대한 부분을 보완해야 했다.

따라서 이 또한 DBMS에서 제공하는 명령어가 있다고 생각한 나머지 검색한 결과

DATEDIFF라는 명령어를 알 수 있었다.

작성방법

DATEDIFF

DATEDIFF(날짜1,날짜2)

날짜1-날짜2 를 진행한다.

이 명령어를 통해 문제에서 각 하루별로 온도 상승 ID를 찾기에

DATEDIFE(w2.recordDate, w1.recordDate)=1로 할 수 있을 것이다.

select w2.id from Weather as w1 join Weather as w2 on w2.temperature>w1.temperature and datediff(w2.recordDate,w1.recordDate)=1

1158. Market Analysis I

Medium

33852Add to ListShare

SQL Schema

Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id is the primary key of this table.
This table has the info of the users of an online shopping website where users can sell and buy items.

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id is the primary key of this table.
item_id is a foreign key to the Items table.
buyer_id and seller_id are foreign keys to the Users table.

Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the primary key of this table.

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output: 
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1         | 2018-01-01 | 1              |
| 2         | 2018-02-09 | 2              |
| 3         | 2018-01-19 | 0              |
| 4         | 2018-05-21 | 0              |
+-----------+------------+----------------+

이 문제는 예전 고교 시절 프로젝트로 쇼핑몰 웹 프로젝트를 만들면서 데이터베이스 또한 비슷하게 만든 기억이 난다.

필자는 이 부분에 대해 테이블이 3개가 있고 출력하는 값은 각 사용자가 가입 날과 2019년에 결제한 카운트를 출력하는 과정이다.

select U.user_id as 'buyer_id',U.join_date,count(O.order_date) as 'orders_in_2019' from 
Users as U Join Orders as O 
on U.user_id=O.buyer_id and Year(O.order_date)=2019

처음 필자는 각각 필요한 조건으로 Inner Join으로 생각하였고 2019년도를 알기 위해 order_date를 Year명령어로 지정하였다.

하지만 이 SQL문에는 문제점이 있다. Inner Join으로 인한 , Group By로 지정하지 않아 buyer_id 1번으로 카운트 3개가 나온다.

이 부분에 대해 다시 생각하였고 문제를 잘 보면 buyer_id -> Users테이블에 user_id 속성이다 따라서 Users 테이블을 LEFT JOIN으로 값을 기준 잡아야 했다.

또한 Group by로 user_id 잡으면 원하는 값이 나올 거라 생각이 된다.

select U.user_id as 'buyer_id',U.join_date,count(O.order_date) as 'orders_in_2019' 
from Users as U Left Join Orders as O 
on U.user_id=O.buyer_id and Year(O.order_date)=2019
group by U.user_id

오늘은 새로운 명령와 함께 테이블이 하나가 아닌 여러개에 대한 상황 문제를 풀어보았다.

DBMS에서 제공하는 명령어는 많고 각각 상황에서 알맞는 명령어는 대부분 지원하고 있다.

대부분 있는지 몰라서, 작성하는 방법이 어려워서 등 몰랐던 내용을 알고 직접 작성하면서 SQL문을 조금이라도 더 빨리 실행시키기 위해, 메모리를 적게 사용하는 SQL문장을 위해 더 공부하고 노력해야한다.