Post

🐘 PostgreSQL κΈ°λ³Έ β…ͺ - SQL 톡계 ν•¨μˆ˜

🐘 PostgreSQL κΈ°λ³Έ β…ͺ - SQL 톡계 ν•¨μˆ˜

πŸ“™ γ€Žμ‹€μš© SQL』을 읽고 μ •λ¦¬ν•œ κΈ€μž…λ‹ˆλ‹€.

κ°œμš”

  • λŒ€κ°œλŠ” SPSS, SAS, R, Python을 톡해 톡계 뢄석을 μˆ˜ν–‰ν•œλ‹€.
  • κ·ΈλŸ¬λ‚˜ PostgreSQL κ΅¬ν˜„μ„ λΉ„λ‘―ν•΄ ν‘œμ€€ ANSI SQL은 데이터셋을 λ‹€λ₯Έ ν”„λ‘œκ·Έλž¨μœΌλ‘œ 내보낼 ν•„μš” 없이 데이터에 λŒ€ν•œ λ§Žμ€ 정보λ₯Ό λ³΄μ—¬μ£ΌλŠ” λͺ‡ κ°€μ§€ κ°•λ ₯ν•œ 톡계 κΈ°λŠ₯을 μ œκ³΅ν•œλ‹€.

인ꡬ쑰사 톡계 ν…Œμ΄λΈ” μƒμ„±ν•˜κΈ°

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE acs_2014_2018_stats (
	geoid text CONSTRAINT geoid_key PRIMARY KEY,
	county text NOT NULL,
	st text NOT NULL,
	pct_travel_60_min numeric(5,2),
	pct_bachelors_higher numeric(5,2),
	pct_masters_higher numeric(5,2),
	median_hh_income integer,
	CHECK (pct_masters_higher <= pct_bachelors_higher)
);

COPY acs_2014_2018_stats
FROM 'C:\YourDirectory\acs_2014_2018_stats.csv'
WITH (FORMAT CSV, HEADER);

SELECT * FROM acs_2014_2018_stats;
  • pct_travel_60_min: μΆœν‡΄κ·Ό μ‹œκ°„μ΄ 60λΆ„ 이상인 16μ„Έ 이상 근둜자의 λΉ„μœ¨μ΄λ‹€.
  • pct_bachelors_higher: ꡐ윑 μˆ˜μ€€μ΄ 학사 이상인 25μ„Έ 이상 인ꡬ의 λΉ„μœ¨μ΄λ‹€.
  • pct_masters_higher: ꡐ윑 μˆ˜μ€€μ΄ 석사 이상인 25μ„Έ 이상 인ꡬ의 λΉ„μœ¨μ΄λ‹€.
  • median_hh_income: 2018λ…„ μΈν”Œλ ˆμ΄μ…˜ μ‘°μ • λ‹¬λŸ¬ κΈ°μ€€ μΉ΄μš΄ν‹°μ˜ 평균 가계 μ†Œλ“μ΄λ‹€.
  • λ―Έκ΅­μ—μ„œλŠ” 학사 ν•™μœ„κ°€ 석사 ν•™μœ„ 이전 λ˜λŠ” λ™μ‹œμ— μ·¨λ“λ˜κΈ° λ•Œλ¬Έμ— 학사 ν•™μœ„ μˆ˜μΉ˜κ°€ 석사 ν•™μœ„ μˆ˜μΉ˜μ™€ κ°™κ±°λ‚˜ 높은지 ν™•μΈν•˜κΈ° μœ„ν•΄ CHECK μ œμ•½ 쑰건을 ν¬ν•¨ν•œλ‹€.

corr(Y, X)λ₯Ό μ‚¬μš©ν•˜μ—¬ 상관 관계 μΈ‘μ •ν•˜κΈ°

  • 상관 κ΄€κ³„λž€ ν•œ λ³€μˆ˜μ˜ λ³€ν™”κ°€ λ‹€λ₯Έ λ³€μˆ˜μ˜ 변화에 영ν–₯을 λ―ΈμΉ˜λŠ” 정도λ₯Ό μ˜λ―Έν•œλ‹€.
  • 이 값은 두 λ³€μˆ˜ κ°„μ˜ 톡계적 관계λ₯Ό μΈ‘μ •ν•΄ κ΅¬ν•œλ‹€.
  • ν•„μš”ν•œ λ°°κ²½ 지식은 λ‹€μŒκ³Ό κ°™λ‹€.

  • 일반적으둜 r둜 ν‘œμ‹œλ˜λŠ” ν”Όμ–΄μŠ¨ 상관 κ³„μˆ˜λŠ” 두 λ³€μˆ˜ κ°„μ˜ μ„ ν˜• κ΄€κ³„μ˜ 강도λ₯Ό μ •λŸ‰ν™”ν•˜κΈ° μœ„ν•œ 척도닀.
  • ν•œ λ³€μˆ˜μ˜ 증가 λ˜λŠ” κ°μ†Œκ°€ λ‹€λ₯Έ λ³€μˆ˜μ™€ μ—°κ΄€λ˜λŠ” 정도λ₯Ό 보여쀀닀.
  • r 값은 -1κ³Ό 1 사이에 μž‡λ‹€.
  • λ²”μœ„μ˜ 끝인 -1κ³Ό 1은 μ™„λ²½ν•œ 상관 관계λ₯Ό 보여쀀닀.
  • 반면, 0에 κ°€κΉŒμš΄ 값은 상관 관계가 μ—†λŠ” λ¬΄μž‘μœ„ 뢄포λ₯Ό λ‚˜νƒ€λ‚Έλ‹€.
  • μ–‘μˆ˜ r 값은 직접 관계에 μžˆλŠ” 각 κ°’ μŒμ„ λ‚˜νƒ€λ‚΄λ©°, 데이터 ν¬μΈνŠΈλŠ” μ™Όμͺ½μ—μ„œ 였λ₯Έμͺ½ μœ„λ‘œ 이어진닀.
  • 음의 r 값은 역관계λ₯Ό λ‚˜νƒ€λ‚Έλ‹€.
  • ν•œ λ³€μˆ˜κ°€ μ¦κ°€ν•˜λ©΄ λ‹€λ₯Έ λ³€μˆ˜λŠ” κ°μ†Œν•˜λ©°, 데이터 ν¬μΈνŠΈλŠ” μ™Όμͺ½μ—μ„œ 였λ₯Έμͺ½ μ•„λž˜λ‘œ κΈ°μšΈμ–΄μ§„λ‹€.
