minne meets Hivemall/pepabo_minne_matrix_factorization_in_hivemall

第三回 Hivemall Meetup
https://eventdots.jp/event/597518

Transcript

1. ϋϯυϝΠυϚʔέοτͰϨίϝϯσʔγϣϯ
   ࡾ୐༔հ(.01&1"#0JOD
   ୈࡾճ)JWFNBMM.FFUVQ
   NJOOFNFFUT)JWFNBMM
   

   View Slide

2. ϓϦϯγύϧΤϯδχΞ
   ࡾ୐༔հ!NPOPDISPNFHBOF
   NJOOFࣄۀ෦
   IUUQCMPHNPOPDISPNFHBOFDPN
   

   View Slide

3. ໨࣍
   wϋϯυϝΠυϚʔέοτͱ͍͏αʔϏεͱɺͦͷಛ௃
   wڠௐϑΟϧλϦϯάͱ͸
   w.BUSJY'BDUPSJ[BUJPOʹΑΔϨίϝϯσʔγϣϯ
   wNJOOFͰͷར༻ࣄྫ
   

   View Slide

4. ϋϯυϝΠυϚʔέοτͱ͍͏
   αʔϏεͱɺͦͷಛ௃
   

   View Slide

5. View Slide

6. wݸਓ͕खܰʹϋϯυϝΠυ࡞඼ͷల
   ࣔ΍ചങ͕Ͱ͖Δɺࠃ಺࠷େͷϋϯ
   υϝΠυϚʔέοτ
   w࡞Ո਺ສਓಥഁ
   wΞΫηαϦʔ΍όοάɺࡒ෍ɺখ෺
   ͳͲ
   NJOOF
   

   View Slide

7. NJOOF
   IUUQTNJOOFDPN
   

   View Slide

8. Ϟϊͮ͘ΓΛ͞ΕΔΈͳ͞·ͷ࡞඼ʹײಈͯ͠ɺ
   ͨ͘͞Μͷํʹ͍ͭͰ΋ݟͯ΋Β͑Δ৔ॴΛ࡞Γ͍ͨɺ
   ͦͯ͠ɺΈͳ͞·ͷϑΝϯΛ࡞Γ૿΍͓͢ख఻͍͕͍ͨ͠
   l
   

   View Slide

9. ϋϯυϝΠυϚʔέοτͷಛ௃
   

   View Slide

10. ϋϯυϝΠυϚʔέοτͷಛ௃
    ଟछଟ༷ͳ࡞Ո͞Μ͕ू͏
    ˠ࡞඼਺͕ଟ͍
    ࡞඼͸ϋϯυϝΠυ
    ˠࡏݿ਺͕গͳ͍ɺ͍ΘΏΔҰ఺΋ͷ΍ɺड஫ੜ࢈
    

    View Slide

11. ࡞඼ͱͷग़ձ͍
    ࡞඼਺͕ଟ͍
    ˠݸผϢʔβʔʹର͢ΔਓྗͰͷਪન͸ࣄ্࣮ෆՄೳ
    ࡏݿ਺͕গͳ͍
    ˠશମϢʔβʔʹର͢Δ࣮੷͔Βબఆͨ͠ਓؾ࡞඼ͷ༗ޮظ͕ؒ୹͍
    ˠݸผͷϢʔβʔ޲͚ʹγεςϜ͕͓͢͢Ί࡞ՈΛબఆ͢Δ࢓૊Έ͕ඞཁ
    

    View Slide

12. ͓͢͢Ί࡞Ոͷબఆ
    wNJOOFʮ͋ͳͨʹ͓͢͢Ίͷ࡞Ոʯ
    wϢʔβʔͷߦಈΛجʹ࡞ՈΛϨʔςΟϯά
    Activity
    Filter and shuffle
    Users
    fav, follow etc…
    Matrix Factorization
    Recommendation
    import
    DB
    

    View Slide

13. ڠௐϑΟϧλϦϯάͱ͸
    

    View Slide

