Како вежбати SQL и Python заједно са реалним проблемима

Почетна » Питон » Како вежбати SQL и Python заједно са реалним проблемима

Ако покушавате да се вратите SQL-у и Python-у након неколико година одсуства, сасвим је нормално да се осећате изгубљено. – посебно ако сте на последњој позицији користили власничке алате и удобне Databricks свеске које више немате. Модерни огласи за посао који захтевају Python, SQL, па чак и PySpark могу изгледати застрашујуће када сваки водич почиње са нечим попут „учитајте свој скуп података о захтевима у своје складиште података“ и ви помислите: „То је управо оно што немам.“

Добра вест је да већину тог искуства учења можете поново створити на свом лаптопу. користећи бесплатне алате, мале скупове података и структурирани скуп задатака за вежбање. У овом водичу ћемо, једноставним језиком, објаснити како да изградимо реалистично локално окружење, како SQL функционише (од основних упита до JOIN-ова и агрегације) и како да обложимо те SQL упите у Пајтон како бисте могли да вежбате тачно оне задатке са којима ћете се сусрести у модерним пословима са подацима.

Изградња једноставног локалног окружења за вежбање помоћу SQLite-а и Python-а

Не треба вам потпуно складиште података или Spark кластер да бисте вежбали SQL плус PythonЗа учење и припрему за интервју, лагана уграђена база података попут SQLite-а је више него довољна. SQLite чува све своје податке у једној датотеци на диску, што га чини савршеним за пројекте играчака, прототипове и образовне вежбе.

Концептуално, SQLite база података много личи на табелу са више листовасваки лист је табела, сваки ред је запис, и свака колона је пољеУ жаргону релационих база података, табеле се понекад називају „релације“, редови „торке“, а колоне „атрибути“, али за практичан рад можете се слободно држати свакодневних термина табела, ред и колона.

Пајтон се испоручује са уграђеним SQLite драјвером под називом sqlite3, што значи да не морате да инсталирате посебан сервер базе података. Ваш Пајтон скрипт ће отворити везу са .sqlite датотеку (креирањем ако не постоји), набавите курсор објекат (веома сличан дескриптору датотеке), а затим шаљите SQL команде кроз тај курсор користећи execute(). Погледајте наше SQLite SELECT и WHERE водич за практичне примере читања и филтрирања података.

Иако се овај чланак фокусира на покретање SQLite-а из Пајтона, постоји и практичан GUI алат под називом „Прегледач базе података за SQLite“ (понекад се дистрибуира као DB Browser за SQLite). Помоћу њега можете визуелно прегледати табеле, ручно уметати или уређивати неколико редова и покретати једноставне SQL изразе. То је као уређивач текста за датотеке базе података: брза ручна подешавања су лакша у графичком корисничком интерфејсу, али све што се понавља или је сложено је боље написати у Пајтону.

Релационе базе података су ригидније од Пајтон листа или диктанта: оне инсистирају на дефинисаној шемиКада креирате табелу, морате декларисати имена колона и типове података које очекујете (текст, цео број, датум/време итд.). SQLite ће затим чувати и индексирати податке на начин који одржава ефикасност претраге, чак и када ваш скуп података превазиђе оно што удобно стане у меморију. За практичне путеве учења и практичне примере, консултујте анализа података помоћу SQL-а.

Креирање табела и уношење података помоћу SQL-а и Python-а

Да бисте почели са вежбањем, прво вам је потребна табела – замислите је као дизајнирање облика ваших податакаПретпоставимо да желите малу табелу музичке библиотеке. Коришћење Пајтоновог sqlite3 модул можете се повезати са датотеком базе података, избацити било коју стару верзију табеле ако постоји, а затим креирати нову табелу са јасно откуцаним колонама.

Ево како тај ток изгледа концептуално у Пајтону: зовете sqlite3.connect('music.sqlite') да бисте отворили или креирали датотеку базе података, а затим позовите conn.cursor() да бисте добили курсор. Преко тог курсора можете покренути SQL команде као што су DROP TABLE IF EXISTS Songs да обришете било коју претходну шему, након чега следи CREATE TABLE Songs (title TEXT, plays INTEGER) да дефинишете нову табелу са две колоне.