상관 κ³„μˆ˜ν•΄μ„
0.00 ~ 0.19맀우 μ•½ν•œ 상관
0.20 ~ 0.39μ•½ν•œ 상관
0.40 ~ 0.59쀑간 μ •λ„μ˜ 상관
0.60 ~ 0.79κ°•ν•œ 상관
0.80 ~ 1.00맀우 κ°•ν•œ 상관
  • ν‘œμ€€ ANSI SQL 및 PostgreSQLμ—μ„œλŠ” corr(Y, X)λ₯Ό μ‚¬μš©ν•˜μ—¬ ν”Όμ–΄μŠ¨ 상관 κ³„μˆ˜λ₯Ό κ³„μ‚°ν•œλ‹€.
  • corr(Y, X)λŠ” μ—¬λŸ¬ 이진 집계 ν•¨μˆ˜ 쀑 ν•˜λ‚˜λ‹€.
  • 이진 집계 ν•¨μˆ˜λž€ 두 개의 μž…λ ₯을 λ°›μ•„λ“€μ΄λŠ” ν•¨μˆ˜μ΄λ‹€.
  • 이진 집계 ν•¨μˆ˜μ—μ„œ μž…λ ₯ YλŠ” λ‹€λ₯Έ λ³€μˆ˜ 값에 μ˜μ‘΄ν•˜μ—¬ 그에 따라 값이 λ‹¬λΌμ§€λŠ” 쒅속 λ³€μˆ˜μ΄κ³ , XλŠ” 값이 λ‹€λ₯Έ λ³€μˆ˜μ— μ’…μ†λ˜μ§€ μ•ŠλŠ” 독립 λ³€μˆ˜μ΄λ‹€.
1
2
3
SELECT corr(median_hh_income, pct_bachelors_higher)
	AS bachelors_income_r
FROM acs_2014_2018_stats;
bachelors_income_r
0.6999086503
  • 이 μ–‘μˆ˜ r 값은 μΉ΄μš΄ν‹°μ˜ ꡐ윑 정도가 증가함에 따라 가ꡬ μ†Œλ“μ΄ μ¦κ°€ν•˜λŠ” κ²½ν–₯이 강함을 λ‚˜νƒ€λ‚Έλ‹€.

μΆ”κ°€ 상관 관계 λ‚˜νƒ€λ‚΄κΈ°

1
2
3
4
5
6
7
8
9
10
11
SELECT
	round(
		corr(median_hh_income, pct_bachelors_higher)::numeric, 2
		) AS bachelors_income_r,
	round(
		corr(pct_travel_60_min, median_hh_income)::numeric, 2
		) AS income_travel_r,
	round(
		corr(pct_travel_60_min, pct_bachelors_higher)::numeric, 2
		) AS bachelors_travel_r
FROM acs_2014_2018_stats;
  • μ΄λ²ˆμ—λŠ” μ†Œμˆ˜ 값을 λ°˜μ˜¬λ¦Όν•˜μ—¬ 좜λ ₯을 더 읽기 μ‰½κ²Œ λ§Œλ“  쿼리닀.
  • 좜λ ₯은 λ‹€μŒκ³Ό κ°™λ‹€.
bachelors_income_rincome_travel_rbachelors_travel_r
0.70.06-0.14
  • income_travel_r, bachelors_travel_r 값은 상관 관계가 거의 μ—†μŒμ„ λ‚˜νƒ€λ‚Έλ‹€.
  • 특히 bachelors_travel_rλŠ” 역관계λ₯Ό λ‚˜νƒ€λ‚Έλ‹€.
  • 상관 관계λ₯Ό ν…ŒμŠ€νŠΈν•  λ•ŒλŠ” λ‹€μŒ λͺ‡ κ°€μ§€ 주의 사항을 염두에 두어야 ν•œλ‹€.
  1. κ°•ν•œ 상관 관계쑰차 인과 관계λ₯Ό μ˜λ―Έν•˜μ§€λŠ” μ•ŠλŠ”λ‹€.
  2. 상관 관계가 ν†΅κ³„μ μœΌλ‘œ μœ μ˜ν•œμ§€ μ—¬λΆ€λ₯Ό ν™•μΈν•˜κΈ° μœ„ν•΄ ν…ŒμŠ€νŠΈλ₯Ό 거쳐야 ν•œλ‹€.
    • 이 ν…ŒμŠ€νŠΈλŠ” μ±…μ—μ„œ λ‹€λ£¨λŠ” λ²”μœ„ μ΄μƒμ˜ μ˜μ—­μ΄λ‹€.

