Share
Embed
SQL Meisterへの道 ~基礎〜参照編~ / sql-meister-R
Biz職もエンジニアリングやっていきの流れができたので、社内勉強会用に書いた。
machu
November 02, 2020
Transcript
59 slides
-
࣍ - SQL͍͍ͧ - ༻ޠղઆͱجຊͷཱͪճΓ - SQL(ΫΤϦ)ͬͯͳΜͧ - ςʔϒϧͬͯͳΜͧ -
σʔλϕʔεͬͯͳΜͧ - ڞ௨Ͱ͑ΔWHERE۟Λ֮͑Α͏ - SELECTจͰσʔλΛݟͯΈΑ͏ - SELECTจͷจ๏ - ࣮ߦ - ΫΤϦ࣮श(redashͷ͍ํ) - ΫΤϦ࣮श(SELECT&WHERE) - ू߹ͷײ֮Λ௫Ή - JOIN͕Ͱ͖Ε͍ͬͪΐલ - ΫΤϦ࣮श(JOIN) - ूܭΫΤϦ ~ूܭؔ~ - ूܭΫΤϦ ~group by~ - ΫΤϦ࣮श(ूܭΫΤϦ) - αϒΫΤϦ - ΫΤϦ࣮श(αϒΫΤϦ) -
σʔλϕʔε(DB)ͬͯͳΜͧ σʔλϕʔε(DB)ͱ͍͏ͷɺςʔϒϧΛ· ͱΊͨͷͰ͢ɻ DBαʔόʔͷ͜ͱΛཧDBɺ͜ͷςʔϒϧ ͷ·ͱ·ΓΛཧDBͱݺΜͰ۠ผͨ͠Γ͠ ·͢ɻ `show databases` ͰDBͷҰཡΛ֬ೝͰ͖ ·͢ɻ
infomation_schemeは勝手に作られる あんまり気にしないでいいやつで、 alp_subscription_sandboxが我々の定義したテーブルが 入ってるDBです -
WHERE۟ͬͯͳΜͧ WHERE۟Ͱɺ શҰகɺ෦ҰகɺϦετҰக(ϦετͷͲΕ͔ʹશҰக)ͷ݅ ͕͑·͢ɻ શҰக `${ΧϥϜ໊} = “݅"` ෦Ұக`${ΧϥϜ໊} LIKE
“%݅%”` ϦετҰக `${ΧϥϜ໊}` IN (“݅1”,“݅2”,“݅3”) ͱ͍͏Α͏ʹࢦఆͰ͖·͢ɻ ͜ΕΒͰSELECTͳΒࢀর͢ΔରɺINSERT,UPDATE,DELETEͳΒߋ৽͢Δ ରΛߜΓ·͢ɻ 条件をまとめないと結果が変わる部分は()でまとめます 掛け算割り算と同じノリです! ※しれっとLIMITを使ってますが、これは件数を制限するやつです! データ量多すぎてうっとおしい時は件数を絞りましょう -
WHERE۟ͬͯͳΜͧ ઌఔͷશҰகɺ෦ҰகɺϦετҰக(ϦετͷͲΕ͔ʹશҰக)ͷ݅ɺ൱ఆ ͷ݅ʹ͑·͢ɻ શҰகͷ൱ఆ `${ΧϥϜ໊} != “݅”` ෦Ұகͷ൱ఆ`${ΧϥϜ໊} NOT LIKE
“%݅%”` ϦετҰகͷ൱ఆ `${ΧϥϜ໊}` NOT IN (“݅1”,“݅2”,“݅3”) ͱ͍͏Α͏ʹࢦఆͰ͖·͢ɻ -
ҙ ΊͪΌͪ͘Ό͕݅ଟ͔ͬͨΓɺΧϥϜʹͰ͔͍σʔλͷೖͬͨςʔϒϧʹΏΔ͍݅ͷΫΤϦΛ࣮ߦ͠ ͯ͠·͏ͱɺ݁ՌΛฦ͢ͷʹ͕͔͔࣌ؒͬͨΓෛՙΛ͔͚ͯ͠·͍·͢ɻ ͦΜͳͱ͖ɺͱΓ͋͑ͣ݅ΛߜΔ limitͩͬͨΓɺऔಘ͢ΔΧϥϜΛ໌ࣔ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ SELECT id,name -> ࢀর͢ΔΧϥϜ໊Λࢦఆ FROM
${ςʔϒϧ໊} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ WHERE ${݅} -> ݅Λࢦఆ LIMIT 10 -> 10͚݅ͩऔಘɻWHEREͳ͠ͰɺFROMͷޙΖʹࢦఆ͢Δ͜ͱͰ͖Δ -
ΫΤϦ࣮शᶃ(SELECT & WHERE) Addressςʔϒϧ͔ΒɺҎԼͷ݅ͰσʔλΛऔಘ͠·͠ΐ͏ɻ - prefecture͕ “TOKYO”(=) ͔ͭ(and) - address_line1ʹ”3ஸ”ؚ͕·ΕΔ(like)
͔ͭ(and) - ( city͕”ौ୩۠”(=) ͘͠(or) தԝ۠(=) ) -
WHERE۟ͱON۟෦ू߹ΛऔΓग़͢Έ WHERE句やON句で写像を作る。 この写像は参照元の部分集合になる。 もちろん、空集合になることもある。 この矢印がSELECTだとすると、結果Aは テーブルBの部分集合となります。 こう考えると、WHERE句を指定しないと 全データが更新されるイメージもつくかも? テーブル ※超厳密に言うと、カラムはSELECT節で自己定義
できるので部分集合でないパターンもありますが、 WHEREはざっくりこういうイメージということで🙏 ※ON۟JOINʹ͏ߏจͰ͜ͷޙग़͖ͯ·͢ɻWHEREͱಉ݅͡ࢦఆ͕Ͱ͖ΔͭͰɺ JOINͷ݅ΛࢦఆͰ͖·͢ɻ -
INNER JOINจͷจ๏ SELECT * -> ࢀর͢ΔΧϥϜ໊Λࢦఆɻجຊશ෦Λҙຯ͢Δ`*`ͰOK FROM ${ςʔϒϧ໊A} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ
INNER JOIN ${ςʔϒϧ໊B} -> JOIN͍ͨ͠ςʔϒϧ໊Λࢦఆ ON ${݅} -> WHERE۟ͱಉ͡ॻ͖ํͰ݅Λࢦఆ͢Δɻ͜͜ͷ݅ݫ͍͠΄͏͕ JOIN͢Δର͕গͳ͘ͳΔͷͰύϑΥʔϚϯεྑ͘ͳΔɻ WHERE ${݅} -> ݅Λࢦఆ -
LEFT JOINจͷจ๏ SELECT * -> ࢀর͢ΔΧϥϜ໊Λࢦఆɻجຊશ෦Λҙຯ͢Δ`*`ͰOK FROM ${ςʔϒϧ໊A} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ
LEFT JOIN ${ςʔϒϧ໊B} -> JOIN͍ͨ͠ςʔϒϧ໊Λࢦఆ ON ${݅} -> WHERE۟ͱಉ͡ॻ͖ํͰ݅Λࢦఆ͜͜ͷ݅ݫ͍͠΄͏͕JOIN͢ Δର͕গͳ͘ͳΔͷͰύϑΥʔϚϯεྑ͘ͳΔɻ WHERE ${݅} -> ݅Λࢦఆ INNER JOINとほぼ一緒だぜ! -
SELECTWHERE߲ʹΧϥϜ͚ͩͰͳ͘ɺܭࢉΛͯ͘͠ΕΔؔΛࢦఆ͢Δ͜ͱ͕Ͱ͖·͢ɻ Α͘͏ͷ5ͭ => SUM,AVG,MAX,MIN,COUNT ͜ΕΒޙड़ͷGROUP BYͱΈ߹Θͤͯ͏͜ͱͰޮྗΛൃش͠·͢ɻ ※👇ͷΑ͏ʹ୯ମͰ͑·͢͠·͢ɻ ूܭؔͬͯʁ // customerςʔϒϧͷ૯݅Λग़͢
> SELECT COUNT(*) FROM customer; // ͋Δproviderͷcustomerͷ૯ΛͱΔ > SELECT COUNT(*) FROM customer WHERE provider_id = "aaa"; -
GROUP BYͬͯʁ GROUP BYɺࢦఆͨ͠ΧϥϜͰ݁ՌΛ·ͱΊͯ͘Ε·͢ɻ ઌఔͷ👇ͷूܭΫΤϦ1ͭͷϓϩόΠμͷquantity߹ܭΛूܭ͍ͯ͠·͢ɻ GROUP BYϓϩόΠμຖͷquantityͷ߹ܭΛશͯݟ͍ͨ߹ʹ͑·͢ɻ // ͋Δproviderͷcustomerͷ૯ΛͱΔ >
SELECT COUNT(*) FROM customer WHERE provider_id = "aaa"; // ܖຖͷbilling_itemͷquantity૯ΛͱΔ > SELECT provider_id, COUNT(*) FROM customer GROUP BY provider_id; -
GROUP BYͬͯʁ GROUP BYͰɺෳͷΧϥϜΛࢦఆ͢Δ͜ͱͰ͖·͢ɻ // contract,contract_charge_item_idຖͷbilling_itemͷquantity૯ΛͱΔ > SELECT contract_id, contract_charge_item_id,
sum(quantity) FROM billing_item GROUP BY contract_id,contract_charge_item_id; -
Πϝʔδਤ GROUP BYͰɺෳͷΧϥϜΛࢦఆ͢Δ͜ͱͰ͖·͢ɻ // contract,contract_charge_item_idຖͷbilling_itemͷquantity૯ΛͱΔ > SELECT contract_id, contract_charge_item_id, sum(quantity)
FROM billing_item GROUP BY contract_id,contract_charge_item_id; -
ෳࢦఆ͢Δͱ͖σʔλͷߏΛҙࣝ͢Δ ෳࢦఆ͢Δࡍσʔλߏతʹҙຯͷ͋ΔͷͰ͋Δ͔Λҙࣝ͢Δඞཁ ͕͋Γ·͢ɻ 1ϖʔδલͷΫΤϦ݁ՌͷߏΛ1ܖநग़͢Δͱ👇ͷΑ͏ʹͳΓ·͢ɻ group by contract_id とすると、quantityは5になる group by
contract_id,contract_charge_item とすると、 quantityは2,2,1とcontract_charge_itemでもgroup化される -
ΫΤϦ࣮श(ूܭΫΤϦ) billingςʔϒϧ͔ΒɺϓϩόΠμ͝ͱͷtotal_billing_priceͷ߹ܭΛग़ͯ͠Έ·͠ΐ͏ ৄࡉͳ݅ҎԼ👇 - total_billing_unit͕”YEN” - provider_idͰgroupԽ - total_billing_priceͷ߹ܭ(SUM)Λूܭ -
දࣔ͢Δ߲provider_id, total_billing_priceͷ߹ܭ -
αϒΫΤϦ ςʔϒϧʮςʔϒϧఆٛʹैͬͨσʔλͷू߹ʯɺΫΤϦͷ݁Ռʮ෦ू߹ʯͱͯ͠ଊ͑ΒΕΔલड़͠·ͨ͠ɻ αϒΫΤϦɺʮ෦ू߹ʯΛΫΤϦʹΈࠐΉ͜ͱͰ͢ɻ SELECTจʹ͓͚ΔཁૉΛͬ͘͟Γઆ໌͢Δͱ👇ͷΑ͏ʹͳΓ·͢ɻ ${ू߹} ͱॻ͍ͯ͋Δ෦ී௨ʹॻ͘ͱςʔϒϧ͕ೖΓ·͕͢ɺΫΤϦͰ࡞ͬͨʮ෦ू߹ʯΛͯΊΔ͜ͱ Ͱ͖·͢ɻ ͜Ε͕αϒΫΤϦͰ͢ɻ SELECT ${ू߹ͷཁૉ}
FROM ${ू߹} XXX JOIN ${ू߹} ON ${ू߹ͷཁૉʹΑΔ݅} WHERE ${ू߹ͷཁૉʹΑΔ݅} -
Ͳ͏ॻ͘ͷʁ ${ू߹} ͷ෦Λ ()ͰғͬͨSELECTจʹஔ͖͑·͢ɻ ͜Μͳײ͡Ͱॻ͖·͢ɻ(͍͜͢͠Ͷʘ(^o^)ʗ) SELECT ${ू߹ͷཁૉ} FROM ( SELECT
* FROM ${ू߹} WHERE aa=“xx” ) XXX JOIN ( SELECT * FROM ${ू߹} WHERE aa=“xx” ) ON ${ू߹ͷཁૉʹΑΔ݅} WHERE ${ू߹ͷཁૉʹΑΔ݅} -
ΩϨΠʹॻ͚Δwith ※MySQLͰɺ5.8͔ΒͷػೳͳͷͰScalebaseͰ·ͩ͑ͳ͍ͷͰ͕͢ɺαϒΫΤϦͷཧղʹͨͪͦ͏ͳͷͰҰԠࡌͤ·͢ɻ αϒΫΤϦɺ෦ू߹ʹ໊લΛ͚ͭͯςʔϒϧͷ༻ʹѻ͏ख๏Ͱ͢ɻ ͜ΕΛΘ͔Γ͘͢ॻ͚Δwith۟ͱ͍͏ͷ͕͋Γ·͢ɻ ཁઌʹ෦ू߹Λ࡞໊ͬͯલΛ͚ͭɺͦͷ໊લΛΫΤϦͰར༻͍ͯ͠Δ͚ͩͰ͕͢ɺೖΓΜͩߏ͕ղফͰ͖ΔͷͰΧφϦ ಡΈ͘͢ͳΓ·͢ɻ WITH tableA as (
SELECT * FROM ${ू߹} WHERE aa=“xx” ), tableB as ( SELECT * FROM ${ू߹} WHERE aa=“xx” ) SELECT ${ू߹ͷཁૉ} FROM tableA XXX JOIN tableB ON ${ू߹ͷཁૉʹΑΔ݅} WHERE ${ू߹ͷཁૉʹΑΔ݅} -
ͳΜʹ͏ͷʁ ςʔϒϧ͚ͩΛࡐྉʹΫΤϦΛΈཱͯΔͱɺͲ͏ͯ͠ूܭͷ୯Ґ͕߹Θͳ͘ͳ͍͖ͬͯ·͢ɻ ྫ͑ɺcontractͷςʔϒϧ͔Βʮࠓ։࢝ͨ͠ܖʯͱɺʮࠓ݄։࢝ͨ͠ܖʯΛऔಘ͍ͨ͠ͱ͠·͢ɻ αϒΫΤϦͳ͠Ͱී௨ʹॻ͘ͱɺ୯Ґ͕ҧ͏ͷͰ1ͷΫΤϦͰऔಘ͢Δ͜ͱͰ͖·ͤΜɻ αϒΫΤϦͩͱɺ👇͜Μͳײ͡Ͱॻ͘ͱҰʹͱΕ·͢ɻ جຊతʹɺҰʹऔಘ͢Δඞཁ͕͋ΔͷੳΫΤϦͱ͔ͳͷͰɺௐࠪঢ়گ֬ೝͰී௨ʹผʑͷΫΤϦͰ2ճʹ͚ͯऔΔ΄͏͕ྑ͍Ͱ͢ɻ SELECT y.provider_id, m.this_month_begin_num, y.this_year_begin_num
FROM ( SELECT provider_id, COUNT(*) as this_year_begin_num FROM contract WHERE DATE_FORMAT(contract_begin_date, “%Y”) = DATE_FORMAT(now(), “%Y”) ) m LEFT JOIN ( SELECT provider_id, COUNT(*) as this_month_begin_num FROM contract WHERE DATE_FORMAT(contract_begin_date, “%Y-%M”) = DATE_FORMAT(now(), “%Y-%M”) ) y ON m.provider_id = y.provider_id -
ΫΤϦ࣮श(αϒΫΤϦ) billingςʔϒϧ͔ΒɺҎԼͷ݅👇ͰσʔλΛऔಘ͍ͯͩ͘͠͞ - billing_date͕ࠓ݄ʹͳ͍ͬͯΔσʔλͷtotal_billing_priceͷ߹ܭ - billing_date͕ࠓʹͳ͍ͬͯΔσʔλͷtotal_billing_priceͷ߹ܭ ※👇͕ ࠓ݄ͷbilling_dateΛऔಘ͢Δ݅ DATE_FORMAT(billing_date, “%Y-%M”)
= DATE_FORMAT(now(), “%Y-%M”) ※👇͕ ࠓͷbilling_dateΛऔಘ͢Δ݅ DATE_FORMAT(billing_date, “%Y”) = DATE_FORMAT(now(), “%Y”)