SQL to dplyr

အရင်ကတော့ Software Development မှာ အသုံးပြုကြတဲ့ Programming Language တွေသာ ကွာခြားလိမ့်မယ် Database ကတော့ ဘာပဲသုံးသုံး Query က SQL ဆိုတော့ Ad Hoc အနေနဲ့ Report တွေတို့ Analytics Reports တွေတို့ ကိုယ်ထုတ်ချင်ရင် ပရိုဂရမ်မာတွေကို ထုတ်ခိုင်းလေ့မရှိဘူး။ သူတို့ထုတ်လာရင် ကိုယ်လိုချင်တာနဲ့ ကိုက်ပါ့မလား မှန်လားမမှန်လား ပြန်စစ်ရတာ ပိုအချိန်ကြာတာရယ် ကိုယ့်ဟာကိုယ်ရေးရင် ဘယ်လောက်မှ မရေးရဘူးဆိုတော့ ကိုယ်တိုင်လည်းလုပ်တယ်။ နောက်ပိုင်း Relational Database တွေသုံးတာ နည်းနည်းလာတော့ ကိုယ်တိုင်ဝင်ပြီး အလွယ်တကူ ဝင်ပြီးတော့ Query လုပ်ကြည့်လို့ မရတော့ဘူး။ ကိုယ်တိုင်ကလည်း ပရိုဂရမ်မာဆိုတော့ API တွေနဲ့ Loop တွေပတ်ပြီး Analytics Report လုပ်ရမယ်ဆိုရင် ကိုယ်ဟာကိုယ်တောင် မယုံတော့ခက်တယ်။

မယုံရဲတဲ့လူတွေကိုပဲ အားကိုးပြီးတော့ လုပ်ခိုင်းတယ်ဆိုပေမယ့် မှန်လားမမှန်လား စစ်ဖို့အတွက်ကျ ဘဏ်မှာစာရင်းစစ်သလို ဂဏန်းတွက်စက်တလုံးနဲ့ ထိုင်စစ်လို့မရတော့ ကိုယ့်မှာလည်း Tools တွေလိုပါတယ်။ စစ်ဖို့အတွက် CSV လို Format တခုခုနဲ့ Export လုပ်ခိုင်းပြီး ကိုယ့်နည်းကိုယ်ဟန်နဲ့ ပြန်စစ်ကြည့်ရတယ်။ လွန်ခဲ့တဲ့ ၄-၅ နှစ်ကစပြီး R ကိုသုံးဖြစ်တယ် Data Analysis တွေအတွက် အဆင်ပြေပေမယ့် Data Manipulation တွေမှာကျ R ရဲ့ Built-in Function တွေသုံးရတာ အလုပ်မတွင်တဲ့ အတွက် Third Party Package တမျိုးသုံးကြည့်လိုက် Function တွေပြောင်းမှတ်နဲ့ လုံးလည်လိုက်တယ်။ ကိုယ့်ဟာကိုယ် လေ့လာလို့ခက်တယ် ထင်ပါတယ်ဆိုပြီး အတန်းသွားတက်လိုက်ရင် အတန်းတတန်းကို Package တမျိုးသုံးတာမြင်ပြီး ရူးချင်တောင်ရူးနိုင်ပါတယ်။ ကြာလာတော့ စိတ်မရှည်တဲ့အခါ Relational Database တခုခုထဲ Import လုပ်ပြီး သုံးလိုက်တာရှိတယ်။