νšŒκ·€ λΆ„μ„μœΌλ‘œ κ°’ μ˜ˆμΈ‘ν•˜κΈ°

  • λͺ¨λ“  데이터 포인트의 쀑간을 μ§€λ‚˜λŠ” 직선을 μ΅œμ†Œ 제곱 νšŒκ·€μ„ μ΄λΌκ³  ν•œλ‹€.
  • λ³€μˆ˜ κ°„μ˜ 관계λ₯Ό κ°€μž₯ 잘 μ„€λͺ…ν•˜λŠ” μ§μ„ μ˜ 졜적 적합에 가깝닀.
  • νšŒκ·€μ„ μ— λŒ€ν•œ 방정식은 기울기-절편 곡식과 μœ μ‚¬ν•˜μ§€λ§Œ, λ‹€λ₯Έ μ΄λ¦„μ˜ λ³€μˆ˜λ₯Ό μ‚¬μš©ν•˜μ—¬ μž‘μ„±λ˜μ—ˆλ‹€.
  • Y = bX + aλΌλŠ” κ³΅μ‹μ˜ ꡬ성 μš”μ†ŒλŠ” λ‹€μŒκ³Ό κ°™λ‹€.
  1. Y: 예츑된 값이며, yμΆ•μ˜ κ°’μ΄κ±°λ‚˜ 쒅속 λ³€μˆ˜μ΄λ‹€.
  2. b
    • μ„ μ˜ 기울기이며, μ–‘μˆ˜ λ˜λŠ” 음수일 수 μžˆλ‹€.
    • xμΆ• κ°’μ˜ 각 λ‹¨μœ„μ— λŒ€ν•΄ yμΆ• 값이 μ¦κ°€ν•˜κ±°λ‚˜ κ°μ†Œν•  λ‹¨μœ„ 수λ₯Ό μΈ‘μ •ν•œλ‹€.
  3. X: xμΆ•μ˜ κ°’ λ˜λŠ” 독립 λ³€μˆ˜λ₯Ό λ‚˜νƒ€λ‚Έλ‹€.
  4. a: y 절편, 즉 x 값이 0일 λ•Œ 선이 yμΆ•κ³Ό κ΅μ°¨ν•˜λŠ” 값이닀.
  • κ°€λ Ή x좕이 학사 ν•™μœ„ μ΄μƒμ˜ λΉ„μœ¨μ„ λ‚˜νƒ€λ‚΄κ³ , y좕이 가계 μ†Œλ“ 쀑앙값을 λ‚˜νƒ€λ‚Έλ‹€κ³  ν•˜μž.
  • μ΄λ•Œ μΉ΄μš΄ν‹° 인ꡬ의 30%κ°€ 학사 ν•™μœ„ 이상을 κ°€μ§€κ³  μžˆλ‹€λ©΄ μΉ΄μš΄ν‹°μ˜ 가계 μ†Œλ“ 쀑간 값은 μ–Όλ§ˆμΌκΉŒ?
  • νšŒκ·€μ„  κ³΅μ‹μ˜ Xμžλ¦¬μ— 30을 λ„£μœΌλ©΄ Y = b(30) + a와 κ°™λ‹€.
  • μ˜ˆμƒ 가ꡬ μ†Œλ“ 쀑앙값을 λ‚˜νƒ€λ‚΄λŠ” Yλ₯Ό κ΅¬ν•˜λ €λ©΄ μ„ μ˜ 기울기인 b와 y 절편인 aκ°€ ν•„μš”ν•˜λ‹€.
  • μ΄λŸ¬ν•œ 값을 μ–»κΈ° μœ„ν•΄ λ‹€μŒκ³Ό 같이 regr_slope(Y, X)와 regr_intercept(Y, X)λ₯Ό μ‚¬μš©ν•œλ‹€.
1
2
3
4
5
6
7
8
SELECT
	round(
		regr_slope(median_hh_income, pct_bachelors_higher)::numeric, 2
		) AS slope,
	round(
		regr_intercept(median_hh_income, pct_bachelors_higher)::numeric, 2
		) AS y_intercept
FROM acs_2014_2018_stats;
  • 쿼리λ₯Ό μ‹€ν–‰ν•˜λ©΄ κ²°κ³Όκ°€ λ‹€μŒκ³Ό κ°™λ‹€.
slopey_intercept
1,016.5529,651.42
  • 이제 두 값을 방정식에 λ„£μ–΄ κ²°κ³Όλ₯Ό κ΅¬ν•˜λ©΄ μ†Œλ“μ€ μ•½ $60.148κ°€ 될 κ²ƒμœΌλ‘œ μ˜ˆμƒν•  수 μžˆλ‹€.
  • μš°λ¦¬κ°€ κ³„μ‚°ν•œ 상관 κ³„μˆ˜λŠ” 0.70으둜, ꡐ윑과 μ†Œλ“ μ‚¬μ΄μ˜ κ΄€κ³„λŠ” κ°•λ ₯ν•˜μ§€λ§Œ μ™„λ²½ν•˜μ§€ μ•ŠμŒμ„ κΈ°μ–΅ν•΄μ•Ό ν•œλ‹€.

