2024-04-24

• Today

1. SQL, 파이썬 코드카타
2. ADsP 강의
3. SQL 라이브 세션 및 과제
4. 데이터 리터러시 강의 듣기(2시간)
 

---

• Today I Learned

파이썬
 
행렬의 덧셈
https://lyj-01.tistory.com/112

행렬의 덧셈

 
리스트를 더해주면 되겠다 해서 이전에 사용했던 [x+y for x,y in zip(arr1,arr2)] 를 사용해보았다

def solution(arr1, arr2):
    answer = [x+y for x,y in zip(arr1,arr2)]
    return answer

결괏값이 내가 생각한 것과 너무 달랐다.
이번 문제에는 arr1 안에 [1,2] 이렇게만 있는 것이 아니라, 행렬안에 행렬이 하나가 더 있는
 arr1 = [[1,2],[2,3]]
이런 형태였기 때문에 저런 값이 나온 듯!
 
구글링을 해보았더니 이 문제에서 나온 arr1 과 같은 형태는 2차원 리스트 라고 한다.
for 문 을 사용하여 각 리스트를 돌아가며 연산해주면 됨
이 과정에서 lambda(x,y:x+y) 함수와 map(함수,반복) 를 다시한번 사용해보았다.

def solution(arr1, arr2):
    answer = []
    for i in range(len(arr1)):
        answer.append(list(map((lambda x,y : x+y), arr1[i],arr2[i])))
    
    return answer

 
2차원 리스트를 더해주는 것은 여러가지 방법이 있으나 
구글링을 해서 찾아보았을 때 나에게 가장 익숙한 방법으로 풀이해보았다!
 
 
Q. arr1 =  [[1,2],[2,3]]  일 때 len(arr1)의 값 은?
A. 2
 
 
* 정욱튜터님의 꿀팁
파이썬 알고리즘은 어떤 문제를 풀 때 어떤 함수를 써야하는지 아는 것이 중요하다고 하셨다.
함수가 많기 때문에 다 외워서 풀기에는 어려울 수 있다.
 
 

---

 
데이터 리터러시

 
상관관계
: 두 변수가 얼마나 상호 의존적인지를 파악
: 한 변수가 증가하면 다른 변수도 따라서 증가/감소한다
 
인과관계
: 하나의 요인으로 인해 다른 요인의 수치가 변하는 형태
: 원인과 결과가 명확한 것
 
상관관계는 인과관계가 아니다
 
 
리터러시란?
눈 앞에 있는 데이터에 의존하지 않고 목적과 문제를 정의
목적달성을 위한 데이터와 지표를 설정
데이터를 어떻게 봐야 효과적으로 얻을 수 있는지 분석
데이터를 보는 방식, 방법론, 통계지식에 매몰되지 않는 것
왜? 를 항상 생각하기
 
 
 

---

 
SQL 라이브 세션

 

과제 설명

FROM 절 서브쿼리를 사용 할 때의 테이블명은 어떻게 써도 상관없지만

예약어들은 제외하는 것이 좋다.

 

별칭을 지을 때에는 AS 를 붙이는 것이 좋다.

 

COUNT 는 null 값을 세어주지 않는다.

select distinct 컬럼1, 컬럼2
 
> 컬럼에 뭐가 들었는지 확인하기 위해 사용
> 카테고리만 보아도 되는데, 상품명이 계속 반복되게 있을 경우!
> unique key 가 굳이 더 중복되게 있을 필요 없을 때 중복 제거
> 이 경우에 컬럼1과 컬럼2 둘 중에 중복값이 있을 때 distinct 를 해준다
 
ex.
 
컬럼1   컬럼2             
1            1
3            2
1            1
 
> (1, 1)
   (3, 2)

컬럼1   컬럼2
1            2
3            3
1            1
1            2
> (1, 2)
   (3, 3)
   (1, 1)
  
 
 
PK 값이 바뀌면 FK 값도 바뀌게 되는지?
> 자동으로 바뀌게 할 수도 있고, 바뀌지 않게도 사용 할 수 있다(설정하기 나름)
 
 
UNION 함수
# 수직결합
# 결합할 테이블의 컬럼명과 순서가 동일 해야 한다.
 
JOIN 함수
# 수평 결합
# PK: 기본키, NULL 일 수 없고, 유일한 값을 가짐, 기준 컬럼
# FK: 외래키, 다른 테이블의 PK 와 연결되어 테이블 간 관계를 나타내주는 컬럼, 연결컬럼
 
 
DISTINCT 하고 안하고일때
정렬 순서가 왜 다르게 나오는지?
> 정렬 순서의 차이이다
 
<과제>
 

# 문제1 case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 
# 결제를 한 유저와 결제를 하지 않은 유저를 추출해주세요.결과값은 아래와 같아야 합니다. 

SELECT CASE WHEN p.pay_amount  IS NULL THEN '결제안함'
	 		ELSE '결제함' END AS 'gb'
	 , COUNT(DISTINCT u.game_account_id) AS 'usercnt'
FROM users AS u
LEFT JOIN payment AS p
ON u.game_account_id = p.game_account_id 
GROUP BY gb;

