Temporal Data Models

Transcript

1. Temporal Data Models @kuro_m88
2. Temporal Data • ࣌ؒʹΑͬͯมΘ͍ͬͯ͘σʔλ • ྫ: • ΩϟϯϖʔϯON/OFF • CPCมߋ • ༧ࢉ / ೔༧ࢉ
3. SnapShot Model
4. SnapShot Model • ࡞੒ DBNQBJHO DBNQBJHO@JE JT@BDUJWF DSFBUF VQEBUF EFMFUF     SELECT is_active FROM campaign WHERE campaign_id = 1000 AND deleted_at IS NULL ; +-----------+ | is_active | +-----------+ | 0 | +-----------+
5. SnapShot Model • ߋ৽ DBNQBJHO DBNQBJHO@JE JT@BDUJWF DSFBUF VQEBUF EFMFUF     SELECT is_active FROM campaign WHERE campaign_id = 1000 AND deleted_at IS NULL ; +-----------+ | is_active | +-----------+ | 1 | +-----------+
6. SnapShot Model • ࡟আ DBNQBJHO DBNQBJHO@JE JT@BDUJWF DSFBUF VQEBUF EFMFUF      SELECT is_active FROM campaign WHERE campaign_id = 1000 AND deleted_at IS NULL ; +-----------+ | is_active | +-----------+ | null | +-----------+
7. SnapShot Model • ݱࡏͷঢ়ଶ͔͠Θ͔Βͳ͍(Ұ࣌఺ͰͷSnapShot) • ઃఆཤྺ͕Θ͔Βͳ͍ • ࠷ޙʹߋ৽͞Εͨ࣌ࠁ͔͠Θ͔Βͳ͍ • ઃఆ͕มΘΔ͝ͱʹϩάϑΝΠϧΛు͘ʁ • ઃఆͷ༧໿͕Ͱ͖ͳ͍ • ઃఆΛม͍͑ͨ࣌ࠁ·Ͱ଴͔ͭ͠ͳ͍ • ༧ఆ࣌ࠁʹઃఆΛߋ৽͢ΔόονΛͭ͘Δʁ
8. TransactionͱFact
9. Transaction • 9/1ʹΩϟϯϖʔϯΛONʹ͢ΔઃఆΛೖΕͨ
 9/14ʹΩϟϯϖʔϯΛOFFʹ͢ΔઃఆΛೖΕͨ • ΩϟϯϖʔϯONͷTransaction Time: 9/1ʙ 9/14 • 9/1ʹCPCΛ100ԁʹ͢ΔઃఆΛೖΕͨ • CPC100ԁͷTransaction Time: 9/1ʙ • ※ͦͷϨίʔυ͕DB্Ͱ༗ޮͰ͋ͬͨظؒʹ஫໨
10. Transactional Data Model • ࡞੒ (now() = 2017/09/01) DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU     SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+
11. Transactional Data Model • ߋ৽ (now() = 2017/09/02) DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU         SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() ; +-----------+ | is_active | +-----------+ | 1 | +-----------+
12. Transactional Data Model • ߋ৽ (now() = 2017/09/10) DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU             SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+
13. Transactional Data Model • ࡟আ (now() = 2017/09/20) DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU             SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() ; +-----------+ | is_active | +-----------+ | null | +-----------+
14. Transactional Data Model • ߋ৽ཤྺ͕Θ͔ΔΑ͏ʹͳͬͨʂ • ઃఆͷ༧໿͸Ͱ͖ͳ͍
 มߋ೔࣌·Ͱ଴ͬͯॻ͖׵͑Δ
15. Fact • 9/2͔ΒΩϟϯϖʔϯ͕ONʹͳΔ
 9/16͔ΒΩϟϯϖʔϯ͕OFFʹͳΔ • ΩϟϯϖʔϯONͷValid Time: 9/2 ʙ 9/16 • 9/1ʹCPCΛ100ԁʹ͢Δ • CPC100ԁͷValid Time: 9/1ʙ • ※ࣄ࣮ʹ஫໨͢Δ(࣮ࡍʹDBʹઃఆͨ࣌͠ࠁ͸ؔ܎ͳ͍)