14. ڠௐϑΟϧλϦϯά
    lଟ͘ͷϢʔβͷᅂ޷৘ใΛ஝ੵ͠ɺ͋ΔϢʔβ
    ͱᅂ޷ͷྨࣅͨ͠ଞͷϢʔβͷ৘ใΛ༻͍ͯࣗ
    ಈతʹਪ࿦Λߦ͏ํ๏࿦z
    8JLJQFEJBIUUQTKBXJLJQFEJBPSHXJLJڠௐϑΟϧλϦϯά
    

    View Slide

15. ڠௐϑΟϧλϦϯά
    8JLJQFEJBIUUQTFOXJLJQFEJBPSHXJLJ$PMMBCPSBUJWF@pMUFSJOH
    #Z.PTIBOJO0XOXPSL $$#:4" IUUQTDPNNPOTXJLJNFEJBPSHXJOEFYQIQ DVSJE
    

    View Slide

16. ڠௐϑΟϧλϦϯά
    ᅂ޷ͷऩू
    ϢʔβʔͱΞΠςϜʹΑΔධՁߦྻΛ࡞੒͢Δ
    ࣅ͍ͯΔϢʔβʔΛ୳ͩ͢͠
    ྫ͑͹ɺϢʔβʔؒͷධՁ఺਺ΛݩʹྨࣅੑείΞΛࢉग़͢Δ
    ϢʔΫϦουڑ཭ɺϐΞιϯ૬ؔɺ+BDDBSE܎਺ͳͲ
    ΞΠςϜΛਪન͢Δ
    ࣅ͍ͯΔϢʔβʔ͕ධՁ͍ͯͯ͠ɺࣗ෼͕ධՁ͍ͯ͠ͳ͍ΞΠςϜ
    ྫ͑͹ɺϢʔβʔྨࣅੑείΞʹج͍ͯධՁ༧ଌΛࢉग़͢Δ
    

    View Slide

17. ڠௐϑΟϧλϦϯά՝୊
    w௨ৗͷϏδωεͰ࢖͏σʔλ͸ɺϢʔβʔɺΞΠςϜͱ΋ʹ๲େ͔ͭɺૄͳσʔ
    λͰ͋Δ͜ͱ͕ଟ͍
    wྨࣅ౓ΛٻΊΔͨΊͷܭࢉྔ͕૿͑Δ
    w૬ؔؔ܎ͷநग़͕೉͘͠ͳΓਫ਼౓͕ग़ͳ͍
    

    View Slide

18. .BUSJY'BDUPSJ[BUJPOʹΑΔ
    Ϩίϝϯσʔγϣϯ
    

    View Slide

19. .BUSJY'BDUPSJ[BUJPO
    

    View Slide

20. .BUSJY'BDUPSJ[BUJPO
    wϢʔβʔ΍ΞΠςϜ͝ͱͷධՁͷภΓ͕͋ͬͯɺେྔ͔ͭૄͳσʔλʹର͢Δ
    ධՁ༧ଌ
    wLݸͷજࡏҼࢠΛ࣋ͭϢʔβʔߦྻͱΞΠςϜߦྻΛ༻͍ͯۙࣅʢ࣍ݩ࡟ݮʣ
    wϢʔβʔ΍ΞΠςϜʹର͢ΔόΠΞεͷߟྀ
    
    
    
    
    
    Item
    User R =
    ≈
    m
    P
    n n Q
    ×
    m
    k
    k
    

    View Slide

21. ͓͞Β͍ߦྻͷੵ
    0
    B
    B
    @
    1 3
    1 4
    2 1
    1 3
    1
    C
    C
    A
    ✓
    4 1 2
    2 4 2
    ◆
    =
    0
    B
    B
    @
    10 13 8
    12 17 10
    10 6 6
    10 13 8
    1
    C
    C
    A
    

    View Slide