r-μ œκ³±μ„ μ‚¬μš©ν•˜μ—¬ 독립 λ³€μˆ˜μ˜ 효과 μ°ΎκΈ°

1
2
3
4
SELECT round(
	regr_r2(median_hh_income, pct_bachelors_higher)::numeric, 3
	) AS r_squared
FROM acs_2014_2018_stats;
  • r-μ œκ³±μ€ κ²°μ • κ³„μˆ˜λΌκ³ λ„ ν•˜λ©°, 0κ³Ό 1 μ‚¬μ΄μ˜ 값을 κ°–κ³  독립 λ³€μˆ˜λ‘œ μ„€λͺ…λ˜λŠ” λ³€λ™μ˜ λ°±λΆ„λ₯ μ„ λ‚˜νƒ€λ‚Έλ‹€.
  • 예λ₯Ό λ“€μ–΄ r- 제곱 값이 0.1이면 독립 λ³€μˆ˜κ°€ 쒅속 λ³€μˆ˜μ˜ 10%λ₯Ό μ„€λͺ…ν•˜κ±°λ‚˜ μ „ν˜€ μ„€λͺ…ν•˜μ§€ μ•ŠλŠ”λ‹€κ³  ν•  수 μžˆλ‹€.
  • regr_r2(Y, X) ν•¨μˆ˜λ₯Ό μ‚¬μš©ν•˜μ—¬ r-제곱 값을 μ°ΎλŠ”λ‹€.
r_squared
0.49
  • μœ„ κ²°κ³ΌλŠ” μΉ΄μš΄ν‹°μ˜ 쀑간 가ꡬ μ†Œλ“ λ³€λ™μ˜ μ•½ 49%κ°€ ν•΄λ‹Ή μΉ΄μš΄ν‹°μ˜ 학사 ν•™μœ„ 이상을 κ°€μ§„ μ‚¬λžŒλ“€μ˜ λΉ„μœ¨λ‘œ μ„€λͺ…될 수 μžˆμŒμ„ λ‚˜νƒ€λ‚Έλ‹€.
  • λ‚˜λ¨Έμ§€ 51%λ₯Ό μ„€λͺ…ν•˜λŠ” 것은 μ—¬λŸ¬ μš”μΈμ΄ 될 수 μžˆλ‹€.
  • λ‹€μ‹œ ν•œλ²ˆ 상관 κ΄€κ³„λŠ” 인관 관계λ₯Ό 증λͺ…ν•˜μ§€λŠ” μ•ŠμŒμ„ λͺ…심해야 ν•œλ‹€.

λΆ„μ‚°κ³Ό ν‘œμ€€νŽΈμ°¨ μ°ΎκΈ°

  • λΆ„μ‚°κ³Ό ν‘œμ€€νŽΈμ°¨λŠ” 값듀이 ν‰κ· μ—μ„œ λ–¨μ–΄μ Έ μžˆλŠ” 정도λ₯Ό λ‚˜νƒ€λ‚Έλ‹€.
  • 뢄산은 (각 숫자 - 평균)^2의 평균이며 값이 많이 ν©μ–΄μ§ˆ 수둝 뢄산은 컀지며, κΈˆμœ΅μ—μ„œ 자주 μ‚¬μš©λœλ‹€.
  • κ°€λ Ή 주식 μ‹œμž₯ κ±°λž˜μžλŠ” 뢄산을 μ‚¬μš©ν•΄ νŠΉμ • μ£Όμ‹μ˜ 변동성을 μΈ‘μ •ν•˜μ—¬ ν•΄λ‹Ή 주식이 μ–Όλ§ˆλ‚˜ μœ„ν—˜ν•œ νˆ¬μžμΈμ§€ μ•Œ 수 μžˆλ‹€.
  • ν‘œμ€€νŽΈμ°¨λŠ” λΆ„μ‚°μ˜ 제곱근으둜, 일반적으둜 μ •κ·œ 뢄포λ₯Ό ν˜•μ„±ν•˜λŠ” 데이터λ₯Ό ν‰κ°€ν•˜λŠ”λ° κ°€μž₯ μœ μš©ν•˜λ‹€.
  • μ •κ·œ λΆ„ν¬λŠ” μ’… λͺ¨μ–‘μ˜ λŒ€μΉ­ κ³‘μ„ μœΌλ‘œ μ‹œκ°ν™”λ˜λ©°, κ°’μ˜ μ•½ 2/3λŠ” ν‰κ· μ˜ ν‘œμ€€νŽΈμ°¨ 1 이내에 μ†ν•œλ‹€.
  • 95%λŠ” ν‘œμ€€νŽΈμ°¨ 2개 λ²”μœ„ μ•ˆμ— μžˆλ‹€.
  • λ”°λΌμ„œ ν‘œμ€€νŽΈμ°¨λŠ” λŒ€λΆ€λΆ„μ˜ 값이 평균에 μ–Όλ§ˆλ‚˜ κ°€κΉŒμš΄μ§€ μ΄ν•΄ν•˜λŠ”λ° 도움이 λœλ‹€.
  • κ°€λ Ή λ―Έκ΅­ 성인 μ—¬μ„±μ˜ 평균 ν‚€λŠ” μ•½ 166cm이고 ν‘œμ€€νŽΈμ°¨λŠ” 6.5cmλΌλŠ” κ²°κ³Όκ°€ λ‚˜μ™”λ‹€κ³  ν•˜λ©΄, ν‚€κ°€ μ •κ·œ 뢄포λ₯Ό λ”°λ₯Έλ‹€λŠ” 점을 κ°μ•ˆν•˜λ©΄, μ΄λŠ” μ—¬μ„±μ˜ μ•½ 2/3κ°€ ν‰κ· μ˜ 6.5cm 이내인 159.5cmμ—μ„œ 172.5cm μ‚¬μ΄μž„μ„ μ˜λ―Έν•œλ‹€.