ကိုယ့်လိုဒုက္ခတွေက ကိုယ့်လိုမျိုးတူတဲ့ တခြားလူတွေလည်း အနည်းနဲ့အများရှိကြတော့ အခုနောက်ပိုင်းမှာ အများသဘောကျတဲ့ Packages တွေကို စုပေါင်းပြီးတော့ tidyverse ဆိုပြီး ထွက်လာတော့မှပဲ R ကိုသုံးတဲ့ Data Analyst တွေရဲ့ဘဝလည်း ကောင်းနိုးရာရာ Package ပေါင်းစုံရှာဖွေရေး မလုပ်ရတော့ပဲ ငြိမ်းချမ်းတော့တယ်။ tidyverse မှာပါတဲ့ dplyr နဲ့ magrittr နှစ်ခုပေါင်းဟာ Data Manipulation အတွက် အတော်အဆင်ပြေတယ်။ SQL ရေးနေကျလူတွေ စဉ်းစားလေ့ရှိတဲ့ Paradigm အတိုင်း dplyr နဲ့ ရေးလို့ရတဲ့အတွက် dplyr သုံးလိုက်တဲ့ အခုနောက်ပိုင်းတော့ Database ထဲကို Import လုပ်လိုက်ရတဲ့ နှံပြည်စုတ်ကို အမြောက်နဲ့ချိန်ရတဲ့ ဖြစ်ရပ်မျိုးတွေ ကိုယ့်မှာတော့ တဖြည်းဖြည်းနဲ့ နည်းတော့လာပါပြီ။

R က လူအသုံးနည်းတဲ့အတွက် တခြားလူတွေအတွက် သုံးမတည့်ဖို့ များပေမယ့် စိတ်ကူးပေါက်တာနဲ့ SQL နဲ့ရေးရမယ့် အလုပ်တွေကို dplyr နဲ့ magrittr သုံးရင် ဘယ်လိုရေးကြမလဲ ယှဉ်ပြီးတော့မှ ရေးကြည့်ထားတယ်။ dplyr ကို Design လုပ်ခဲ့ကြတဲ့ လူတွေက Memory ထဲက Data တွေအပြင် Database ထဲက Data တွေကို R ကနေ လှမ်းပြီးတော့ ဖတ်တဲ့အခါလည်း သုံးလို့ရဖို့အတွက် လုပ်ခဲ့ကြတဲ့အတွက် dplyr နဲ့ SQL ဟာ concepts တွေဟာ အတော်များများ ထပ်တူညီလို့ရပါတယ်။ အောက်မှာပြထားတဲ့ Table 1 မှာ တူညီတဲ့ Concept တွေကို နမူနာအနေနဲ့ ယှဉ်ပြီးပြထားတယ်။ magrittr ကတော့ dplyr မှာ pipe အနေနဲ့ တွဲသုံးလို့ရလို့ Function တခုကထွက်လာတဲ့ Results တွေကို နောက်တခုကို အလွယ်တကူ ပေးလို့ရတယ်။

Table 1: SQL and dplyr

Data အနေနဲ့ကတော့ dplyr မှာ Example သုံးလို့ရအောင်ပါလာတဲ့ Star Wars နဲ့ Storm tracks data တွေကိုပဲ သုံးပြထားတယ်။ Star Wars Data က Star Wars ထဲမှာပါတဲ့ ဇာတ်ကောင်တွေရဲ့ နာမည်၊ အသားအရောင်၊ ဆံပင်အရောင်၊ အရပ်အမောင်း၊ အလေးချိန် စသည်ဖြင့် Data တွေရှိပါတယ်။ Storm Tracks Data ကတော့ အတ္တလန်တိတ်သမုဒ္ဒရာမှာ ဖြစ်ပွားခဲ့တဲ့ ဟာရီကိန်းတွေရဲ့ နာမည်၊ နေ့စွဲ၊ အမျိုးအစား၊ လေတိုက်နှုန်း၊ လေဖိအား စသည်ဖြင့် Data တွေပါတယ်။ Data တွေရဲ့ Schema နဲ့ Metadata အသေးစိတ်ကိုတော့ သိချင်တယ်ဆိုရင် References မှာပြထားတဲ့ URLs တွေကတဆင့် သွားကြည့်ကြပါ။

ပထမဆုံးအနေနဲ့ SELECT ကို စမ်းကြည့်မယ် starwars ထဲမှာပါတဲ့ Rows တွေနဲ့ Columns တွေ အကုန်လိုချင်တယ် ဆိုကြပါစို့။ SQL မှာဆိုရင် အောက်မှာ ပြထားသလို ရေးရလိမ့်မယ်။

