Temporal Data Models @kuro_m88 

Temporal Data • ࣌ؒʹΑͬͯมΘ͍ͬͯ͘σʔλ • ྫ: • ΩϟϯϖʔϯON/OFF • CPCมߋ • ༧ࢉ / ೔༧ࢉ 

SnapShot Model 

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 | +-----------+ 

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 | +-----------+ 

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 | +-----------+ 

SnapShot Model • ݱࡏͷঢ়ଶ͔͠Θ͔Βͳ͍(Ұ࣌఺ͰͷSnapShot) • ઃఆཤྺ͕Θ͔Βͳ͍ • ࠷ޙʹߋ৽͞Εͨ࣌ࠁ͔͠Θ͔Βͳ͍ • ઃఆ͕มΘΔ͝ͱʹϩάϑΝΠϧΛు͘ʁ • ઃఆͷ༧໿͕Ͱ͖ͳ͍ • ઃఆΛม͍͑ͨ࣌ࠁ·Ͱ଴͔ͭ͠ͳ͍ • ༧ఆ࣌ࠁʹઃఆΛߋ৽͢ΔόονΛͭ͘Δʁ 

TransactionͱFact 

Transaction • 9/1ʹΩϟϯϖʔϯΛONʹ͢ΔઃఆΛೖΕͨ
 9/14ʹΩϟϯϖʔϯΛOFFʹ͢ΔઃఆΛೖΕͨ • ΩϟϯϖʔϯONͷTransaction Time: 9/1ʙ 9/14 • 9/1ʹCPCΛ100ԁʹ͢ΔઃఆΛೖΕͨ • CPC100ԁͷTransaction Time: 9/1ʙ • ※ͦͷϨίʔυ͕DB্Ͱ༗ޮͰ͋ͬͨظؒʹ஫໨ 

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 | +-----------+ 

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 | +-----------+ 

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 | +-----------+ 

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 | +-----------+ 

Transactional Data Model • ߋ৽ཤྺ͕Θ͔ΔΑ͏ʹͳͬͨʂ • ઃఆͷ༧໿͸Ͱ͖ͳ͍
 มߋ೔࣌·Ͱ଴ͬͯॻ͖׵͑Δ 

Fact • 9/2͔ΒΩϟϯϖʔϯ͕ONʹͳΔ
 9/16͔ΒΩϟϯϖʔϯ͕OFFʹͳΔ • ΩϟϯϖʔϯONͷValid Time: 9/2 ʙ 9/16 • 9/1ʹCPCΛ100ԁʹ͢Δ • CPC100ԁͷValid Time: 9/1ʙ • ※ࣄ࣮ʹ஫໨͢Δ(࣮ࡍʹDBʹઃఆͨ࣌͠ࠁ͸ؔ܎ͳ͍) 

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             

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             

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             

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             

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             

Valid Time Data Model • ઃఆมߋͷ༧໿͕Ͱ͖Δʂ • ͍ͭઃఆΛॻ͖׵͑ͨͷ͔͕Θ͔Βͳ͍
 ༗ޮظؒதʹॻ͖׵͑ΔͱԿ͕ى͖ͨͷ͔
 ݕূෆೳʗ(^o^)ʘ 

BiTemporal Data Model 

• ͜Μͳ͜ͱ͕͍ͨ͠ • 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 

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       

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       

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                   

• ͜Μͳ͜ͱ͕͍ͨ͠ • 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 

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                   

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 | +-----------+ 

BiTemporal Data Model • γεςϜ্ͷཤྺͱࣄ࣮৘ใͷཤྺΛ
 ಉ࣌ʹਖ਼͘͠දݱ • ͋Δ࣌఺Ͱͷʮࣄ࣮ʯ͕ҰҙʹΘ͔Δ • ෺ཧ࡟আ / ࿦ཧ࡟আ͕ෆཁʂ 

ؾʹͳΔͱ͜Ζ • idͷ࠾൪͸Ͳ͏͢Δͷ͔ • SnowFlake౳͍͍ײ͡ͷ΍ͭ࢖͑͹͍͍ͷ͔ • ΫΤϦύϑΥʔϚϯεͷӨڹ͸͋Δͷ͔ • ΫΤϦΩϟογϡ͕ޮ͚͹ͦΜͳʹʁ