뢄산을 κ³„μ‚°ν•˜λŠ” ν•¨μˆ˜
  1. var_pop(numeric)
    • μž…λ ₯ κ°’μ˜ λͺ¨μ§‘단 뢄산을 κ³„μ‚°ν•œλ‹€.
    • 이 μ»¨ν…μŠ€νŠΈμ—μ„œ λͺ¨μ§‘단은 κ°€λŠ₯ν•œ λͺ¨λ“  값을 ν¬ν•¨ν•œλ‹€.
  2. var_samp(numeric)
    • μž…λ ₯ κ°’μ˜ ν‘œλ³Έ 뢄산을 κ³„μ‚°ν•œλ‹€.
    • λ¬΄μž‘μœ„ ν‘œλ³Έ μ‘°μ‚¬μ—μ„œμ™€ 같이 λͺ¨μ§‘λ‹¨μ—μ„œ μƒ˜ν”Œλ§λœ 데이터와 ν•¨κ»˜ μ‚¬μš©ν•œλ‹€.
ν‘œμ€€νŽΈμ°¨λ₯Ό κ³„μ‚°ν•˜λŠ” ν•¨μˆ˜
  1. stddev_pop(numeric): λͺ¨μ§‘단 ν‘œμ€€νŽΈμ°¨λ₯Ό κ³„μ‚°ν•œλ‹€.
  2. stddev_samp(numeric): μƒ˜ν”Œ ν‘œμ€€νŽΈμ°¨λ₯Ό κ³„μ‚°ν•œλ‹€.

예제 μ½”λ“œ

1
2
3
4
5
6
7
-- 전체 인ꡬ의 λΆ„μ‚°
SELECT var_pop(median_hh_income)
FROM acs_2014_2018_stats;

-- 전체 인ꡬ의 ν‘œμ€€νŽΈμ°¨
SELECT stddev_pop(median_hh_income)
FROM acs_2014_2018_stats;

SQL을 μ‚¬μš©ν•˜μ—¬ μˆœμœ„ λ§€κΈ°κΈ°

rank() 및 dense_rank()둜 μˆœμœ„ λ§€κΈ°κΈ°

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE widget_companies (
	id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	company text NOT NULL,
	widget_output integer NOT NULL
);

INSERT INTO widget_companies (company, widget_output)
VALUES
	('Dom Widgets', 125000),
	('Ariadne Widget Masters', 143000),
	('Saito Widget Co.', 201000),
	('Mal Inc.', 133000),
	('Dream Widget Inc.', 196000),
	('Miles Amalgamated', 620000),
	('Arthur Industries', 244000),
	('Fischer Worldwide', 201000);

SELECT
	company,
	widget_output,
	rank() OVER (ORDER BY widget_output DESC),
	dense_rank() OVER (ORDER BY widget_output DESC)
FROM widget_companies
ORDER BY widget_output DESC;
companywidget_outputrankdense_rank
Miles Amalgamated620,00011
Arthur Industries244,00022
Fischer Worldwide201,00033
Saito Widget Co.201,00033
Dream Widget Inc.196,00054
Ariadne Widget Masters143,00065
Mal Inc.133,00076
Dom Widgets125,00087
  • κ²°κ³Όλ₯Ό 보면 μ•Œ 수 μžˆλ“―μ΄, rank() ν•¨μˆ˜λŠ” μˆœμœ„ μˆœμ„œμ— 간격을 ν¬ν•¨ν•˜μ§€λ§Œ dense_rank() ν•¨μˆ˜λŠ” κ·Έλ ‡μ§€ μ•Šλ‹€.
  • μˆœμœ„ ν•¨μˆ˜ 뒀에 OVER ν‚€μ›Œλ“œλ₯Ό μ‚¬μš©ν•˜λŠ”λ°, κ΄„ν˜Έ μ•ˆμ—λŠ” ν•¨μˆ˜κ°€ μž‘λ™ν•΄μ•Ό ν•˜λŠ” ν–‰μ˜ 창을 μ§€μ •ν•˜λŠ” ν‘œν˜„μ‹μ„ λ°°μΉ˜ν•œλ‹€.
  • μ—¬κΈ°μ„œ μœˆλ„μš°λŠ” ν˜„μž¬ 행을 κΈ°μ€€μœΌλ‘œ μ„€μ •ν•œ ν–‰ μ§‘ν•©μœΌλ‘œ, μ—¬κΈ°μ„  두 ν•¨μˆ˜κ°€ λ‚΄λ¦Όμ°¨μˆœμœΌλ‘œ μ •λ ¬λœ widget_output μ—΄μ˜ λͺ¨λ“  ν–‰μ—μ„œ μž‘λ™ν•œλ‹€.
  • μ‹€μ œλ‘œλŠ” rank()κ°€ κ°€μž₯ 자주 μ‚¬μš©λ˜λŠ”λ°, Dream Widget Inc.보닀 μ•žμ„  νšŒμ‚¬κ°€ 총 4κ°œλΌλŠ” 사싀을 λ³΄μ—¬μ€ŒμœΌλ‘œμ¨ μˆœμœ„κ°€ 맀겨진 전체 νšŒμ‚¬ 수λ₯Ό 더 μ •ν™•ν•˜κ²Œ λ°˜μ˜ν•œλ‹€.

