This example uses a simulated data set to show how to use the CCM function to:
- Identify the optimal value for EmbeddingDimensions.
- Check for a causal relationship between two time series.
Input
seqid | t | a | b |
---|---|---|---|
1 | 1 | 0.439016523 | 0.844698604 |
1 | 2 | 0.79590473 | 0.416313404 |
1 | 3 | 0.457454911 | 0.80120226 |
1 | 4 | 0.83460391 | 0.462840003 |
1 | 5 | 0.453855618 | 0.866674285 |
1 | 6 | 0.847111468 | 0.420195438 |
1 | 7 | 0.464311363 | 0.840110673 |
1 | 8 | 0.854059164 | 0.474302962 |
1 | 9 | 0.440280147 | 0.863834294 |
1 | 10 | 0.809889391 | 0.425037187 |
2 | 1 | 0.773946283 | 0.63958518 |
2 | 2 | 0.508680994 | 0.850617675 |
... | ... | ... | ... |
2 | 10 | 0.766623465 | 0.940756652 |
3 | 1 | 0.813294227 | 0.789552227 |
3 | 2 | 0.247789031 | 0.549992501 |
... | ... | ... | ... |
3 | 10 | 0.793969139 | 0.608697646 |
... | ... | ... | ... |
... | ... | ... | ... |
10 | 1 | 0.503156674 | 0.794651776 |
10 | 2 | 0.823104545 | 0.580508316 |
... | ... | ... | ... |
10 | 10 | 0.812508422 | 0.406036663 |
Step 1 SQL Call: Identify Optimal Value for EmbeddingDimensions
The CauseColumns and EffectColumns arguments must have the same value, the SelfPredict argument must have the value 'true', and the LibrarySize argument must be omitted.
SELECT * from CCM ( ON CCMExample AS InputTable USING SequenceIDColumn ('seqid') TimeColumn ('t') CauseColumns ('b') EffectColumns ('b') EmbeddingDimensions (2,3,4,5,6,7,8,9,10) SelfPredict ('t') ) AS dt;
Step 1 Output
cause | effect | library_size | correlation | jaccard_index | lower_bound | upper_bound | effect_size | effect_size_id | embedding_dimension |
---|---|---|---|---|---|---|---|---|---|
b | b | 90 | 0.997619146432549 | 0.997619146432549 | 0.997619146432549 | 0 | 0 | 0.997619146432549 | 2 |
Step 2 SQL Call: Check for Causal Relationship Between Two Time Series
The EmbeddingDimensions argument has the optimal value identified in Step 1.
SELECT * from CCM ( ON CCMExample AS InputTable USING SequenceIDColumn ('seqid') TimeColumn ('t') CauseColumns ('a','b') EffectColumns ('a','b') EmbeddingDimensions ('2') ) AS dt;
Step 2 Output
cause | effect | library_size | correlation | jaccard_index | lower_bound | upper_bound | effect_size | effect_size_id |
---|---|---|---|---|---|---|---|---|
a | b | 3 | 0.0953577181959707 | 0.0474348274083848 | 0.142842631635922 | 0.2053062000969 | 0.0474348274083848 | 0.0261042449848043 |
a | b | 100 | 0.292185902147916 | 0.276353905641276 | 0.307859356247328 | 0.276353905641276 | ||
b | a | 3 | 0.126132322073481 | 0.0748762908890498 | 0.176723628043906 | 0.495124117770105 | 0.0748762908890498 | 0.0282432749583384 |
b | a | 100 | 0.55247162271285 | 0.538744605905156 | 0.565905368590645 | 0.538744605905156 |