Када табела постоји, прелазите са DDL-а (језик за дефинисање података) на DML (језик за манипулацију подацима) помоћу INSERT izjaveУ Пајтону би увек требало да користите параметризоване упите: write INSERT INTO Songs (title, plays) VALUES (?, ?) и проследите корку као што је ('Thunderstruck', 20) као други аргумент за execute()Знакови питања су резервисана места која ће Пајтон безбедно заменити, помажући вам да избегнете проблеме са SQL убризгавањем и цитирањем грешака.

Након уметања или ажурирања, морате позвати conn.commit() да бисте скинули измене на дискДок не извршите измене, операције се налазе само у баферу трансакција. Ово се разликује од једноставног писања у датотеке и једна је од кључних навика које треба успоставити рано: упитај, измени, па потврди измене.

Да бисте поново прочитали своје податке, користите SELECT изјава и итерација преко курсора. На пример, SELECT title, plays FROM Songs ће стримовати сваки ред као Пајтон торку, као што је ('Thunderstruck', 20)Курсор не учитава све резултате одједном; уместо тога, полако преузима редове, што је корисно када се на крају бавите већим скуповима података.

Основни елементи SQL упита и филтрирање помоћу WHERE

Сваки SQL упит је изграђен на малом скупу клаузула које се појављују стандардним редоследом: SELECT, FROM, WHERE, GROUP BY, HAVING, i ORDER BYНајмање што наводите јесте које колоне желите (SELECT) и из које табеле (FROM). Опционе клаузуле затим прецизирају, агрегирају, филтрирају агрегиране резултате и сортирају излаз.

WHERE клаузула филтрира редове пре него што се изврши било какво груписање или агрегацијаЗа нумеричке колоне можете користити операторе поређења као што су =, != (Или <>), >, <, >=, <=Текстуалне колоне подржавају ово плус подударање са обрасцима путем LIKE и провере чланства путем INВредности датума/времена подржавају иста релациона поређења и често се виде опсези изражени помоћу BETWEEN.

Руковање нултим вредностима у SQL-у је довољно необично да заслужује посебну пажњуРедовна поређења као што су = != не понашај се како би се очекивало NULL, тако да SQL пружа IS NULL IS NOT NULL да провери недостајуће вредности. Булове колоне обично раде са = !=, али ти и даље треба IS NULL када сама булова вредност може да недостаје.

Када комбинујете више услова, запамтите да AND OR пратите правила приоритетаАко пишете age < 5 OR age > 10 AND breed = 'Ragdoll', SQL ће проценити AND прво. Да бисте изразили „мачке расе регдол млађе од 5 или старије од 10 година“, требало би да користите заграде: (age < 5 OR age > 10) AND breed = 'Ragdoll'Упознавање са овим логичким комбинацијама је кључно за аналитички рад у стварном свету.

Упаривање образаца са LIKE омогућава вам претрагу низова који почињу, завршавају се или садрже одређене фрагментеЗнак процента % је џокер за било који низ знакова, тако да breed LIKE 'R%' проналази расе које почињу са словом „Р“, fav_toy LIKE 'ball%' проналази играчке чија имена почињу са „лопта“ и coloration LIKE '%m' проналази обрасце боја који се завршавају на „м“. Упарено са AND/OR, ово постаје моћан алат за филтрирање текста.

Вежбање упита над једном табелом са играчком за скуп података

Користан начин за изградњу мишићне меморије је да фиксирате малу шему у глави и решавате многа питања у складу са њом.Замислите cat табела са колонама као што су id, name, breed, coloration, age, sex, i fav_toyОво вам даје довољно разноликости – текст, бројеви, једноставне категорије – да вежбате већину основних образаца упита.

За провере типа булове, често филтрирате на основу једне колоне, а затим слојевите додатне условеДа бисте навели „досадне“ мужјаке мачке који немају забележену омиљену играчку, изабрали бисте name где sex = 'M' fav_toy IS NULLОво илуструје како се null провере упарују са једноставним поређењима да би се изоловао одређени подскуп редова.

