초안 업로드.. 속초 여행 피곤하지만 조금이라도 했다
# Write your MySQL query statement below
SELECT
DATE_FORMAT(t.trans_date, '%Y-%m') as month
, t.country
FROM Transactions as t
ORDER BY DATE_FORMAT(t.trans_date, '%Y-%m'), t.country이렇게만 끝내면 너무 날먹같으니.. 조금 서술하면(사실 노트북으로 잠깐 문제 해석하며 주석달아놓은게 날라갔다)
Transactions테이블은 입금관련 정보를 가지고 있다.
(25.07.28)
위 코드로는 아래와 같이 똑같은 2018-12 | US가 제대로 GROUP되지 않는 문제가 발생한다.
| month | country |
| ------- | ------- |
| 2018-12 | US |
| 2018-12 | US |
| 2019-01 | DE |
| 2019-01 | US |GROUP BY의 순서를 바꾸어보아도 똑같았다. 어떻게 해결할 수 있을까?
아래와 같이 SELECT 이후 날짜 부분에 DISTINCT를 붙여 해결할 수 있었다.
아마 ORDER BY 내장함수로 사용한 날짜 추출 값으로 ORDER BY를 수행하는 것이 아닌 해당 속성의 값을 보아 다른 값(일자가 다르니)으로 인식한 듯 하다. 이를 DISTINCT로 결과 필터링을 수행한 개념으로 인지하면 될 듯.
# Write your MySQL query statement below
# state는 입출금 승인,실패를 나타낸다.
# 각 나라와 도시 별로 거래건수와 총량, 승인된 거래의 개수와 총량을 나타내라.
# Write your MySQL query statement below
SELECT
DISTINCT DATE_FORMAT(t.trans_date, '%Y-%m') as month,
t.country
FROM Transactions as t
ORDER BY DATE_FORMAT(t.trans_date, '%Y-%m'), t.country| month | country |
| ------- | ------- |
| 2018-12 | US |
| 2019-01 | DE |
| 2019-01 | US |그 뒤 값을 계산하는 수식을 작성해보았다.
# Write your MySQL query statement below
# state는 입출금 승인,실패를 나타낸다.
# 각 나라와 도시 별로 거래건수와 총량, 승인된 거래의 개수와 총량을 나타내라.
# Write your MySQL query statement below
SELECT
DISTINCT DATE_FORMAT(t.trans_date, '%Y-%m') as month,
t.country,
COUNT(*) as trans_count,
SUM(IF(t.state="approved", 1, 0)) as approved_count,
SUM(IF(t.state="approved", t.amount, 0)) as trans_total_amount
FROM Transactions as t
ORDER BY DATE_FORMAT(t.trans_date, '%Y-%m'), t.country그러자 위처럼 나올 것이라 기대했던 결과가 아래와 같이 나왔다.
| month | country | trans_count | approved_count | trans_total_amount |
| ------- | ------- | ----------- | -------------- | ------------------ |
| 2018-12 | US | 4 | 3 | 5000 |해결해보자. 우선 count, amount의 값은 전체를 기준으로 봤을 때 잘 계산되었다. 문제는 ORDER BY가 잘 수행되게 하는 것이다.
집계함수의 범위가 전체로 잡힌 듯 한데..
집계함수를 사용하자마자 데이터 그루핑이 사라지니, JOIN을 이용해 3개의 값이 나오는 SQL에 데이터를 붙이고 값을 계산하였다.
# Write your MySQL query statement below
# state는 입출금 승인,실패를 나타낸다.
# 각 나라와 도시 별로 거래건수와 총량, 승인된 거래의 개수와 총량을 나타내라.
# Write your MySQL query statement below
SELECT
t1.month,
t1.country,
COUNT(*) as trans_count,
SUM(IF(t2.state="approved", 1, 0)) as approved_count,
SUM(t2.amount) as trans_total_amount,
SUM(IF(t2.state="approved", t2.amount, 0)) as approved_total_amount
FROM(
SELECT
DISTINCT DATE_FORMAT(t.trans_date, '%Y-%m') as month,
t.country
FROM Transactions as t
ORDER BY DATE_FORMAT(t.trans_date, '%Y-%m'), t.country
) as t1 JOIN Transactions as t2
ON (t1.month = DATE_FORMAT(t2.trans_date, '%Y-%m') AND t1.country=t2.country)
GROUP BY t1.month, t1.country
null값 처리까지 해야한다라..
확인해보니 JOIN에 사용한 SELECT 문에서는 정상적으로 null이 출력된다. 바깥 식을 수정하면 될 듯 하다.
(25.07.29)
어디서 null이 사라지는지 찾아보자.
우선 위에 말했듯이 내부 SELECT는 null이 정상출력된다.
그리고 최상위 SELECT에 *을 출력해보자.
우선 최상위 SELECT나 내부 SELECT의 문제는 아니다.
내부 SELECT의 문제가 없었으니 LEFT JOIN을 통해 null을 살려보자.
잘 됐는데 왜 내부 함수에서 저렇게 처리되는지 잘 모르겠다..
우선 현재까지의 코드이다.
# Write your MySQL query statement below
# state는 입출금 승인,실패를 나타낸다.
# 각 나라와 도시 별로 거래건수와 총량, 승인된 거래의 개수와 총량을 나타내라.
# Write your MySQL query statement below
SELECT
t1.month,
t1.country,
COUNT(*) as trans_count,
SUM(IF(t2.state="approved", 1, 0)) as approved_count,
SUM(t2.amount) as trans_total_amount,
SUM(IF(t2.state="approved", t2.amount, 0)) as approved_total_amount
FROM(
SELECT
DISTINCT DATE_FORMAT(t.trans_date, '%Y-%m') as month,
t.country
FROM Transactions as t
ORDER BY DATE_FORMAT(t.trans_date, '%Y-%m'), t.country
) as t1 LEFT JOIN Transactions as t2
ON (t1.month = DATE_FORMAT(t2.trans_date, '%Y-%m') AND t1.country=t2.country)
GROUP BY t1.month, t1.country