22. ͓͞Β͍ߦྻͷੵ
    w ߦͱྻͷ಺ੵ͕ߦྻ੒෼
    0
    B
    B
    @
    1 3
    1 4
    2 1
    1 3
    1
    C
    C
    A
    ✓
    4 1 2
    2 4 2
    ◆
    =
    0
    B
    B
    @
    10 13 8
    12 17 10
    10 6 6
    10 13 8
    1
    C
    C
    A
    (1 ⇤ 4) + (3 ⇤ 2) = 10
    

    View Slide

23. ͓͞Β͍ߦྻͷੵ
    w ߦͱྻͷ಺ੵ͕ߦྻ੒෼
    w NºLͱLºOͷߦྻͷੵ͸NºO
    w ٯ΋·ͨ੒Γཱͭ
    0
    B
    B
    @
    1 3
    1 4
    2 1
    1 3
    1
    C
    C
    A
    ✓
    4 1 2
    2 4 2
    ◆
    =
    0
    B
    B
    @
    10 13 8
    12 17 10
    10 6 6
    10 13 8
    1
    C
    C
    A
    3
    4
    3
    4
    (4×2) ͱ (2×3) Ͱ (4×3)ͷߦྻ
    

    View Slide

24. .BUSJY'BDUPSJ[BUJPO
    

    View Slide

25. .BUSJY'BDUPSJ[BUJPO
    ϥϯμϜͳ஋ͰॳظԽ͞Εͨ෼ղޙͷߦྻΛͭ༻ҙ͢Δ
    ݩͷධՁߦྻͰ஋͕ೖ͍ͬͯΔ੒෼શͯʹରͯ͠ɺ෼ղޙͷߦྻͷੵͷ݁Ռ
    ͱൺֱ͢Δ
    ޡࠩΛิਖ਼͢Δ
    ݩͷධՁߦྻͱۙࣅʹͳΔ·Ͱ Λ܁Γฦ͢
    
    
    
    
    
    Item
    User R =
    ≈
    m
    P
    n n Q
    ×
    m
    k
    k
    

    View Slide

26. ͭ·Γ
    R
    =
    m
    P n
    n Q
    ×
    m
    k
    k
    LݸͷજࡏҼࢠΛ࣋ͭͭͷߦྻͷੵʹ
    ΑΓɺݩͷධՁߦྻͷ஋ʹۙͮ͘Α͏
    ͳA Aͷ૊Έ߹ΘͤΛ୳͢͜ͱ
    0
    B
    B
    @
    ? ?
    ? ?
    ? ?
    ? ?
    1
    C
    C
    A
    ✓
    ? ? ?
    ? ? ?
    ◆
    =
    0
    B
    B
    @
    10 13 8
    12 17 10
    10 6 6
    10 13 8
    1
    C
    C
    A
    ٻΊΒΕ֤ͨߦྻ͸ɺݩͷಛ௃Λ·ͱΊͨߦྻͱΈͳ
    ͢͜ͱ͕Ͱ͖Δ
    w 6TFSͷಛ௃ΛL࣍ݩʹ·ͱΊͨߦྻ
    w *UFNͷಛ௃ΛL࣍ݩʹ·ͱΊͨߦྻ
    

    View Slide

27. ͭ·Γ
    R
    =
    m
    P n
    n Q
    ×
    m
    k
    k
    LݸͷજࡏҼࢠΛ࣋ͭͭͷߦྻͷੵʹ
    ΑΓɺݩͷධՁߦྻͷ஋ʹۙͮ͘Α͏
    ͳA Aͷ૊Έ߹ΘͤΛ୳͢͜ͱ
    0
    B
    B
    @
    ? ?
    ? ?
    ? ?
    ? ?
    1
    C
    C
    A
    ✓
    ? ? ?
    ? ? ?
    ◆
    =
    0
    B
    B
    @
    10 13 8
    12 17 10
    10 6 6
    10 13 8
    1
    C
    C
    A
    ˠ
    ϥϯμϜͳA Aͷ૊Έ߹Θ͔ͤΒٻΊ
    ͨ஋ͱɺਖ਼ղͷ஋ͷޡࠩΛখͯ͘͞͠
    ͍͚͹Α͍
    ˠޯ഑߱Լ๏
    

    View Slide