SQL

SELECT * 
FROM starwars;

dplyr နဲ့ဆိုရင်တော့ အကုန်လုံးလိုချင်ရင် ဘာမှရေးစရာမလိုဘူး data တွေကို သိမ်းထားတဲ့ variable နာမည်ကိုပဲ ရိုက်ထည့်လိုက်ရင်ရပြီ။

dplyr

starwars

Columns တွေ အကုန်မလိုချင်ပဲ name, height, mass ပဲ လိုချင်တယ်ဆိုရင်တော့ SQL နဲ့ဆိုရင် အောက်မှာပြထားသလို ရေးရလိမ့်မယ်။

SQL

SELECT name, height, mass 
FROM starwars;

dplyr နဲ့ဆိုရင်တော့ ပထမဆင့်အနေနဲ့ Data အားလုံးကို magrittr ရဲ့ Pipe operator %>% သုံးပြီးတဆင့်ရယ် လိုချင်တဲ့ Columns တွေကိုပဲ ပြန်ထုတ်တဲ့ အဆင့်ရယ်ဆိုပြီး ၂ ခုရေးရတယ်။

dplyr

starwars %>%
  select(name, height, mass)

ဇာတ်ကောင်တွေရဲ့ လိုချင်တဲ့ name, height, mass ကို ရပေမယ့် ဇာတ်ကောင်တွေက လူတွေရော စက်ရုပ်တွေရော ရောနေတဲ့အတွက် Rows အကုန်မလိုချင်ပဲ လူဇာတ်ကောင်တွေပဲ သိချင်တယ်ဆိုရင် SQL နဲ့ဆို အောက်မှာ ပြထားသလိုရမယ်။

SQL

SELECT name, height, mass 
FROM starwars 
WHERE species='Human';

dplyr နဲ့ဆိုရင် SQL နဲ့ ရှေ့နောက်ကွဲပေမယ့် Concept အရဆိုရင် တူတယ်ပဲပြောရမယ် အောက်မှာပြထားသလို ရေးရပါလိမ်မယ်။

dplyr

starwars %>%
  filter(species=='Human') %>%
  select(name, height, mass)

နောက်တဆင့်အနေနဲ့ လူဇာတ်ကောင်တွေမှာ အရပ်အမောင်းနဲ့ အလေးချိန်ကိုသိရင် BMI တွက်ကြည့်လို့ရတယ်။ BMI ရဲ့ Equation က \(BMI = weight(kg)/height(m)^2\) ဆိုတော့ ခက်ခက်ခဲခဲ မတွက်ရပါဘူး။

SQL

SELECT name, height, mass, mass/POWER(height/100, 2) AS bmi 
FROM starwars 
WHERE species='Human';

SQL မှာ Column တိုးတာက SELECT ထဲမှာ သင့်လျော်သလို ထည့်ရေးလို့ရသလို dplyr မှာလည်း mutate() ကိုသုံးရင်ရပါတယ်။

dplyr

starwars %>%
  filter(species=='Human') %>%
  mutate(bmi = mass/(height/100)^2) %>%
  select(name, height, mass, bmi)

ထွက်လာတဲ့ အဖြေတွေကို ကြည့်လိုက်ရင် Darth Vader ကြီးဟာ BMI 33.3 ဆိုတော့ အဝလွန်နေတာ တွေ့ရပါတယ်။ သိပ်မသေချာတာက Darth Vader ရဲ့ Weight မှာ အစားထိုးထားရတဲ့ ခြေလက်တုတွေ ပါလားမပါလားတော့ Document ထဲမှာလည်း မပါတဲ့အတွက် တရားသေအနေနဲ့ ကောက်ချက်ချလို့ မရပါဘူး။