PARTITION BYλ₯Ό μ‚¬μš©ν•˜μ—¬ ν•˜μœ„ κ·Έλ£Ή λ‚΄ μˆœμœ„ μ§€μ •ν•˜κΈ°

  • λ•Œλ‘œλŠ” ν…Œμ΄λΈ”μ˜ ν–‰ κ·Έλ£Ή λ‚΄μ—μ„œ μˆœμœ„λ₯Ό μƒμ„±ν•˜κ³ μž ν•  수 μžˆλ‹€.
  • κ°€λ Ή 각 λΆ€μ„œ λ‚΄ κΈ‰μ—¬ λ³„λ‘œ 곡무원 μˆœμœ„λ₯Ό λ§€κΈ°κ±°λ‚˜, 각 μž₯λ₯΄ λ‚΄ ν₯ν–‰ μˆ˜μ΅μ„ κΈ°μ€€μœΌλ‘œ μ˜ν™” μˆœμœ„λ₯Ό λ§€κΈΈ 수 μžˆλ‹€.
  • μ΄λŸ¬ν•œ λ°©μ‹μœΌλ‘œ μœˆλ„μš° ν•¨μˆ˜λ₯Ό μ‚¬μš©ν•˜κΈ° μœ„ν•΄ OVER μ ˆμ— PARTITION BYλ₯Ό μΆ”κ°€ν•˜μ—¬ μ§€μ •ν•œ μ—΄μ˜ 값에 따라 ν…Œμ΄λΈ” 행을 λ‚˜λˆˆλ‹€.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE store_sales (
	store text NOT NULL,
	category text NOT NULL,
	unit_sales bigint NOT NULL,
	CONSTRAINT store_category_key PRIMARY KEY (store, category)
);

INSERT INTO store_sales (store, category, unit_sales)
VALUES
	('Broders', 'Cereal', 1104),
	('Wallace', 'Ice Cream', 1863),
	('Broders', 'Ice Cream', 2517),
	('Cramers', 'Ice Cream', 2112),
	('Broders', 'Beer', 641),
	('Cramers', 'Cereal', 1003),
	('Cramers', 'Beer', 640),
	('Wallace', 'Cereal', 980),
	('Wallace', 'Beer', 988);

SELECT
	category,
	store,
	unit_sales,
	rank() OVER (PARTITION BY category ORDER BY unit_sales DESC)
FROM store_sales
ORDER BY category, rank() OVER (PARTITION BY category
	ORDER BY unit_sales DESC);
categorystoreunit_salesrank
BeerWallace9881
BeerBroders6412
BeerCramers6403
CerealBroders1,1041
CerealCramers1,0032
CerealWallace9803
Ice CreamBroders2,5171
Ice CreamCramers2,1122
Ice CreamWallace1,8633
  • λ§ˆμ§€λ§‰ 쿼리λ₯Ό 톡해 μΉ΄ν…Œκ³ λ¦¬ λ³„λ‘œ 각 μƒμ μ˜ νŒλ§€λŸ‰ μˆœμœ„λ₯Ό λ³΄μ—¬μ£ΌλŠ” κ²°κ³Ό 집합을 λ§Œλ“ λ‹€.
  • 각 μΉ΄ν…Œκ³ λ¦¬μ— λŒ€ν•œ 행은 μˆœμœ„λ₯Ό ν‘œμ‹œν•˜λŠ” rank μ—΄κ³Ό ν•¨κ»˜ μ œν’ˆ νŒλ§€λŸ‰ 순으둜 μ •λ ¬λœλ‹€.
  • 이 κ°œλ…μ„ λ‹€μ–‘ν•œ 상황에 μ μš©ν•  수 μžˆλ‹€.