28. ޯ഑߱Լ๏ͱ͸
    wػցֶशʹ͓͍ͯϞσϧʹରֶͯ͠शΛਐΊΔͨΊͷख๏ͷͻͱͭɻ
    wτϨʔχϯάର৅ͷσʔλʹରͯ͠Ϟσϧͱͷޡ͕ࠩ࠷খʹͳΔΑ͏ʹϞσϧ
    ಺ͷύϥϝλΛߋ৽͍ͯ͘͜͠ͱɻ
    wύϥϝλߋ৽͸ɺޡࠩΛఆٛͨؔ͠਺Λඍ෼ͯ͠࠷খʹ͚ۙͮΔૢ࡞Λ܁Γฦ
    ͢͜ͱͰߦ͏ɻ
    

    View Slide

29. ࢀߟ
    (PʹΑΔޯ഑߱Լ๏
    ཧ࿦ͱ࣮ફ
    IUUQTTQFBLFSEFDLDPNNPOPDISPNFHBOFHSBEJFOUEFTDFOUJOHPMBOH
    

    View Slide

30. .BUSJY'BDUPSJ[BUJPO
    w༧ଌ஋
    ˆ
    rij = pT
    i
    qj =
    k
    X
    k=1
    pikqkj
    e2
    ij
    = (rij ˆ
    rij)2 = (rij
    k
    X
    k=1
    pikqkj)2
    p0
    ik
    = pik + ↵
    @
    @pik
    e2
    ij
    = pik + 2↵eijqkj
    q0
    kj
    = qkj + ↵
    @
    @qkj
    e2
    ij
    = qkj + 2↵eijpik
    wೋ৐ޡࠩͷ໨తؔ਺
    wύϥϝλߋ৽ࣜ
    

    View Slide

31. .BUSJY'BDUPSJ[BUJPO3FHVMBSJ[BUJPO
    w༧ଌ஋
    ˆ
    rij = pT
    i
    qj =
    k
    X
    k=1
    pikqkj
    wೋ৐ޡࠩͷ໨తؔ਺
    wύϥϝλߋ৽ࣜ
    e2
    ij
    = (rij ˆ
    rij)2 +
    2
    ( P 2
    + Q 2
    )
    p0
    ik
    = pik + ↵
    @
    @pik
    e2
    ij
    = pik + ↵(2eijqkj pik)
    q0
    kj
    = qkj + ↵
    @
    @qkj
    e2
    ij
    = qkj + ↵(2eijpik qkj)
    

    View Slide

32. .BUSJY'BDUPSJ[BUJPO#JBT
    w༧ଌ஋
    wೋ৐ޡࠩͷ໨తؔ਺
    wύϥϝλߋ৽ࣜ
    ˆ
    rij = µ + buser(i) + bitem(j) +
    k
    X
    k=1
    pikqkj
    e2
    ij
    = (rij ˆ
    rij)2 +
    2
    ( buser
    2
    + bitem
    2
    + P 2
    + Q 2
    )
    p0
    ik
    = pik + ↵
    @
    @pik
    e2
    ij
    = pik + ↵(2eijqkj pik)
    q0
    kj
    = qkj + ↵
    @
    @qkj
    e2
    ij
    = qkj + ↵(2eijpik qkj)
    buser(i) = buser(i) + ↵
    @
    @buser(i)
    e2
    ij
    = buser(i) + ↵(2eij buser(i))
    bitem(j) = bitem(j) + ↵
    @
    @bitem(j)
    e2
    ij
    = bitem(j) + ↵(2eij bitem(j))
    

    View Slide

33. :FT XF`SFHPQIFS
    

    View Slide

34. .BUSJY'BDUPSJ[BUJPO
    rows, cols := R.Dims()
    p := mat64.NewDense(rows, K, randomSlice(rows*K))
    q := mat64.NewDense(K, cols, randomSlice(rows*K))
    for step := 0; step < 5000; step++ {
    for i := 0; i < rows; i++ {
    for j := 0; j < cols; j++ {
    rij := R.At(i, j)
    if rij == 0.0 {
    continue
    }
    pi := p.RowView(i)
    qj := q.ColView(j)
    err := rij - mat64.Dot(pi, qj)
    for k := 0; k < K; k++ {
    pik := p.At(i, k)
    qkj := q.At(k, j)
    p.Set(i, k, pik+alpha*(2*err*qkj))
    q.Set(k, j, qkj+alpha*(2*err*pik))
    }
    }
    }
    }
    return p, q
    # R
    ⽷5 0 3⽹
    ⾇0 2 2⾇
    ⽸4 4 0⽺
    # R^
    ⽷ 4.867 4.489 3.194⽹
    ⾇ 2.482 2.266 1.629⾇
    ⽸ 4.155 3.834 2.726⽺
    

    View Slide

35. NJOOFͰͷར༻ࣄྫ
    

    View Slide

36. Ϩίϝϯυ
    wNJOOFʮ͋ͳͨʹ͓͢͢Ίͷ࡞Ոʯ
    wϢʔβʔͷߦಈΛجʹ࡞ՈΛϨʔςΟϯά
    Activity
    Filter and shuffle
    Users
    fav, follow etc…
    Matrix Factorization
    Recommendation
    import
    DB
    

    View Slide

37. ߦಈϩάͷϨʔςΟϯά
    

    View Slide

38. #JHGPPU
    wϖύϘͷ࣍ੈ୅ϩάl׆༻zج൫
    wߦಈϩάͷऩूɺ෼ੳɺ׆༻ͷ֤ஈ֊ʹ͓͍ͯɺશࣾͰར༻Ͱ͖Δ൚༻ੑͱ۩
    ମతͳ׆༻ํ๏Λఏڙ
    wࠃ಺࠷େڃϋϯυϝΠυϚʔέοτNJOOFΛࢧ͑Δϩάج൫
    

    View Slide

39. #JHGPPU
    IDFA/GAID
    UID
    rack-bigfoot
    Service
    Request
    Activity
    log
    Services
    DB
    Attribute
    Big Cube
    Cube
    https://icons8.com
    BI
    Recommendation
    Bandit algorithm
    Re-marketing
    Feedback
    Name identification
    Cookie Sync
    

    View Slide

40. ࢀߟ
    αʔϏεʹدΓఴ͏ϩάج൫
    ϩάऩूͷͦͷઌʹ
    IUUQTTQFBLFSEFDLDPNNPOPDISPNFHBOFQFQBCPMPHJOGSBTUSVDUVSFCJHGPPU
    

    View Slide

41. Ϩίϝϯυ
    wNJOOFʮ͋ͳͨʹ͓͢͢Ίͷ࡞Ոʯ
    wϢʔβʔͷߦಈΛجʹ࡞ՈΛϨʔςΟϯά
    Activity
    Filter and shuffle
    Users
    fav, follow etc…
    Matrix Factorization
    Recommendation
    import
    DB
    

    View Slide

42. .BUSJY'BDUPSJ[BUJPO
    CZ)JWFNBMM
    

    View Slide

43. .BUSJY'BDUPSJ[BUJPOCZ)JWFNBMM
    Activity
    Rating
    Ratings
    20%
    80%
    testings
    trainings sgd_model predictions
    evaluations MAE
    RMSE
    Bias
    P, Q
    Service
    fav, follow etc…
    Recommendation
    Matrix
    Factorization
    

    View Slide

44. SBUJOH
    wධՁߦྻΛ౤ೖ͢Δ޻ఔ
    wNJOOFͰ͸Ϣʔβʔʹ໌ࣔతͳධՁΛೖྗͯ͠΋Β͏ͷͰ͸ͳ͘ɺલड़ͷߦ
    ಈϩάΛݩʹAߪೖऀAͷA࡞ՈAʹର͢ΔධՁΛಋग़͍ͯ͠Δ
    Activity
    Filter and shuffle
    Users
    fav, follow etc…
    Matrix Factorization
    Recommendation
    import
    DB
    

    View Slide

45. SBUJOH
    INSERT OVERWRITE TABLE ratings
    SELECT
    r.account_id,
    r.creator_id,
    (
    -- rating
    ) AS rating,
    rand(31) as rnd
    FROM (
    ...
    3FTVMU&YQPSUΑΓ*/4&35 07&383*5&
    5"#-&
    ͷ΄͏͕ߴ଎
    ޙ޻ఔͰτϨʔχϯάͱςετσʔλʹׂΓৼΔͨΊ
    ϥϯμϜ஋ΛׂΓৼ͓ͬͯ͘ͱศར
    

    View Slide

46. USBJOJOH UFTUJOH
    wϨʔςΟϯάޙͷσʔλΛτϨʔχϯά༻ɺݕূ༻ʹׂΓৼΔ޻ఔ
    wࠓճ͸ΛτϨʔχϯά༻σʔλɺΛݕূ༻σʔλͱ͢Δ
    

    View Slide

47. USBJOJOH UFTUJOH
    INSERT OVERWRITE TABLE training
    SELECT
    account_id,
    creator_id,
    rating,
    rnd
    FROM
    ratings
    WHERE
    rnd > 0.2
    -- rnd <= 0.2
    ;
    ΛτϨʔχϯά༻ USBJOJOH
    ʹɻ
    ಉ͡Α͏ʹͯ͠Λςετ༻ UFTUJOH
    ʹׂΓৼΔɻ
    

    View Slide

48. .BUSJY'BDUPSJ[BUJPO
    w.BUSJY'BDUPSJ[BUJPOΛ࢖ͬͯɺධՁߦྻ͔ΒLݸͷજࡏҼࢠΛ࣋ͭϢʔβʔ
    ߦྻͱΞΠςϜߦྻʢNJOOFͷ৔߹ɺ࡞ՈߦྻʣΛಋ͘޻ఔ
    w)JWFNBMMͰ͸όΠΞε߲͖ͭͷߦྻΛಋग़͢Δ
    
    
    
    
    
    Item
    User R =
    ≈
    m
    P
    n n Q
    ×
    m
    k
    k
    

    View Slide

49. .BUSJY'BDUPSJ[BUJPO
    INSERT OVERWRITE TABLE sgd_model
    SELECT
    idx,
    array_avg(u_rank) as Pu,
    array_avg(i_rank) as Qi,
    avg(u_bias) as Bu,
    avg(i_bias) as Bi,
    min(mu) as mu
    FROM (
    SELECT train_mf_sgd(account_id, creator_id, rating, '-
    factor 20 -iter 50 -update_mu') AS (idx, u_rank, i_rank,
    u_bias, i_bias, mu)
    FROM training
    ) t
    GROUP BY idx;
    

    View Slide

50. .BUSJY'BDUPSJ[BUJPO
    INSERT OVERWRITE TABLE sgd_model
    SELECT
    idx,
    array_avg(u_rank) as Pu,
    array_avg(i_rank) as Qi,
    avg(u_bias) as Bu,
    avg(i_bias) as Bi,
    min(mu) as mu
    FROM (
    SELECT train_mf_sgd(account_id, creator_id, rating, '-
    factor 20 -iter 50 -update_mu') AS (idx, u_rank, i_rank,
    u_bias, i_bias, mu)
    FROM training
    ) t
    GROUP BY idx;
    ޙ޻ఔͰධՁͷฏۉ஋Λ࢖͏͕ɺ5SFBTVSF%BUB্
    Ͱ͸ม਺͕࢖͑ͳ͍ͷͰʢશͯಉ͡ʹͳΔ͕ʣ
    VQEBUFNVΦϓγϣϯΛ࢖ͬͯɺ͜͜ͰٻΊ͓ͯ͘
    ࿦ཧߏ଄ͱͯ͠͸ߦྻ͕ͩJEY͸ڞ௨Խ͞Ε͓ͯΓ
    ͻͱͭͷςʔϒϧʹ֨ೲ͞ΕΔ
    1V 2J͸ͦΕͧΕϢʔβʔɺΞΠςϜʢ࡞Ոʣ͋ͨ
    ΓͷLݸͷજࡏҼࢠΛ഑ྻͱͯ࣋ͭ͠
    USBJO@NG@THEʹΑΓ֬཰తޯ഑߱Լ๏ʹΑΔ
    .BUSJY'BDUPSJ[BUJPO͕ߦΘΕΔɻ
    ଞʹ΋"EB(SBE࠷దԽΛ࢖͏USBJO@NG@BEBHSBE΋
    ࢖͏͜ͱ͕Ͱ͖Δ
    

    View Slide

51. FWBMVBUJPO
    wֶशͨ͠Ϟσϧ͕աֶश͍ͯ͠ͳ͍͔Λௐ΂Δ޻ఔ
    w࠷ॳͷ޻ఔͰ෼཭͓͍ͯͨ͠ςετσʔλΛ࢖ͬͯ༧ଌධՁΛߦ͏
    wશͯͷ݁ՌΛݟൺ΂Δ͜ͱ͸Ͱ͖ͳ͍ͨΊɺશޡࠩΛಛఆͷख๏ʹΑΓ൑ఆ
    ͢Δ
    w."&ޡࠩͷઈର஋ͷฏۉ
    w3.4&ޡࠩͷೋ৐ͷฏۉͷฏํࠜ
    wຖճͷ݁ՌΛςʔϒϧʹग़ྗͯ͠ਪҠΛ֬ೝ͍ͯ͠Δ
    

    View Slide

52. FWBMVBUJPO
    SELECT
    mae(t3.predicted, t3.actual) as mae,
    rmse(t3.predicted, t3.actual) as rmse
    FROM
    (
    SELECT
    t2.actual,
    mf_predict(if(size(t2.Pu)=0, null, t2.Pu), if(size(p2.Qi)=0, null, Qi), t2.Bu, p2.Bi, t2.mu)
    as predicted
    FROM (
    SELECT
    t1.account_id,
    t1.creator_id,
    t1.rating as actual,
    p1.Pu,
    p1.Bu,
    p1.mu
    FROM
    testing t1
    LEFT OUTER JOIN sgd_model p1
    ON (t1.account_id = p1.idx)
    WHERE
    p1.Bu IS NOT NULL AND p1.mu IS NOT NULL
    ) t2
    LEFT OUTER JOIN sgd_model p2
    ON (t2.creator_id = p2.idx)
    ) t3;
    ֶशࡁΈϞσϧΛ࢖ͬͯςετσʔλʹର͢Δ
    ."& 3.4&ΛٻΊΔʢ஋͸খ͍͞΄ͲΑ͍ʣ
    

    View Slide

53. QSFEJDUJPO
    wֶश͕ऴΘͬͨϞσϧΛ࢖ͬͯධՁΛߦ͏޻ఔ
    wϢʔβʔߦྻͱΞΠςϜʢ࡞Ոʣߦྻͷੵ࿨ʢʹόΠΞεΛՃ͑ͨ΋ͷʣ͕༧
    ଌϨʔςΟϯάͱͳΔ
    wྫ͑͹͋ΔϢʔβʔ͕ະධՁͷΞΠςϜʢ࡞Ոʣͷ͏ͪɺ༧ଌϨʔςΟϯά͕
    ߴ͍΋ͷΛਪન͢Δ
    

    View Slide

54. QSFEJDUJPO
    INSERT OVERWRITE TABLE prediction
    SELECT
    t2.account_id,
    t2.creator_id,
    t2.rank,
    t2.predicted
    FROM
    (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY t1.account_id ORDER BY t1.predicted DESC) AS rank,
    t1.account_id,
    t1.creator_id,
    t1.predicted
    FROM
    (
    SELECT
    accounts_creators.account_id,
    accounts_creators.creator_id,
    mf_predict(if(size(accounts.Pu)=0, null, accounts.Pu), if(size(creators.Qi)=0, null,
    creators.Qi), accounts.Bu, creators.Bi, accounts.mu) AS predicted
    FROM
    -- prediction for all accounts and creators
    ) t1
    ) t2
    WHERE
    t2.rank <= 100;
    ֤ΞΧ΢ϯτͷ༧ଌϨʔςΟϯά͕ߴ͍΋ͷΛ݅
    NG@QSFEJDUʹΑΓֶशࡁΈͷϞσϧ͔Β֤ΞΠςϜʹର͢Δ
    ༧ଌϨʔςΟϯάΛٻΊΔ
    ΞΧ΢ϯτ͝ͱͷ༧ଌϨʔςΟϯάͰฒͼସ͑
    

    View Slide

55. ϫʔΫϑϩʔͷ؅ཧ
    

    View Slide

56. ϫʔΫϑϩʔͷ؅ཧ
    w5SFBTVSF%BUBͷεέδϡʔϧΫΤϦΛར༻
    wΫΤϦͷίʔυ؅ཧ༻ʹ1FOEVMVNΛ։ൃ
    wIUUQTHJUIVCDPNNPOPDISPNFHBOFQFOEVMVN
    w%4-ʹΑͬͯεέδϡʔϧΫΤϦΛهड़͠ɺίʔυ؅ཧ
    Scheduled queries
    Queries on GitHub
    Apply
    Pendulum
    

    View Slide

57. 1FOEVMVN
    schedule 'test-scheduled-job' do
    database 'db_name'
    query 'select time from access;'
    retry_limit 0
    priority :normal
    cron '30 0 * * *'
    timezone 'Asia/Tokyo'
    delay 0
    result_url 'td://@/db_name/table_name'
    end
    Schedfile
    Apply
    $ pendulum --apikey='...' -a --dry-run
    $ pendulum --apikey='...' -a
    

    View Slide

58. %JHEBHҠߦத
    IUUQTHJUIVCDPNUSFBTVSFEBUBEJHEBH
    
    

    View Slide

59. "QQFOEJY
    

    View Slide

60. "QQFOEJY
    w'BDUPSJ[BUJPO.BDIJOF
    wϨʔςΟϯάͷ޻෉
    wಋೖͱධՁํ๏
    

    View Slide

61. ·ͱΊ
    

    View Slide

62. ·ͱΊ
    w)JWFNBMMͱ5SFBTVSF%BUBͷ૊Έ߹ΘͤͰେྔσʔλʹର͢Δػցֶशར༻Λ
    ෑډ௿࢝͘ΊΔ͜ͱ͕Ͱ͖Δ
    w)JWFΫΤϦͰ؆୯ʹ΍Δ͜ͱ͕Ͱ͖Δ͚Ͳɺ಺෦ͷϩδοΫ͸೺Ѳ͍ͯͨ͠΄
    ͏͕Α͍
    wσʔλ͕͋Ε͹ྟΉ݁Ռ͕ग़ͯ͘ΔΘ͚Ͱ͸ͳ͍ͷͰɺϑΟϧλϦϯά΍νϡʔ
    χϯάɺϨʔςΟϯάͷௐ੔ͳͲ͸ͱͬͯ΋େࣄ
    

    View Slide

63. ͓ΘΓ
    

    View Slide

64. ܅΋ϖύϘͰಇ͔ͳ͍͔ʁ
    ࠷৽ͷ࠾༻৘ใΛνΣοΫˠ !QC@SFDSVJU
    

    View Slide