ウィンドウ関数について

ウィンドウ関数（SQL）について解説

Transcript

1. ΢Οϯυ΢ؔ਺ʹ͍ͭͯ
2. ΢Οϯυ΢ؔ਺ͱ͸ • ෼ੳʹศརͳSQLͷؔ਺ • ू໿ΛߦΘͣूܭ͕Ͱ͖Δ 
 ʢूܭͱσʔλऔಘΛ·ͱΊͯߦ͑Δʣ • લޙͷϨίʔυΛൺֱ͢Δ͜ͱ͕Ͱ͖Δ 
 ʢҠಈฏۉΛग़͢͜ͱ͕Ͱ͖Δʣ
3. ׆༻ྫᶃ σʔλͷू໿ΛߦΘͣूܭͰ͖Δ ஫จ೔ ϝχϡʔ Ձ֨ ஫จऀ  ϥʔϝϯ  τϜ  ϥʔϝϯ  ϚΠΫ  νϟʔϋϯ  τϜ  νϟʔϋϯ  δΣγʔ  ϥʔϝϯ  ϚΠΫ  ϥʔϝϯ  ϚΠΫ ϝχϡʔ ϝχϡʔຖͷ
   ஫จ਺ ஫จ೔ Ձ֨ ஫จऀ νϟʔϋϯ    τϜ νϟʔϋϯ    δΣγʔ ϥʔϝϯ    τϜ ϥʔϝϯ    ϚΠΫ ϥʔϝϯ    ϚΠΫ ϥʔϝϯ    ϚΠΫ ΢Οϯυ΢ؔ਺
   ࢖͏ͱ
4. GROUP BYͰूܭͨ͠৔߹ ϝχϡʔ ϝχϡʔຖͷ஫จ਺ ϥʔϝϯ  νϟʔϋϯ  SELECT item_name as ϝχϡʔ, count(item_name) AS ϝχϡʔຖͷ஫จ਺ FROM order GROUP BY item_name
5. SELECT item_name as ϝχϡʔ, count(item_name) over count_menu AS ஫จ਺ , price as Ձ֨, order_date as ஫จ೔, orderer as ஫จऀ FROM order WINDOW count_menu AS (PARTITION BY item_name); จ๏ʢPARTITION BYʣ ΢Οϯυ΢ؔ਺ JUFN@OBNFͰάϧʔϐϯά͢Δ
   (3061
   #:ͷΑ͏ͳΠϝʔδʣ 1"35*5*0/
   #:Ͱू໿ͤͣूܭ͕Ͱ͖Δ
6. ׆༻ྫᶄ ౦ژͷίϩφײછऀ਺ ೔෇ ࠓ೔ͷײછऀ਺ ೔ؒฏۉ ೔ؒ߹ܭ                                         લޙͷϨίʔυͷ߹ܭ΍ฏۉΛܭࢉͰ͖Δ
7. SELECT date AS ೔෇, infected_person AS ࠓ೔ͷײછऀ਺, AVG(infected_person) OVER 7 days AS 7 ೔ؒฏۉ, SUM(infected_person) OVER 7 days AS 7 ೔ؒ߹ܭ FROM covit WINDOW 7 days AS ( ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) จ๏ʢORDER BYʣ ݱࡏͷߦ͔ΒߦલͷϨίʔυΛܭࢉ 03%&3
   #:Λ࢖͏ͱલޙͷϨίʔυͷूܭ͕Ͱ͖Δ
8. ΢Οϯυ΢ؔ਺ͷ·ͱΊ • PARTITION BYΛ࢖͏ͱू໿ͤͣʹूܭͰ͖Δ 
 ʢσʔλͷऔಘͱΧ΢ϯτΛ·ͱΊͯߦ͑Δʣ • ORDER BYΛ࢖͏ͱલޙͷϨίʔυͷܭࢉ͕Ͱ͖Δ 
 ʢҠಈฏۉΛ؆୯ʹٻΊΒΕΔʣ
9. ࢀߟ • ୡਓʹֶͿSQLపఈࢦೆॻ ୈ2൛ ॳڃऀͰऴΘΓͨ ͘ͳ͍͋ͳͨ΁ʢᠳӭࣾ ϛοΫஶ) • ෼ੳؔ਺ʢ΢Πϯυ΢ؔ਺ʣΛΘ͔Γ΍͘͢આ໌͠ ͯΈͨ 
 https://qiita.com/tlokweng/items/ fc13dc30cc1aa28231c5