2024-05-29

• Today

1. SQL 코드카타(96번)

2. 알고리즘 코드카타(41번)

3. Python Basic 4회차 수강

4. SQL Basic 2회차 문제풀이

5. SQL Basic 3회차 수강

 

---

• Today I Learned

 

SQL

 

특정 조건을 만족할 때 건수 구하기

SELECT SUBSTR(trans_date,1,7) month
     , country
     , COUNT(id) trans_count
     , SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) approved_count
     , SUM(amount) trans_total_amount
     , SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) approved_total_amount 
FROM transactions
GROUP BY month, country

state 가 'approved' 면 1, 아니면 0 

파이썬 개인과제때처럼 단순히 건수를 구하는 것이라면 조건에 맞을 때 1 아니면 0 이렇게 해도 되겠다!

 

처음에는 WHERE 절에 state = 'approved' 를 넣었는데

그랬더니 모든 조건에 approved 일 때가 들어가버려서 원하던 결과값이 아니었다.

 

 

SQL Basic 2회차 문제

# 문제 1 지난 달 주문한 고객의 이름을 조회하세요.
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
# 지난달보다 큰 order_date 
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH')
# 이번달보다 작은 order_date
AND order_date < DATE_TRUNC('month', CURRENT_DATE)
);

 

CURRENT_DATE

: 현재날짜

 

INTERVAL '1 MONTH'

: 1달

 

CURREND_DATE - INTERVAL '1 MONTH'

: 현재날짜에서 1달을 뺀

 

DATE_TRUNC('month',date)

: date 값을 월의 시작으로 자르기

>> 2024-05-01

 

4월1일보다 크고, 5월1일보다 작은

>> 지난달

 

참조: https://velog.io/@yshjft/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-SQL-Fundamentals-Date-Timestamp-Interval

 

# 문제 2 각 도시별로 가장 많은 주문을 한 고객의 이름과 주문 횟수를 조회하세요.
SELECT city
	 , customer_name
	 , order_count
FROM (
	SELECT c.city
		 , c.customer_name
		 , COUNT(o.oder_id) order_count
         # 도시별로 order_id 를 카운트한 숫자를 내림차순으로 순서 매기기
		 , RANK() OVER (PARTITION BY c.city ORDER BY COUNT(o.order_id) DESC) rnk
	FROM customers c JOIN orders o ON c.customer_id = o.customer_id
	GROUP BY c.city, c.customer_name
	) a
WHERE rnk = 1;

# 문제 3 총 주문 금액이 고객 평균 주문 금액보다 높은 고객의 이름과 총 주문 금액을 조회하세요.
SELECT customer_name
	 , total_amount
FROM (
# 두 테이블을 조인하여 고객별 총 주문금액 컬럼을 추출
	SELECT c.customer_name
		 , SUM(o.amount) total_amount
	FROM customers c JOIN orders o ON c.customer_id = o.customer_id
	GROUP BY c.customer_name
	) sub
# 평균 주문금액보다 큰 총 주문금액 구하기
WHERE total_amount > (
	# 평균 주문금액 구하기
	SELECT AVG(total_amount) 
	FROM(
    # 총 주문금액 구하기
		SELECT SUM(amount) total_amount
		FROM orders
		GROUP BY customer_id
		) avg_sub
	);

 

---

Python

 

문자열 인덱스마다 대/소문자로 바꾸기

def solution(s):
    answer = ''
    s_list = s.split(' ')
    for i in s_list:
        for j in range(len(i)):
            if j % 2 ==0 :
                answer += i[j].upper()
            else:
                answer += i[j].lower()
        answer += ' '
    return answer[:-1]

 

https://lyj-01.tistory.com/163

이상한 문자 만들기

split

를 사용해서 단어를 하나하나 띄어주는 것 까지는 진행했지만

그 후 진행방식을 모르겠어서 코드를 찾아봤다.

 

answer += ' '

 

이 구문이 이해되지 않아서 조금 헤맸던 코드!

자세한 것은 따로 코드풀이 포스팅을 작성했다.

 

---

 

 

Python Basic 4회차

 

 

matplotlib

https://lyj-01.tistory.com/139

[Python] 시각화 그래프

지난번 시각화 그래프 강의를 내가 보기 편하게 정리했던 포스팅도 첨부!

 

# 라인 그래프 그리기
d1=df2.groupby('Category')['Customer ID'].count().reset_index()
dplot1=plt.figure(figsize = (4,3))
x=d1['Category']
y=d1['Customer ID']
plt.plot(x,y,color='red',marker='o',alpha=0.5,linewidth=2)