Да бисте циљали одређене расе или их искључили, комбинујете једнакост са логичком негацијомОдабир само рагдол мачака одређене старости користи breed = 'Ragdoll'; искључујући Персијанце и Сијамце, могло би изгледати као breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese'Иако неке базе података подржавају NOT IN ('Persian', 'Siamese')Вежбање експлицитног обрасца помаже у учвршћивању вашег разумевања NOT LIKE.

Вежбе попут „женке мачке које воле играчке за задиркивање и нису персијске или сијамске“ вас терају да комбинујете текстуалне филтере, једнакост и логичке оператореИзабрали бисте id, name, breed, coloration и ограничите редове користећи sex = 'F', fav_toy = 'teaser', и сложени услов који искључује нежељене расе. Обраћање пажње на заграде осигурава да се сви подуслови примењују у предвиђеној комбинацији.

Када се савладате са овим једноставним примерима у сировом SQL-у, поново их имплементирајте преко Пајтона користећи параметризоване упите.Напишите кратке сценарије који траже расу, минималну старост или врсту играчке од input(), укључите их у WHERE клаузуле и исписати резултате. Ово је управо мост између писања упита и стварног апликацијског кода који многи млађи стручњаци за податке очекују.

Разумевање и вежбање SQL JOIN-ова

Чим превазиђете проблеме са играчкама, стално ћете се придруживати више столова.JOIN-ови су начин на који повезујете повезане скупове података: купце са поруџбинама, уметнике са уметничким делима, игре са компанијама и тако даље. У SQL-у описујете које колоне треба да се подударају између табела, а механизам базе података спаја редове у комбиновани скуп резултата.

Постоје четири основна типа придруживања које ћете срести на интервјуима и стварним пројектима: INNER JOIN (често се пише само JOIN), LEFT JOIN, RIGHT JOIN, i FULL OUTER JOINУнутрашње спајање враћа само редове где обе табеле имају подударне кључеве; лево спајање задржава све редове из леве табеле, попуњавајући NULLкада десна табела нема подударања; десно спајање ради симетричну ствар; а потпуно спољашње спајање враћа сваки ред са обе стране, подударајући се где је то могуће и користећи NULL где не.

Мислити о LEFT JOIN RIGHT JOIN као операције „више верујте овој страни“Код левог спајања, лева табела је примарни извор истине: сваки ред из ње се појављује барем једном у излазу, чак и када десна табела не доприноси ништа. Код потпуног спајања, ниједна страна није привилегована – једноставно обједињујете све кључеве из обе табеле и поравнавате их тамо где се преклапају.

Да би упити за више табела били читљиви, увек додајте псеудониме вашим табеламаУместо писања SELECT artist.name више пута, писати FROM artist AS a а затим референцирајте колоне као a.name. Слично томе, piece_of_art може постати poa, i museum могу бити mКада ваш упит нарасте на три или више спајања, добри алијаси су разлика између јасноће и хаоса.

Класична поставка тренинга користи три стола: artist, museum, i piece_of_art. artist сто би могао да стане id, name, birth_year, death_year и примарну област попут акварела или вајарства. museum продавнице столова id, name country. piece_of_art држачи за сто id, name, artist_id museum_idТе последње две колоне су страни кључеви који повезују свако уметничко дело са његовим ствараоцем и локацијом.

Са том шемом можете вежбати унутрашње спајање, лево спајање и условне филтереНа пример, да бисте навели уметнике рођене после 1800. године који су живели више од 50 година, поред имена њихових дела, придружили бисте се artist piece_of_art on artist.id = piece_of_art.artist_id а затим филтрирајте са death_year - birth_year > 50 birth_year > 1800Додај алијас изабраним колонама као artist_name piece_name за јасноћу.

Да бисте видели сва уметничка дела заједно са називима музеја и земљама – укључујући и „изгубљена“ дела која немају музеј – користили бисте LEFT JOIN od piece_of_art до museum on museum_idНа тај начин, уметничка дела без придруженог музеја и даље се појављују у резултату, са NULL у колонама музеја. Филтрирање редова где artist_id IS NULL омогућава вам да откријете дела непознатих уметника, а да се и даље придружите музејима који их чувају.

Напредније вежбе захтевају да се истовремено придружите три столаДа бисте навели свако уметничко дело и његовог уметника и назив музеја, придружили бисте се museum до piece_of_art on museum.id = piece_of_art.museum_id, затим се придружите artist on artist.id = piece_of_art.artist_idКоришћење обичног JOIN (унутрашње спајање) намерно изоставља уметничка дела којима недостаје уметник или музеј, дајући вам осећај како тип спаја утиче на број редова.