Star Wars ကို ခဏနားပြီးတော့ ဟာရီကိန်း Data တွေနဲ့ Aggregations တွေကို စမ်းကြည့်ကြဦးမယ်။ ဟာရီကိန်း Data မှာ year, wind, pressure ဆိုပြီး Columns တွေပါတယ် အဲဒါတွေကို အခြေခံပြီးတော့ နှစ်လိုက် မုန်တိုင်း ဘယ်နှစ်ခါဖြစ်ခဲ့တယ် လေတိုက်နှုန်းနဲ့ လေဖိအား ပျမ်းမျှကို တွက်မယ်ဆိုရင် အောက်မှာပြထားသလို SQL နဲ့ dplyr အတွက် ရေးလို့ရပါတယ်။

SQL

SELECT year, avg(pressure) AS pressure_avg, avg(wind) AS wind_avg, count(*) AS total 
FROM storms 
GROUP BY year;

dplyr

storms %>% 
  group_by(year) %>% 
  summarise(pressure_avg=mean(pressure), wind_avg=mean(wind), total=n())

ဟာရီကိန်းမုန်တိုင်း အဖြစ်အများဆုံး ခုနှစ်ကို သိချင်တယ်ဆိုရင်တော့ အပေါ်ကထွက်လာတဲ့ results တွေကို total နဲ့ ကြီးစဉ်ငယ်လိုက် စီကြည့်ရင် အလွယ်တကူသိတယ်။

SQL

SELECT year, avg(pressure) AS pressure_avg, avg(wind) AS wind_avg, count(*) AS total 
FROM storms 
GROUP BY year 
ORDER BY total DESC;

dplyr

storms %>% 
  group_by(year) %>% 
  summarise(pressure_avg=mean(pressure), wind_avg=mean(wind), total=n()) %>%
  arrange(desc(total))

ဒီလောက် အစပျိုးပြီးပြီဆိုရင် စိတ်ဝင်စားတဲ့လူတွေ ဟာရီကိန်းအဖြစ်အများဆုံး ၁၀ နှစ်တို့ လေတိုက်နှုန်းအမြင့်ဆုံးက ဘယ်နှစ်မှာဆိုရင် ဘယ်လပိုင်းတွေလဲ Star Wars ထဲမှာ လူဇာတ်ကောင်နဲ့ တခြားဇာတ်ကောင်တွေ အချိုးအစား ဘယ်လောက်ကွာလဲ စသည်ဖြင့် စိတ်ကူးပေါက်ရာတွေ လက်တည့်စမ်းလို့ ရလောက်ပါပြီ။

dplyr မှာ magrittr ရဲ့ Pipe operator %>% ကြောင့် select ပြီးမှ filter လာရမယ် စသည်ဖြင့် ကန့်သတ်ချက်တွေမရှိပဲ SQL မှာလို Subquery တွေ မလိုအပ်ပဲ ရေးစရာမလိုဘူး။ Query Language မဟုတ်ပဲ Programming Language ဖြစ်နေတော့ တန်းတူယှဉ်ကြည့်လို့တော့ မသင့်လျော်ဘူးပေါ့။ စမ်းသုံးကြည့်လို့ စိတ်ဝင်စားသွားရင် ဆက်ပြီးတော့ dplyr ရဲ့ Documents မှာဖြစ်ဖြစ် R for Data Science ရဲ့ Data Transformation Chapter မှာဖြစ်ဖြစ် ဖတ်ဖို့လိုပါလိမ့်မယ်။

မှတ်ချက်။ ။ SQL Statemens တွေက စိတ်ကူးထဲပေါ်လာတဲ့ Concept ကို ချရေးထားတာဖြစ်ပြီး Data တွေကို Database တခုခုထဲကို ထည့်စမ်းကြည့်ဖို့ ပျင်းတဲ့အတွက် စမ်းမထားမိတာကြောင့် Syntax အမှားပါနိုင်ပါတယ်။

References

1. dplyr - https://dplyr.tidyverse.org
2. R for Data Science - https://r4ds.had.co.nz/transform.html
3. Starwars characters - https://dplyr.tidyverse.org/reference/starwars.html
4. Storm tracks data - https://dplyr.tidyverse.org/reference/storms.html