SQLite - 自连接与“未来的自我”

Question

我想用它的“未来的自我”自我加入一张桌子。或者附加一列，其中包含一周后的值（或任何其他周期）。假设我有一个包含日期、两个标识符和一个值的表。在查询中，我想获取该值，以及一周后同一组标识符的值。

对于新手来说这是两个问题。首先，当我有多个标识符时（在实际问题中，我有六个标识符来描述每个日期的唯一条目），我应该为该项目创建自己的标识符吗？其次，我该如何解决这一周的滞后问题，尤其是几个月的变化？

这是一个示例数据集：

       date id_1 id_2 value value_future
1  20101224    a    c     1           NA
2  20101224    a    d     2           NA
3  20101224    b    c     3           NA
4  20101224    b    d     4           NA
5  20101225    a    c     5           NA
6  20101225    a    d     6           NA
7  20101225    b    c     7           NA
8  20101225    b    d     8           NA
9  20101226    a    c     9           NA
10 20101226    a    d    10           NA
11 20101226    b    c    11           NA
12 20101226    b    d    12           NA
13 20101227    a    c    13           NA
14 20101227    a    d    14           NA
15 20101227    b    c    15           NA
16 20101227    b    d    16           NA
17 20101228    a    c    17           NA
18 20101228    a    d    18           NA
19 20101228    b    c    19           NA
20 20101228    b    d    20           NA
21 20101229    a    c    21           NA
22 20101229    a    d    22           NA
23 20101229    b    c    23           NA
24 20101229    b    d    24           NA
25 20101230    a    c    25           NA
26 20101230    a    d    26           NA
27 20101230    b    c    27           NA
28 20101230    b    d    28           NA
29 20101231    a    c    29            1
30 20101231    a    d    30            2
31 20101231    b    c    31            3
32 20101231    b    d    32            4
33 20110101    a    c    33            5
34 20110101    a    d    34            6
35 20110101    b    c    35            7
36 20110101    b    d    36            8
37 20110102    a    c    37            9
38 20110102    a    d    38           10
39 20110102    b    c    39           11
40 20110102    b    d    40           12

谢谢！

Answer 1

我不太了解 SQLLite，所以我不得不查找如何添加日期，但我认为这可能有用。

SELECT  c.date, c.id_1, c.id_2, c.value, f.value as value_future
FROM mytable c
LEFT OUTER JOIN mytable f
ON date(c.date, '+7 day') = f.date AND c.id_1 = f.id_1 AND c.id_2 = f.id_2

基本上，您想要对同一个表进行联接，这需要您为联接中的两个表（c 和 f）指定别名。使用 ON 子句中的日期函数连接到一周后的记录，您可以根据需要调整日期修饰符子句（+7 天）来更改日期范围。然后只需添加您拥有的所有标识符即可。一般来说，创建单个主键是一个好主意，但在这种情况下它不会对您有帮助，因为无法找出您想要加入的记录的键。最后要指出的是，结果中现在有 2 个“值”列，因此您需要为其中一个列添加别名以使列列表唯一。这就是“as value_future”的作用。