λΉ„μœ¨ 계산을 ν†΅ν•œ 의미 μžˆλŠ” κ²°κ³Ό μ°ΎκΈ°

  • 개수λ₯Ό 기반으둜 ν•œ μˆœμœ„κ°€ 항상 의미 μžˆλŠ” 것은 μ•„λ‹ˆλ‹€.
  • 사싀, 개수둜만 μˆœμœ„λ₯Ό λ§€κΈ°λ©΄ μ˜€ν•΄κ°€ 생긴닀.
  • κ°€λ Ή 2019년에 ν…μ‚¬μŠ€ μ£Όμ—μ„œ 377, 599λͺ…μ˜ μ•„κΈ°κ°€ νƒœμ–΄λ‚¬κ³ , μœ νƒ€μ£Όμ—μ„œ 46,826λͺ…μ˜ μ•„κΈ°κ°€ νƒœμ–΄λ‚¬λ‹€κ³  ν•  λ•Œ, ν…μ‚¬μŠ€μ˜ 여성듀이 μ•„κΈ°λ₯Ό 낳을 ν™•λ₯ μ΄ 더 λ†’λ‹€κ³  속단할 수 μžˆμ„κΉŒ?
  • κ·Έλ ‡μ§€ μ•Šλ‹€.
  • 2019λ…„ ν…μ‚¬μŠ€μ˜ μΆ”μ • μΈκ΅¬λŠ” μœ νƒ€μ˜ 9λ°°μ˜€κ³ , 이런 μƒν™©μ—μ„œ 두 주의 μΆœμƒ 수λ₯Ό λΉ„κ΅ν•˜λŠ” 것은 κ·Έλ‹€μ§€ μ˜λ―Έκ°€ μ—†λ‹€.
  • 이 숫자λ₯Ό λΉ„κ΅ν•˜λŠ” 더 μ •ν™•ν•œ 방법은 λΉ„μœ¨λ‘œ λ³€ν™˜ν•˜λŠ” 것이닀.

κ΄€κ΄‘ μ‚¬μ—…μ²΄μ˜ λΉ„μœ¨ κ΅¬ν•˜κΈ°

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE cbp_naics_72_establishments (
	state_fips text,
	county_fips text,
	county text NOT NULL,
	st text NOT NULL,
	naics_2017 text NOT NULL,
	naics_2017_label text NOT NULL,
	year smallint NOT NULL,
	establishments integer NOT NULL,
	CONSTRAINT cbp_fips_key PRIMARY KEY (state_fips, county_fips)
);

COPY cbp_naics_72_establishments
FROM 'C:\YourDirectory\cbp_naics_72_establishments.csv'
WITH (FORMAT CSV, HEADER);

SELECT *
FROM cbp_naics_72_establishments
ORDER BY state_fips, county_fips
LIMIT 5;
  • μœ„ μ½”λ“œλŠ” 인ꡬ 쑰사 μΉ΄μš΄ν‹° κΈ°μ—… νŒ¨ν„΄ 데이터에 λŒ€ν•œ ν…Œμ΄λΈ”μ„ μƒμ„±ν•˜κ³  데이터λ₯Ό κ°€μ Έμ˜€λŠ” 쿼리닀.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	cbp.county,
	cbp.st,
	cbp.establishments,
	pop.pop_est_2018,
	round( (cbp.establishments::numeric / pop.pop_est_2018) * 1000, 1 )
		AS estabs_per_1000
FROM cbp_naics_72_establishments cbp JOIN us_counties_pop_est_2019 pop
	ON cbp.state_fips = pop.state_fips
	AND cbp.county_fips = pop.county_fips
WHERE pop.pop_est_2018 >= 50000
ORDER BY cbp.establishments::numeric / pop.pop_est_2018 DESC;
  • 인ꡬ 천 λͺ…λ‹Ή 사업체 수λ₯Ό ν™•μΈν•˜κΈ° μœ„ν•œ 쿼리닀.
countystestablishmentspop_est_2018estabs_per_1000
Cape May CountyNew Jersey92592,44610
Worcester CountyMaryland45351,9608.7
Monroe CountyFlorida54074,7577.2
Warren CountyNew York42764,2156.6
New York CountyNew York10,4281,629,0556.4
Hancock CountyMaine33754,7346.2
Sevier CountyTennessee57097,8955.8
  • μœ„ λΉ„μœ¨μ€ λ‹¨μˆœ μ‚¬μ—…μ²΄μ˜ 수λ₯Ό λΉ„κ΅ν•œ 것이 μ•„λ‹ˆκΈ° λ•Œλ¬Έμ— 의미 μžˆλŠ” λ°μ΄ν„°μž„μ„ μ•Œ 수 μžˆλ‹€.

κ³ λ₯΄μ§€ μ•Šμ€ 데이터 닀듬기

  • 이동 평균은 λ°μ΄ν„°μ…‹μ—μ„œ 일정 κΈ°κ°„λ§ˆλ‹€ μΈ‘μ •ν•œ ν‰κ· μœΌλ‘œ, μΌμ •λŸ‰μ˜ 행을 μž…λ ₯으둜 μ‚¬μš©ν•œλ‹€.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE us_exports (
	year smallint,
	month smallint,
	citrus_export_value bigint,
	soybeans_export_value bigint
);

COPY us_exports
FROM 'C:\YourDirectory\us_exports.csv'
WITH (FORMAT CSV, HEADER);

-- 월별 감귀λ₯˜ μˆ˜μΆœλŸ‰ 확인
SELECT year, month, citrus_export_value
FROM us_exports
ORDER BY year, month;

-- 둀링 평균 계산
SELECT year, month, citrus_export_value,
	round(
		avg(citrus_export_value)
			OVER(ORDER BY year, month
				ROWS BETWEEN 11 PRECEDING AND CURRENT ROW), 0)
	AS twelve_month_avg