Агрегација, GROUP BY и HAVING у пракси

Када можете да преузмете и спојите податке, следећа велика вештина је њихово сумирањеФункције агрегације као што су SUM(), AVG(), COUNT(), MAX(), i MIN() израчунајте метрике над скуповима редова. GROUP BY подели ваш скуп података у групе и примени те функције унутар сваке групе – на пример, једна група годишње, по компанији или по уметнику. Ако више волите структуриране курсеве за вежбање ових концепата, погледајте свеобухватни SQL курс.

Замислите једноставан sales_table са колонама year, month, i sales. Обичан SELECT SUM(sales) AS total_sales FROM sales_table даје вам укупан збир у свим редовима. Додавање GROUP BY year мења питање: сада тражите укупну продају годишње, а не један укупан број.

Кључно правило је да свака неагрегирана колона у вашем SELECT мора се појавити у GROUP BY. Ако одаберете year SUM(sales), групишете по year. Ако одаберете year month заједно са агрегатима, затим групишете по оба year monthКонцептуално, различите комбинације груписаних колона дефинишу групе.

WHERE HAVING су оба филтера, али делују у различитим фазама. WHERE филтрира сирове редове пре него што дође до било каквог груписања или агрегације. HAVING филтрира груписане резултате користећи агрегатне изразе. На пример, могли бисте WHERE production_year BETWEEN 2000 AND 2009 и онда HAVING SUM(revenue) > 4000000 да задржи само компаније чије су „добре игре“ генерисале више од четири милиона прихода.

Реалистичнија шема праксе је games табела са колонама попут id, title, company, type, production_year, system, production_cost, revenue, i ratingПомоћу ове јединствене табеле можете да вежбате просеке, бројање, сумирање, груписање и рангирање – основу аналитичког SQL-а.

На пример, да би се израчунала просечна цена производње игара објављених од 2010. до 2015. године са оценом већом од 7, изабрали бисте AVG(production_cost) и ограничи редове са WHERE production_year BETWEEN 2010 AND 2015 AND rating > 7То је класично питање у стилу интервјуа и можете га лако уградити у Пајтон и одштампати резултујући један број.

Такође можете директно израдити статистику на годишњем нивоу из истог games табелаГрупиши по production_year, затим израчунајте COUNT(*) AS count, AVG(production_cost) AS avg_cost, i AVG(revenue) AS avg_revenueОва врста упита вам даје компактан приказ временских серија који је изузетно чест у BI контролним таблама и алатима за извештавање.

Да бисте рангирали компаније по бруто добити током свих година, можете их агрегирати на companyПрактичан образац је SELECT company, SUM(revenue - production_cost) AS gross_profit_sum FROM games GROUP BY 1 ORDER BY 2 DESC. Ево GROUP BY 1 ORDER BY 2 користите позиције колона у SELECT листа, која може да одржи ствари сажетим, али се мора користити пажљиво како не бисте прекинули упите каснијим преуређивањем колона.

Сложенији упити повезују филтере, груписање и филтере након агрегацијеПретпоставимо да дефинишете „добре игре“ као оне произведене између 2000. и 2009. године, са оценом изнад 6 и приходом већим од трошкова производње. За сваку компанију желите број таквих игара плус њихов укупан приход, али само за компаније чији приход од добрих игара прелази 4,000,000. Филтрирали бисте редове са WHERE on production_year, rating, и профитабилност, груписати по company, израчунати COUNT(company) SUM(revenue), затим примените HAVING SUM(revenue) > 4000000Овај један упит обухвата већину менталних корака из стварног света са којима ћете се сусрести у аналитичким задацима.

Моделирање података помоћу више табела и кључева

Дизајн са једном табелом вас доводи прилично далеко, али релационе базе података су најбоље када нормализујете податке у више табела.Нормализација је процес елиминисања сувишне меморије и представљања односа путем кључева. То чини вашу базу података мањом, бржом и мање склоном грешкама.

