π 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_r | income_travel_r | bachelors_travel_r |
---|---|---|
0.7 | 0.06 | -0.14 |
income_travel_r
,bachelors_travel_r
κ°μ μκ΄ κ΄κ³κ° κ±°μ μμμ λνλΈλ€.- νΉν
bachelors_travel_r
λ μκ΄κ³λ₯Ό λνλΈλ€. - μκ΄ κ΄κ³λ₯Ό ν μ€νΈν λλ λ€μ λͺ κ°μ§ μ£Όμ μ¬νμ μΌλμ λμ΄μΌ νλ€.
- κ°ν μκ΄ κ΄κ³μ‘°μ°¨ μΈκ³Ό κ΄κ³λ₯Ό μλ―Ένμ§λ μλλ€.
- μκ΄ κ΄κ³κ° ν΅κ³μ μΌλ‘ μ μνμ§ μ¬λΆλ₯Ό νμΈνκΈ° μν΄ ν
μ€νΈλ₯Ό κ±°μ³μΌ νλ€.
- μ΄ ν μ€νΈλ μ± μμ λ€λ£¨λ λ²μ μ΄μμ μμμ΄λ€.
νκ· λΆμμΌλ‘ κ° μμΈ‘νκΈ°
- λͺ¨λ λ°μ΄ν° ν¬μΈνΈμ μ€κ°μ μ§λλ μ§μ μ μ΅μ μ κ³± νκ·μ μ΄λΌκ³ νλ€.
- λ³μ κ°μ κ΄κ³λ₯Ό κ°μ₯ μ μ€λͺ νλ μ§μ μ μ΅μ μ ν©μ κ°κΉλ€.
- νκ·μ μ λν λ°©μ μμ κΈ°μΈκΈ°-μ νΈ κ³΅μκ³Ό μ μ¬νμ§λ§, λ€λ₯Έ μ΄λ¦μ λ³μλ₯Ό μ¬μ©νμ¬ μμ±λμλ€.
Y = bX + a
λΌλ 곡μμ κ΅¬μ± μμλ λ€μκ³Ό κ°λ€.
Y
: μμΈ‘λ κ°μ΄λ©°,y
μΆμ κ°μ΄κ±°λ μ’ μ λ³μμ΄λ€.b
- μ μ κΈ°μΈκΈ°μ΄λ©°, μμ λλ μμμΌ μ μλ€.
x
μΆ κ°μ κ° λ¨μμ λν΄y
μΆ κ°μ΄ μ¦κ°νκ±°λ κ°μν λ¨μ μλ₯Ό μΈ‘μ νλ€.
X
:x
μΆμ κ° λλ λ 립 λ³μλ₯Ό λνλΈλ€.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;
- 쿼리λ₯Ό μ€ννλ©΄ κ²°κ³Όκ° λ€μκ³Ό κ°λ€.
slope | y_intercept |
---|---|
1,016.55 | 29,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 μ¬μ΄μμ μλ―Ένλ€.
λΆμ°μ κ³μ°νλ ν¨μ
var_pop(numeric)
- μ λ ₯ κ°μ λͺ¨μ§λ¨ λΆμ°μ κ³μ°νλ€.
- μ΄ μ»¨ν μ€νΈμμ λͺ¨μ§λ¨μ κ°λ₯ν λͺ¨λ κ°μ ν¬ν¨νλ€.
var_samp(numeric)
- μ λ ₯ κ°μ νλ³Έ λΆμ°μ κ³μ°νλ€.
- 무μμ νλ³Έ μ‘°μ¬μμμ κ°μ΄ λͺ¨μ§λ¨μμ μνλ§λ λ°μ΄ν°μ ν¨κ» μ¬μ©νλ€.
νμ€νΈμ°¨λ₯Ό κ³μ°νλ ν¨μ
stddev_pop(numeric)
: λͺ¨μ§λ¨ νμ€νΈμ°¨λ₯Ό κ³μ°νλ€.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;
company | widget_output | rank | dense_rank |
---|---|---|---|
Miles Amalgamated | 620,000 | 1 | 1 |
Arthur Industries | 244,000 | 2 | 2 |
Fischer Worldwide | 201,000 | 3 | 3 |
Saito Widget Co. | 201,000 | 3 | 3 |
Dream Widget Inc. | 196,000 | 5 | 4 |
Ariadne Widget Masters | 143,000 | 6 | 5 |
Mal Inc. | 133,000 | 7 | 6 |
Dom Widgets | 125,000 | 8 | 7 |
- κ²°κ³Όλ₯Ό 보면 μ μ μλ―μ΄,
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);
category | store | unit_sales | rank |
---|---|---|---|
Beer | Wallace | 988 | 1 |
Beer | Broders | 641 | 2 |
Beer | Cramers | 640 | 3 |
Cereal | Broders | 1,104 | 1 |
Cereal | Cramers | 1,003 | 2 |
Cereal | Wallace | 980 | 3 |
Ice Cream | Broders | 2,517 | 1 |
Ice Cream | Cramers | 2,112 | 2 |
Ice Cream | Wallace | 1,863 | 3 |
- λ§μ§λ§ 쿼리λ₯Ό ν΅ν΄ μΉ΄ν κ³ λ¦¬ λ³λ‘ κ° μμ μ νλ§€λ μμλ₯Ό 보μ¬μ£Όλ κ²°κ³Ό μ§ν©μ λ§λ λ€.
- κ° μΉ΄ν
κ³ λ¦¬μ λν νμ μμλ₯Ό νμνλ
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;
- μΈκ΅¬ μ² λͺ λΉ μ¬μ 체 μλ₯Ό νμΈνκΈ° μν 쿼리λ€.
county | st | establishments | pop_est_2018 | estabs_per_1000 |
---|---|---|---|---|
Cape May County | New Jersey | 925 | 92,446 | 10 |
Worcester County | Maryland | 453 | 51,960 | 8.7 |
Monroe County | Florida | 540 | 74,757 | 7.2 |
Warren County | New York | 427 | 64,215 | 6.6 |
New York County | New York | 10,428 | 1,629,055 | 6.4 |
Hancock County | Maine | 337 | 54,734 | 6.2 |
Sevier County | Tennessee | 570 | 97,895 | 5.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;
- κ°κ·€λ₯μ λλμ μλ³ μμΆμ‘μ 보μ¬μ£Όλ λ°μ΄ν°λ₯Ό κ°μ Έμ€λ 쿼리λ€.
year | month | citrus_export_value |
---|---|---|
2,019 | 10 | 26,308,151 |
2,019 | 11 | 60,885,676 |
2,019 | 12 | 84,873,954 |
2,020 | 1 | 110,924,836 |
2,020 | 2 | 171,767,821 |
2,020 | 3 | 201,231,998 |
2,020 | 4 | 122,708,243 |
2,020 | 5 | 75,644,260 |
2,020 | 6 | 36,090,558 |
2,020 | 7 | 20,561,815 |
2,020 | 8 | 15,510,692 |
- 첫 λ²μ§Έ 쿼리λ₯Ό μ€νν κ²°κ³Ό μ€ λ§μ§λ§ 12κ°μ νμ΄λ€.
year | month | citrus_export_value | twelve_month_avg |
---|---|---|---|
2,019 | 9 | 14,012,305 | 74,465,440 |
2,019 | 10 | 26,308,151 | 74,756,757 |
2,019 | 11 | 60,885,676 | 74,853,312 |
2,019 | 12 | 84,873,954 | 74,871,644 |
2,020 | 1 | 110,924,836 | 75,099,275 |
2,020 | 2 | 171,767,821 | 78,874,520 |
2,020 | 3 | 201,231,998 | 79,593,712 |
2,020 | 4 | 122,708,243 | 78,278,945 |
2,020 | 5 | 75,644,260 | 77,999,174 |
2,020 | 6 | 36,090,558 | 78,045,059 |
2,020 | 7 | 20,561,815 | 78,343,206 |
2,020 | 8 | 15,510,692 | 78,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.