Tweet
Tweet

Slide 1

Slide 1 text

Temporal Data Models @kuro_m88

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

SnapShot Model

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

TransactionͱFact

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

BiTemporal Data Model

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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