16. Valid Time Data Model • now() = 8/31 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | null | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF GSPN UISV            
17. Valid Time Data Model • now() = 9/1 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF GSPN UISV            
18. Valid Time Data Model • now() = 9/2 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF GSPN UISV            
19. Valid Time Data Model • now() = 9/10 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF GSPN UISV            
20. Valid Time Data Model • now() = 9/20 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | null | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF GSPN UISV            
21. Valid Time Data Model • ઃఆมߋͷ༧໿͕Ͱ͖Δʂ • ͍ͭઃఆΛॻ͖׵͑ͨͷ͔͕Θ͔Βͳ͍
 ༗ޮظؒதʹॻ͖׵͑ΔͱԿ͕ى͖ͨͷ͔
 ݕূෆೳʗ(^o^)ʘ
22. BiTemporal Data Model
23. • ͜Μͳ͜ͱ͕͍ͨ͠ • 9/1ʹʮ9/2͔ΒΩϟϯϖʔϯΛONʹ͢Δ༧໿ΛೖΕΔʯ • 9/14ʹʮ9/16͔ΒΩϟϯϖʔϯΛOFFʹ͢Δ༧໿ΛೖΕΔʯ • ΋ͪΖΜͦΕΒͷཤྺ΋อ࣋͢Δ TransactionͱFact 9/1 9/2 9/14 9/16 Transaction Time Valid Time 9/2͔ΒONʹ͢Δ 9/16͔ΒOFFʹ͢Δ ON OFF
24. BiTemporal Data Model • ࡞੒ (now() = 2017/09/01) SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | null | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU GSPN UISV      
25. BiTemporal Data Model • now() = 2017/09/02 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 1 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU GSPN UISV      
26. BiTemporal Data Model • ߋ৽ (now() = 2017/09/14) SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 1 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU GSPN UISV                  
27. • ͜Μͳ͜ͱ͕͍ͨ͠ • 9/1ʹʮ9/2͔ΒΩϟϯϖʔϯΛONʹ͢Δ༧໿ΛೖΕΔʯ • 9/14ʹʮ9/16͔ΒΩϟϯϖʔϯΛOFFʹ͢Δ༧໿ΛೖΕΔʯ • ΋ͪΖΜͦΕΒͷཤྺ΋อ࣋͢Δ TransactionͱFact(࠶ܝ) 9/1 9/2 9/14 9/16 Transaction Time Valid Time 9/2͔ΒONʹ͢Δ 9/16͔ΒOFFʹ͢Δ ON OFF
28. BiTemporal Data Model • now() = 2017/09/16 SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | 0 | +-----------+ DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU GSPN UISV                  
29. BiTemporal Data Model • ࡟আ (now() = 2017/10/01) DBNQBJHO DBNQBJHO@JE JT@BDUJWF JO PVU GSPN UISV                   SELECT is_active FROM campaign WHERE campaign_id = 1000 AND in <= NOW() AND out > NOW() AND from <= NOW() AND thru > NOW() ; +-----------+ | is_active | +-----------+ | null | +-----------+
30. BiTemporal Data Model • γεςϜ্ͷཤྺͱࣄ࣮৘ใͷཤྺΛ
 ಉ࣌ʹਖ਼͘͠දݱ • ͋Δ࣌఺Ͱͷʮࣄ࣮ʯ͕ҰҙʹΘ͔Δ • ෺ཧ࡟আ / ࿦ཧ࡟আ͕ෆཁʂ
31. ؾʹͳΔͱ͜Ζ • idͷ࠾൪͸Ͳ͏͢Δͷ͔ • SnowFlake౳͍͍ײ͡ͷ΍ͭ࢖͑͹͍͍ͷ͔ • ΫΤϦύϑΥʔϚϯεͷӨڹ͸͋Δͷ͔ • ΫΤϦΩϟογϡ͕ޮ͚͹ͦΜͳʹʁ