Једноставан, али поучан пример долази из прегледа друштвених графикона сличних ТвитеруРецимо да желите да пратите корисничке налоге и односе „пратилаца“ између њих. Један наиван приступ би био једна табела где сваки ред дуплира имена и пратилаца и пратилаца као текст. То брзо доводи до прекомерног понављања и недоследног правописа.

Уместо тога, поделили сте ствари на People сто и а Follows табела. People може имати цео број id као примарни кључ, јединствени name (име на екрану или корисничко име) и retrieved заставица која сигнализира да ли сте већ прегледали листу пријатеља тог налога. Follows садржи парове целих бројева from_id to_id, што представља усмерене везе од једног корисника до другог.

Три кључна концепта структурирају овај модел: логички кључеви, примарни кључеви и страни кључевиЛогички кључ је оно што спољашњи свет користи да би се позовео на запис – овде, Твитер идентификатор у nameПримарни кључ је обично цео број генерисан од стране базе података (id) који јединствено идентификује сваки ред и јефтин је за индексирање и поређење. Спољни кључ је цео број који указује на примарни кључ у другој табели – from_id to_id у Follows табела су страни кључеви који референцирају People.id.

Да бисте обезбедили квалитет података, декларишете ограничења у дефиницијама табела. На пример, name TEXT UNIQUE in People осигурава да не можете случајно уметнути два реда са истим ручком. А UNIQUE(from_id, to_id) ограничење у Follows спречава вас да сачувате исту пратећу ивицу више пута. Ова ограничења служе и као сигурносне мреже када почнете да пишете логику за upsert у Пајтону.

У Пајтону sqlite3 модул, уобичајени образац је коришћење INSERT OR IGNORE да поштује та ограничења достојанственоАко покушате да убаците name који већ постоји, SQLite ће тихо прескочити операцију уместо да избаци грешку. Затим можете проверити cursor.rowcount да бисте видели да ли је ред заиста додат и ослонили се на cursor.lastrowid да открије додељено id за новоукључене кориснике.

Када ваш код добије ново корисничко име, прво би требало да покуша да пронађе одговарајуће id. Ако SELECT id FROM People WHERE name = ? враћа ред, поново користите тај цео број. Ако не, уносите име са retrieved = 0, потврди, а затим прочитај lastrowidТај образац „пронађи или убаци“ је у сржи многих скрипти за пријем података.

Када су познати и ИД-ови пратиоца и пратиоца, бележи се однос у Follows је само још један INSERT OR IGNORE. Твоје UNIQUE(from_id, to_id) Ограничење се бави дупликатима, а ви можете да се концентришете на логику вишег нивоа о томе које профиле треба следеће пописати, уместо на микроуправљање дедупликацијом редова.

Коришћење JOIN-а за реконструкцију односа из нормализованих табела

Нормализоване шеме замењују редундантност индиректношћу: чувате целе бројеве уместо поновљених низова, али сада морате да спојите табеле да бисте реконструисали целу слику.То је управо оно што SQL JOIN је дизајниран за, и када се навикнете на то, упити који користе много JOIN-а делују потпуно природно.

У примеру друштвеног графикона, ако желите да видите са којим корисником id = 2 прати, придружили бисте се Follows до People на страни циља. Концептуално, покрећете SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2Ово производи комбиноване редове који садрже и нумеричку ивицу и име које људи могу да читају за сваког пратиоца.

Сваки ред у том резултату је „мета-ред“ који спаја колоне из обе табелеПрве две колоне би могле бити (from_id, to_id) od Follows, док наредне колоне припадају People - као (id, name, retrieved). Због JOIN услов спроводи Follows.to_id = People.id, можете експлицитно видети ту везу: друга и трећа колона сваког реда се подударају.

Овај исти образац се природно протеже на више столоваВећ си то видео са artist, piece_of_art, i museum, а Твитер претраживач то илуструје са People FollowsУ сложенијим аналитичким процесима, можете спојити табеле чињеница (догађаји, поруџбине) са вишеструким табелама димензија (корисници, производи, кампање) како бисте одговорили на вишестрана питања.