FROM us_exports
ORDER BY year, month;
  • 감귀λ₯˜μ™€ λŒ€λ‘μ˜ 월별 μˆ˜μΆœμ•‘μ„ λ³΄μ—¬μ£ΌλŠ” 데이터λ₯Ό κ°€μ Έμ˜€λŠ” 쿼리닀.
yearmonthcitrus_export_value
2,0191026,308,151
2,0191160,885,676
2,0191284,873,954
2,0201110,924,836
2,0202171,767,821
2,0203201,231,998
2,0204122,708,243
2,020575,644,260
2,020636,090,558
2,020720,561,815
2,020815,510,692
  • 첫 번째 쿼리λ₯Ό μ‹€ν–‰ν•œ κ²°κ³Ό 쀑 λ§ˆμ§€λ§‰ 12개의 행이닀.
yearmonthcitrus_export_valuetwelve_month_avg
2,019914,012,30574,465,440
2,0191026,308,15174,756,757
2,0191160,885,67674,853,312
2,0191284,873,95474,871,644
2,0201110,924,83675,099,275
2,0202171,767,82178,874,520
2,0203201,231,99879,593,712
2,0204122,708,24378,278,945
2,020575,644,26077,999,174
2,020636,090,55878,045,059
2,020720,561,81578,343,206
2,020815,510,69278,376,692
  • 두 번째 쿼리λ₯Ό μ‹€ν–‰ν•œ κ²°κ³Ό 쀑 λ§ˆμ§€λ§‰ 12개의 행이닀.
  • 이 κ²°κ³Όλ₯Ό 톡해 12κ°œμ›” λ‹¨μœ„ 이동 평균을 κ³„μ‚°ν•˜μ—¬ λ§€μ›” 수좜의 μ—°κ°„ μΆ”μ„Έλ₯Ό λ³Ό 수 μžˆλ‹€.
  • OVER μ ˆμ„ 톡해 평균을 계산할 데이터λ₯Ό 연도와 열을 따라 μ •λ ¬ν•˜κ³  ROWS BETWEEN 11 PRECEDING AND CURRENT ROWλ₯Ό μ‚¬μš©ν•˜μ—¬ 평균을 λ‚Ό ν–‰μ˜ 수λ₯Ό μ„€μ •ν•œλ‹€.
  • μ—¬κΈ°μ„œλŠ” ν˜„μž¬ ν–‰κ³Ό κ·Έ μ΄μ „μ˜ 11개의 ν–‰μœΌλ‘œ μ œν•œν•˜κ³  μžˆλ‹€.
  • μ΄ν›„μ—λŠ” SELECT에 avg() ν•¨μˆ˜λ₯Ό λ°°μΉ˜ν•˜μ—¬ citrus_export_value 열에 μžˆλŠ” κ°’μ˜ 평균을 κ³„μ‚°ν•œλ‹€.
  • μ—‘μ…€ 같은 톡계 ν”„λ‘œκ·Έλž¨μœΌλ‘œ κ²°κ³Όλ₯Ό κ·Έλž˜ν”„λ‘œ ν‘œμ‹œν•΄λ³΄λ©΄ 12κ°œμ›” 평균이 훨씬 더 일관성이 μžˆμŒμ„ μ•Œ 수 μžˆλ‹€.
  • μ•„μšΈλŸ¬ μ›”κ°„ λ°μ΄ν„°μ—μ„œλŠ” μ›€μ§μž„μ„ μ‹λ³„ν•˜κΈ° μ–΄λ ΅μ§€λ§Œ, 이동 평균을 보면 μ œλŒ€λ‘œ μ•Œ 수 μžˆλ‹€.
  • μœˆλ„μš° ν•¨μˆ˜λŠ” 뢄석을 μœ„ν•œ μ—¬λŸ¬ μ˜΅μ…˜μ„ μ œκ³΅ν•œλ‹€.
  • κ°€λ Ή 이동 평균을 κ΅¬ν•˜λŠ” λŒ€μ‹  sum() ν•¨μˆ˜λ₯Ό λŒ€μ²΄ν•˜μ—¬ 일정 κΈ°κ°„ λ™μ•ˆμ˜ 이동 합계λ₯Ό ꡬ할 μˆ˜λ„ μžˆλ‹€.
  • 7일 κΈ°μ€€ 이동 합계λ₯Ό κ³„μ‚°ν•˜λ©΄ λ°μ΄ν„°μ…‹μ—μ„œ μ£Όκ°„ 총계가 μ–Έμ œ μ§‘κ³„λ˜λŠ”μ§€ νŒŒμ•…ν•  수 μžˆλ‹€.

이동 평균 λ˜λŠ” 이동 합계 계산은 λ°μ΄ν„°μ˜ 기간에 λˆ„λ½λœ 값이 μ—†μ–΄μ•Ό 잘 κ³„μ‚°λœλ‹€. μœˆλ„μš° ν•¨μˆ˜λŠ” λ‚ μ§œκ°€ μ•„λ‹Œ ν–‰μ˜ 개수λ₯Ό κΈ°μ€€μœΌλ‘œ μ‚ΌμœΌλ―€λ‘œ λˆ„λ½λœ 값이 μžˆμ„ 경우 그만큼 값이 λ°€λ¦°λ‹€.

This post is licensed under CC BY 4.0 by the author.