# 처음 문제 풀이를 할 때 결제를 한 유저와 하지 않은 유저를 나눠줄 때 account_id 를 사용했었는데
튜터님께서 현업에서 분석을 한다면 pay_amount 를 사용하는게 좋다고 말씀해주셨다! 
그래서 수정 함
 
 

# 문제2 - 서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고
# game_account_id 를 기준으로 game_actor_id 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
# pay_amount 값을 더해주시고, sumamount 으로 컬럼명을 명시해주세요.
# having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출해주세요.
SELECT *
FROM 
	(
	SELECT u.game_account_id 
		 , COUNT(DISTINCT u.game_actor_id) AS 'actorcnt'
	   	 , (SUM(p.pay_amount)/COUNT(DISTINCT u.game_actor_id)) AS 'sumamount'    # 왜 actorcnt 로 나누어 주어야 하는지?
	FROM users AS u
	INNER JOIN payment AS p
	ON u.game_account_id = p.game_account_id 
	AND (serverno >= 2) 
	AND (pay_type = 'CARD')
	GROUP BY u.game_account_id 
	) AS a
WHERE actorcnt >= 2;

# 이렇게 푸는 건 정답이 아니다..
 
* 성훈튜터님께 여쭤본 결과
 
users 테이블과 payment 테이블이 그대로 JOIN 이 될 경우 
pay_amount 값이 game_account_id 만큼 들어가게 된다
ex.

select u.game_account_id , u.game_actor_id ,p.pay_amount 
from users AS u
	INNER JOIN payment AS p
	ON u.game_account_id = p.game_account_id 
where u.game_account_id = '00veuotg-lavb-jzrt-9h7z-bguzzjr8m9u9' AND p.pay_type = 'CARD'

# 멘토링 후 풀이
SELECT u.game_account_id
	 , actorcnt
	 , SUM(p.pay_amount) AS 'sumamount'
FROM 
	(
	SELECT game_account_id 
		 , COUNT(DISTINCT game_actor_id) AS 'actorcnt'
	FROM users 
	WHERE serverno >= 2
	GROUP BY game_account_id
	) u
INNER JOIN
	(
	SELECT *
	FROM payment 
	WHERE pay_type = 'CARD'
	) p
ON u.game_account_id = p.game_account_id
WHERE actorcnt >= 2
GROUP BY 1

 
① users 테이블에서 game_account_id 를 그룹화 해준 후
game_actor_id 를 count 해준다.
② payment 테이블에서 원하는 값을 출력하여 users 테이블과 join 해준다!
>> users 에서 account_id 값과 payment 의 account_id 값이 일치하는 값일 때의 SUM() 을 해준다..
 

# 문제3 - user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 
-- 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행해주세요.
-- 그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 
-- 그리고 컬럼이름을 *diffdate로 설정해주세요.* 두 날짜의 형식은 같아야 합니다.
-- 마지막으로, 인라인 뷰 subquery 를 이용하여  서버별 평균 diffdate를 구해주세요. 
-- 다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다.  
-- 또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 
-- 그리고 서버번호를 기준으로 내림차순 정렬해주세요.

SELECT serverno
	 , avgdiffdate
FROM 
	(
	SELECT serverno
	 	 , ROUND(AVG(DATEDIFF(DATE_FORMAT(last_approved,'%Y-%m-%d'), DATE_FORMAT(`date`, '%Y-%m-%d')))) AS 'avgdiffdate'
	FROM
		(
		SELECT game_account_id 
		 	 , `date` 
			 , serverno 
		FROM users 
		) AS a
	INNER JOIN
		(
		SELECT game_account_id 
		 	 , MAX(approved_at) AS last_approved
		FROM payment 
		GROUP BY game_account_id 
		) AS b
	ON a.game_account_id = b.game_account_id
	GROUP BY serverno
	) c
WHERE avgdiffdate >= 10
ORDER BY serverno DESC;

 
# 이렇게 풀 경우 AVG(diffdatie) 를 10일 이상일때를 뽑아주기 때문에 잘 못 됐음
# 문제에서 요구했던 것은 diffdate 값이 10일 이상일 경우이다.
 

# 동기분과 얘기 후 다시 쓴 쿼리

SELECT serverno
	 , ROUND(AVG(diffdate)) AS 'avgdiffdate'
FROM 
	(
	SELECT u.game_account_id
		 , u.`date`
		 , u.serverno
		 , p.last_approved
	 	 , DATEDIFF(DATE_FORMAT(last_approved,'%Y-%m-%d'), DATE_FORMAT(`date`, '%Y-%m-%d')) AS 'diffdate'
	FROM
		(
		SELECT game_account_id 
		 	 , `date` 
			 , serverno 
		FROM users 
		) AS u
	INNER JOIN
		(
		SELECT game_account_id 
		 	 , MAX(approved_at) AS last_approved
		FROM payment 
		GROUP BY game_account_id 
		) AS p
	ON u.game_account_id = p.game_account_id
	) a
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY serverno DESC;

 
① 결제일자 - 접속일자 인 diffdate 값을 구해준다.
② diffdate 값은 WHERE 절에서 10일 이상 조건을 구해줌!
 

---

• Next

1. SQL, 파이썬 코드카타
2. ADsP 인공신경망 내용 정리
3. 파이썬 강의 학습