plt.title("group by category - user cnt")
plt.xlabel("category")
plt.ylabel("usercnt")

 

 

marker:

Matplotlib 공식 문서 - Marker API

alpha: 투명도

linewidth: line 두께

 

 

*

stack: 컬럼이 인덱스로 옮겨지고 df 가 Series 로 바뀌게 된다.

unstack 인덱스를 컬럼으로 풀어준다.

# 성별이 컬럼으로
d2=df2.groupby(['Category','Gender'])['Customer ID'].count().unstack(1)

# 카테고리가 컬럼으로
d2=df2.groupby(['Category','Gender'])['Customer ID'].count().unstack(0)

d2.stack()

를 하게되면 d2 데이터프레임이 시리즈로 풀린다!

 

 

누적 막대그래프를 그리고 싶다면

stacked=True

#누적 막대그래프 그리기

dplot9=d2.plot(kind='bar', stacked=True, color=['#F4D13B','#9b59b6'])
plt.title("bar plot2")
plt.xlabel("category")
plt.ylabel("usercnt")

 

# 파이차트 그리기
piedf=df2.groupby('Gender')['Customer ID'].count().reset_index()

dplot7=plt.figure(figsize=(5,5))
plt.pie(
    x=piedf['Customer ID'],
    labels=piedf['Gender'],
    autopct='%1.1f',        # 소수점 첫째자리까지  표시
    colors=['#F4D13B','#9b59b6'],
    startangle=90,
)
plt.title("pie plot", loc='center', pad=1,fontsize=8,fontweight="bold")
plt.show()

* 숫자 뒤에 % 를 붙이고 싶다면

autopct='%1.1f%%'

 

이렇게 %% 를 적으면 된다!

 

# 산점도 그리기

d3=df2.groupby('Age')['Purchase Amount (USD)'].mean().reset_index()
plt.scatter(d3['Age'],d3['Purchase Amount (USD)'], c="#9b59b6")

c: 컬러

 

# 여러개의 그래프 그리기

#figure,ax 만들기
fig,ax=plt.subplots(2,2)

#그래프 그리기
ax[0,0].plot(np.linspace(0,100,20),np.linspace(0,100,20)**2, marker='o', markersize=2, markeredgecolor='y')
ax[0,1].hist(np.random.randn(500), bins=30, color='purple', alpha=0.5)
ax[1,0].bar([1,3,5,7],np.array([1,2,3,4]),color='r')
ax[1,1].boxplot(np.random.randn(500,5))

#그래프 사이 간격 추가
fig.subplots_adjust(hspace=0.5,wspace=0.3)

#그래프별 타이틀 추가
ax[0,0].set_title("line plot", fontsize=9) 
ax[0,1].set_title("hist plot", fontsize=9)  
ax[1,0].set_title("bar plot", fontsize=9)  
ax[1,1].set_title("box plot", fontsize=9)  
plt.show()

그래프의 갯수를 정하기

fix,ax=plt.subplots(2,2)

ㅁㅁ

ㅁㅁ

fix,ax=plt.subplots(2,3)

ㅁㅁㅁ

ㅁㅁㅁ

0,0 line plot

0,1 hist plot

1,0 bar

1,1 boxplot

 

 

상관관계 그래프 그리기

 

df10 = df2[['Age','Purchase Amount (USD)','Review Rating','Previous Purchases']]
df10.corr()

df.corr()

:  각 열간의 상관계수를 반환

 

# 상관관계 히트맵 그리기

dplot10 = sns.heatmap(df10.corr(),annot = True, cmap = 'viridis')
dplot10.set(title='corr plot')

annot = True

: 각 칸안에 숫자를 넣을 것인지

cmap

: 색상

상관관계

1에 가까울 수록 비례

양의 상관관계

1에 멀어질수록 반비례

음의 상관관계

0에 가까우면 아무 상관 없는 것

joinplot: hex 가 짙어질 수록 밀도가 있는 것

Altair: 동적 그래프

 

*대시보드는 전체데이터에 대한 이해를 위해,

앞쪽에 배치를 해주는 것이 좋음

                                                                                                                                                                                                                                                                                  

 

 

---

• Next

1. Python Basic 4 회차 복습( 이중축 그래프, 피라미드, 대시보드)

2. SQL 코드카타(97번)

3. 알고리즘 코드카타(42번)

4. SQL Basic 3회차 문제풀이

5. SQL Basic 4회차 수강