ππ₯π₯ λΌμΌλ¦¬μ μ½λ λΆμ€λ¬κΈ° ππ₯π₯
ποΈ λ°μ΄ν°λΆμμ μν SQL λ μνΌ μ±
μ μ½κ³ μ 리 / μμ½ ν λ΄μ©μ
λλ€.
μκ³μ΄μ λ°λ₯Έ μ¬μ©μ μ 체μ μν λ³ν μ°ΎκΈ° μ¬μ©μλ μλΉμ€ μ¬μ© μμμΌλΆν° μκ°μ΄ μ§λλ©΄μ ‘μΆ©μ±λ λμ μ¬μ©μ’, ‘μ¬μ©μ μ€μ§’, ‘ν΄λ©΄’ λ±μΌλ‘ μνκ° λ³ν μ¬μ©μκ° κ³μν΄μ μ¬μ©(리νΌνΈ) μ¬μ©μκ° μ¬μ©μ μ€λ¨(νν΄/ν΄λ©΄) μλΉμ€ μ 곡μλ μ¬μ©μκ° μ΄λ μ λμμ κ³μ μ¬μ©νκ³ μλμ§ νμ
νκ³ λͺ©νμμ 괴리λ₯Ό μ΄λ»κ² μ’ν κ² μΈμ§ λ°©μμ κ²ν ν΄λ©΄ μ¬μ©μλ₯Ό μ΄λ»κ² λ€μ μ¬μ©νκ² λ§λ€μ§λ μκ° μμ νν΄μ κ²½μ° λ³΅κ·λ μ΄λ €μ°λ ν΄λ©΄ μ¬μ©μλ λ©μΌ 맀거μ§/CM/κ΄κ³ λ±μ νμ©νμ¬ λ€μ μ¬μ©νκ² μ λ μν λ°μ΄ν° SNS μ¬μ©μ λ§μ€ν° ν
μ΄λΈ SNS μ‘μ
λ‘κ·Έ ν
μ΄λΈ μ¬μ©μ λ§μ€ν° ν
μ΄λΈκ³Ό μ‘μ
λ‘κ·Έ ν
μ΄λΈ 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10') , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2016-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2016-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2016-10-10', 'pc' , '2016-10-10') , ('U007', 'F', '1950-07-18', '2016-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2016-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2016-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2016-10-16', 'pc' , NULL ) , ('U011', 'F', '1993-10-21', '2016-10-18', 'pc' , NULL ) , ('U012', 'M', '1993-12-22', '2016-10-18', 'app', NULL ) , ('U013', 'M', '1988-02-09', '2016-10-20', 'app', NULL ) , ('U014', 'F', '1994-04-07', '2016-10-25', 'sp' , NULL ) , ('U015', 'F', '1994-03-01', '2016-11-01', 'app', NULL ) , ('U016', 'F', '1991-09-02', '2016-11-01', 'pc' , NULL ) , ('U017', 'F', '1972-05-21', '2016-11-01', 'app', NULL ) , ('U018', 'M', '2009-10-12', '2016-11-01', 'app', NULL ) , ('U019', 'M', '1957-05-18', '2016-11-01', 'pc' , NULL ) , ('U020', 'F', '1954-04-17', '2016-11-03', 'app', NULL ) , ('U021', 'M', '2002-08-14', '2016-11-03', 'sp' , NULL ) , ('U022', 'M', '1979-12-09', '2016-11-03', 'app', NULL ) , ('U023', 'M', '1992-01-12', '2016-11-04', 'sp' , NULL ) , ('U024', 'F', '1962-10-16', '2016-11-05', 'app', NULL ) , ('U025', 'F', '1958-06-26', '2016-11-05', 'app', NULL ) , ('U026', 'M', '1969-02-21', '2016-11-10', 'sp' , NULL ) , ('U027', 'F', '2001-07-10', '2016-11-10', 'pc' , NULL ) , ('U028', 'M', '1976-05-26', '2016-11-15', 'app', NULL ) , ('U029', 'M', '1964-04-06', '2016-11-28', 'pc' , NULL ) , ('U030', 'M', '1959-10-07', '2016-11-28', 'sp' , NULL ) ; 12.
ποΈ λ°μ΄ν°λΆμμ μν SQL λ μνΌ μ±
μ μ½κ³ μ 리 / μμ½ ν λ΄μ©μ
λλ€.
11. μ¬μ©μ μ 체μ νΉμ§κ³Ό κ²½ν₯ μ°ΎκΈ° μλΉμ€λ₯Ό μ 곡νλ κ²μ μ¬μ©μμκ² κ°μΉλ₯Ό μ 곡νλ κ²
μλΉμ€ μ 곡μ μΈ‘μμ κ΄λ ¨ μ λ³΄λ‘ μκ³ μΆμ κ²
μ¬μ©μμ μμ±(λμ΄, μ±λ³, μ£Όμμ§) μ¬μ©μμ νλ(ꡬ맀ν μν, μ¬μ©ν κΈ°λ₯, μ¬μ©ν λΉλ) μν λ°μ΄ν° μ¬μ©μ λ§μ€ν° ν
μ΄λΈ κ°μ
μ μ¬μ©μ μ±λ³, κ°μ
λ μ§, μ¬μ© μ₯μΉ λ± νν΄μλ λ μ§λ§ κΈ°λ‘ μ‘μ
λ‘κ·Έ ν
μ΄λΈ νμ΄μ§ μ΄λ, κ΄μ¬ μν λ±λ‘, μΉ΄νΈ μΆκ°, ꡬ맀, 리뷰 λ± κ° μ‘μ
μ μ΄λ¦μ λͺ
μ user_idκ° NULL μΈ κ²½μ° λΉλ‘κ·ΈμΈ νμ μ‘μ
λ‘κ·Έ ν
μ΄λΈμ λ°λ‘ λ§λ€κ³ μ‘μ
λ΄μ©μ μ μ΄λλ©΄ λ³λμ JOIN, UNION μμ΄ λ°μ΄ν°λ₯Ό λ€λ£° μ μμ μ¬μ©μ λ§μ€ν° ν
μ΄λΈκ³Ό μ‘μ
λ‘κ·Έ ν
μ΄λΈ 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 -- μ¬μ©μ λ§μ€ν° μ 보 DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10') , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2016-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2016-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2016-10-10', 'pc' , '2016-10-10') , ('U007', 'F', '1950-07-18', '2016-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2016-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2016-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2016-10-16', 'pc' , NULL ) ; -- μ‘μ
λ‘κ·Έ DROP TABLE IF EXISTS action_log; CREATE TABLE action_log( session varchar(255) , user_id varchar(255) , action varchar(255) , category varchar(255) , products varchar(255) , amount integer , stamp varchar(255) ); INSERT INTO action_log VALUES ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('989004ea', 'U001', 'view' , NULL , NULL , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'favorite', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'review' , 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 18:01:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001,D002', NULL, '2016-11-03 18:02:00') , ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2016-11-03 18:10:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D001' , NULL, '2016-11-03 19:00:00') , ('47db0370', 'U002', 'purchase', 'drama' , 'D001' , 1000, '2016-11-03 20:00:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D002' , NULL, '2016-11-03 20:30:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A004' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A005' , NULL, '2016-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A006' , NULL, '2016-11-04 12:00:00') , ('9afaf87c', 'U002', 'purchase', 'drama' , 'D002' , 1000, '2016-11-04 13:00:00') , ('9afaf87c', 'U001', 'purchase', 'action', 'A005,A006', 1000, '2016-11-04 15:00:00') ; 11.
κ΅¬κΈ κ³μ μμ± κΈ°λ³Έ 90μΌ λμ $300 ν¬λ λ§μΌλ‘ λ¬΄λ£ νκ°νμ μ¬μ©ν μ μλ€. λ¬΄λ£ μ²΄ν κΈ°κ°μ΄ μ§λλ©΄ λ¬΄λ£ μ²΄ν μ€ μμ±ν 리μμ€λ λͺ¨λ μ€μ§ λλ©° μ λ£ κ³μ μΌλ‘ μ
κ·Έλ μ΄λ νμ§ μμΌλ©΄ μκΈμ΄ λΆκ³Όλμ§ μλλ€. μ νλ‘μ νΈ λ²νΌμ ν΄λ¦νμ¬ νλ‘μ νΈ μμ±νκΈ° λ°μ΄ν° μΈνΈ μμ±νκΈ° λ°μ΄ν° μΈνΈλ ν
μ΄λΈκ³Ό λ·°μ λν μ‘μΈμ€λ₯Ό κ΅¬μ± λ° μ μ΄νλ μ΅μμ 컨ν
μ΄λμ΄λ€. ν
μ΄λΈμ΄λ λ·°λ λ°λμ λ°μ΄ν° μΈνΈμ μν΄μΌ νλ―λ‘ μ΅μ ν κ° μ΄μμ λ°μ΄ν° μΈνΈλ₯Ό ꡬμ±ν΄μΌ νλ€.
Google Cloud Platform μ½μ λ©λ΄μμ BigQuery λ©λ΄λ₯Ό μ ν
ποΈ λ°μ΄ν°λΆμμ μν SQL λ μνΌ μ±
μ μ½κ³ μ 리 / μμ½ ν λ΄μ©μ
λλ€.
10. λ€λ©΄μ μΈ μΆμ μ¬μ©ν΄ λ°μ΄ν° μ§μ½νκΈ° 맀μΆμ μκ³μ΄λΏλ§ μλλΌ μνμ μΉ΄ν
κ³ λ¦¬, κ°κ²© λ±μ μ‘°ν©νμ¬ λ°μ΄ν°μ νΉμ§μ μΆμΆν΄ 리ν¬ν
μν λ°μ΄ν° λ§€μΆ λ°μ΄ν° λ‘κ·Έ 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 28 29 30 DROP TABLE IF EXISTS purchase_detail_log; CREATE TABLE purchase_detail_log( dt varchar(255) , order_id integer , user_id varchar(255) , item_id varchar(255) , price integer , category varchar(255) , sub_category varchar(255) ); INSERT INTO purchase_detail_log VALUES ('2017-01-18', 48291, 'usr33395', 'lad533', 37300, 'ladys_fashion', 'bag') , ('2017-01-18', 48291, 'usr33395', 'lad329', 97300, 'ladys_fashion', 'jacket') , ('2017-01-18', 48291, 'usr33395', 'lad102', 114600, 'ladys_fashion', 'jacket') , ('2017-01-18', 48291, 'usr33395', 'lad886', 33300, 'ladys_fashion', 'bag') , ('2017-01-18', 48292, 'usr52832', 'dvd871', 32800, 'dvd' , 'documentary') , ('2017-01-18', 48292, 'usr52832', 'gam167', 26000, 'game' , 'accessories') , ('2017-01-18', 48292, 'usr52832', 'lad289', 57300, 'ladys_fashion', 'bag') , ('2017-01-18', 48293, 'usr28891', 'out977', 28600, 'outdoor' , 'camp') , ('2017-01-18', 48293, 'usr28891', 'boo256', 22500, 'book' , 'business') , ('2017-01-18', 48293, 'usr28891', 'lad125', 61500, 'ladys_fashion', 'jacket') , ('2017-01-18', 48294, 'usr33604', 'mem233', 116300, 'mens_fashion' , 'jacket') , ('2017-01-18', 48294, 'usr33604', 'cd477' , 25800, 'cd' , 'classic') , ('2017-01-18', 48294, 'usr33604', 'boo468', 31000, 'book' , 'business') , ('2017-01-18', 48294, 'usr33604', 'foo402', 48700, 'food' , 'meats') , ('2017-01-18', 48295, 'usr38013', 'foo134', 32000, 'food' , 'fish') , ('2017-01-18', 48295, 'usr38013', 'lad147', 96100, 'ladys_fashion', 'jacket') ; 10.
ποΈ λ°μ΄ν°λΆμμ μν SQL λ μνΌ μ±
μ μ½κ³ μ 리 / μμ½ ν λ΄μ©μ
λλ€.
λ§€μΆ λ°μ΄ν°λ₯Ό μκ³μ΄λ‘ μ§κ³ νκΈ° λ§€μΆ λ°μ΄ν°λ₯Ό μκ³μ΄λ‘ μ§κ³νλ©΄ κ·μΉμ±μ μ°Ύκ±°λ, μ΄λ€ κΈ°κ°κ³Ό λΉκ΅νμ λ λ³ννμ νμΈ ν μ μλ€. 9. μκ³μ΄ κΈ°λ°μΌλ‘ λ°μ΄ν° μ§κ³νκΈ° λ§€μΆ λ°μ΄ν° λ‘κ·Έ dt order_id user_id purchase_amount 2014-01-01 1 rhwpvvitou 13900 2014-01-01 2 hqnwoamzic 10616 2014-01-02 3 tzlmqryunr 21156 2014-01-02 4 wkmqqwbyai 14893 2014-01-03 5 ciecbedwbq 13054 2014-01-03 6 svgnbqsagx 24384 2014-01-03 7 dfgqftdocu 15591 2014-01-04 8 sbgqlzkvyn 3025 2014-01-04 9 lbedmngbol 24215 2014-01-04 10 itlvssbsgx 2059 2014-01-05 11 jqcmmguhik 4235 2014-01-05 12 jgotcrfeyn 28013 2014-01-05 13 pgeojzoshx 16008 2014-01-06 14 msjberhxnx 1980 2014-01-06 15 tlhbolohte 23494 2014-01-06 16 gbchhkcotf 3966 2014-01-07 17 zfmbpvpzvu 28159 2014-01-07 18 yauwzpaxtx 8715 2014-01-07 19 uyqboqfgex 10805 2014-01-08 20 hiqdkrzcpq 3462 2014-01-08 21 zosbvlylpv 13999 2014-01-08 22 bwfbchzgnl 2299 2014-01-09 23 zzgauelgrt 16475 2014-01-09 24 qrzfcwecge 6469 2014-01-10 25 njbpsrvvcq 16584 2014-01-10 26 cyxfgumkst 11339 9.
ποΈ λ°μ΄ν°λΆμμ μν SQL λ μνΌ μ±
μ μ½κ³ μ 리 / μμ½ ν λ΄μ©μ
λλ€.
λ°μ΄ν°λ₯Ό κ°κ³΅ν΄μΌ νλ μ΄μ λ€λ£° λ°μ΄ν°κ° λ°μ΄ν° λΆμ μ©λλ‘ μμ λμ§ μμ κ²½μ° μ°μ°ν λ λΉκ΅ κ°λ₯ν μνλ₯Ό λ§λ€κ³ μ€λ₯λ₯Ό ννΌνκΈ° μν κ²½μ° 5. νλμ κ° μ‘°μνκΈ° 5.1 μ½λ κ°μ λ μ΄λΈλ‘ λ³κ²½νκΈ° λν΄νΈ κ°μ μ§μ ν κ²½μ° ELSE ꡬ문μ μ¬μ©νλ€.
1 2 3 4 5 6 7 8 9 SELECT user_id , CASE WHEN register_device = 1 THEN 'λ°μ€ν¬ν±' WHEN register_device = 2 THEN 'μ€λ§νΈν°' WHEN register_device = 3 THEN 'μ ν리μΌμ΄μ
' ELSE '' END as device_name FROM mst_users; 5.