Када отклањате грешке у коду или учите како се шема уклапа, ток рада „покрените Пајтон, а затим проверите помоћу прегледача базе података за SQLite“ је изузетно ефикасан.Извршите скрипту да бисте попунили базу података, затворите све инстанце графичког корисничког интерфејса које држе датотеку закључаном, а затим отворите .sqlite датотеку у прегледачу. Одатле можете прегледати садржај сваке табеле и покренути ад-хок SELECT питања како бисте потврдили своје претпоставке.

Једно упозорење: SQLite намеће закључавање датотека, тако да ако прегледач базе података има отворену базу података у режиму уређивања, ваш Пајтон скрипт можда неће успети да се повеже или потврди измене.Решење је да затворите базу података у графичком корисничком интерфејсу (или потпуно затворите прегледач) пре него што поново покренете свој Пајтон код. Стицање навике затварања алата који закључавају вашу датотеку базе података спасиће вас од мистериозних грешака „база података је закључана“.

Спајање ових техника – дизајн шеме, ограничења, параметризовани упити у Пајтону, JOIN-ови, GROUP BY и HAVING – даје вам моћну локалну лабораторију за вежбање тачно оне врсте SQL и Python посла коју ћете радити на послу. Са ничим више од SQLite-а и неколико добро структурираних примерних табела, можете увежбати питања у стилу интервјуа, направити прототип аналитичке логике и повратити самопоуздање уз помоћ модерних алата за обраду података.

Где се платформе попут DataLemur-а и интерактивни курсеви уклапају

Поред ваше локалне праксе, интерактивне платформе вам могу пружити вођеније искуство са тренутним повратним информацијама.Алати настали из искуства у стварном свету – на пример, платформе које су креирали бивши инжењери података из Фејсбука и Гугла који су дане проводили пишући SQL и Пајтон и спроводећи A/B тестове – често свој садржај усмеравају на стварна питања за интервју и аналитичке сценарије.

Књиге које покривају статистику, машинско учење и пословну интуицију за интервјуе са подацима су одличне за теорију, али они не пружају увек практично SQL игралиште које многи ученици желе. Та празнина је управо оно што неки модерни алати желе да попуне: они препакују стотине упита у стилу интервјуа у SQL и аналитичко окружење у прегледачу тако да можете покретати, подешавати и поново покретати своје упите без бриге о локалном подешавању. Такође можете испробати примењене примере као што су процена ризика од одлива купаца да комбинује SQL са основним токовима рада машинског учења.

Такође ћете пронаћи интерактивне SQL курсеве који одражавају теме које смо овде обрадилиупити са једном табелом са SELECT WHERE, спајања две или три табеле, агрегација и груписање, подупити и још много тога. Многи од ових курсева се ослањају на реалистичне скупове података – замислите игре, музеје или трансакциону продају – тако да питања делују као прави пословни проблеми, а не као измишљене загонетке.

Ако се осећате преоптерећено документацијом за алате попут PySpark-а, DuckDB-а или dbt-а, сасвим је разумно да их одложите док вам основе SQL-а не буду чврсте.Фокусирање прво на SQLite плус Python вам омогућава да интернализујете основне обрасце упита без борбе са конфигурацијом кластера или дозволама у облаку. Када основе постану друга природа, учење PySpark-а постаје више везано за дистрибуирано извршавање него за нове концепте упита.

На крају крајева, комбинација једноставног локалног подешавања, структурираних вежбовних задатака и повремене употребе интерактивних платформи даје вам најбоље од свих светова: потпуну контролу над вашим окружењем, чврсто концептуално утемељење и изложеност стилу питања које врхунски послодавци воле. Уз сталну праксу, некада застрашујућа мешавина SQL-а, Python-а и алата за инжењеринг података постаје познат, чак и пријатан, сет алата који можете са сигурношћу користити у новим улогама.

Када све то саберете, ваш пут напред је јасан: покрените SQLite базу података помоћу Пајтона, дизајнирајте неколико реалистичних табела, увежбајте основне и средње SQL обрасце (филтери, спајања, агрегација, груписање, HAVING), обмотајте те упите у Пајтон скрипте и опционо допуните своје учење интерактивним SQL платформама које су изградили практичари који су били управо тамо где сте ви сада.На тај начин ћете обновити своје техничке инстинкте, смањити анксиозност око модерних стекова података и бити спремни да се носите са SQL и Python захтевима данашњих улога у вези са подацима.