用于关联 SQLITE 数据库表中带时间戳的事件的 SQL 查询

发布于 2024-12-11 19:08:27 字数 730 浏览 0 评论 0原文

我有一个 SQLITE 数据库（我们称之为参数），它存储参数更改的列表以及它们更改的时间。即：

key    paramid    time    val
---    -------    ----    ---
1      7          1.2     0
2      3          2.5     23
3      3          5.6     54
4      3          7.1     76
5      7          8.4     1
6      3          9.2     33
7      9          9.7     22
8      3          10.3    21

我想使用查询生成一个表，其中包含每次一个参数更改时以及第一个参数更改时另一个参数的值以及更改时间。即：

param3Val    latestParam7Val    time
---------    ---------------    ----
23           0                  2.5
54           0                  5.6
76           0                  7.1
33           1                  9.2
21           1                  10.3

有什么想法可以在 SQL 中有效地做到这一点吗？

谢谢！

原文

I have a database in SQLITE (lets call it params) that stores a list of parameter changes and the times that they changed. ie:

key    paramid    time    val
---    -------    ----    ---
1      7          1.2     0
2      3          2.5     23
3      3          5.6     54
4      3          7.1     76
5      7          8.4     1
6      3          9.2     33
7      9          9.7     22
8      3          10.3    21

I would like to use a query to generate a table that includes every time one parameter changed along with the value of the other parameter when the first parameter changed and the time of the change. ie:

param3Val    latestParam7Val    time
---------    ---------------    ----
23           0                  2.5
54           0                  5.6
76           0                  7.1
33           1                  9.2
21           1                  10.3

Any ideas for doing this efficiently in SQL?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月染过的梦 2024-12-18 19:08:27

您可以使用：

SELECT p1.val
     , (SELECT p2.val
          FROM params p2
         WHERE p2.paramid = 7
           AND p2.time < p1.time
         ORDER BY p2.time DESC
         LIMIT 1
       ) latestParam7Val
     , p1.time
  FROM params p1

替代连接：

SELECT p1.val
     , p2.val
     , p1.time
  FROM params p1
  LEFT JOIN params p2 ON p2.paramid = 7 AND p2.time < p1.time
  LEFT JOIN params p3 ON p3.paramid = 7 AND p3.time < p1.time
                                        AND p3.time > p2.time
 WHERE p3.time IS NULL

You could use:

SELECT p1.val
     , (SELECT p2.val
          FROM params p2
         WHERE p2.paramid = 7
           AND p2.time < p1.time
         ORDER BY p2.time DESC
         LIMIT 1
       ) latestParam7Val
     , p1.time
  FROM params p1

Alternative with joins:

SELECT p1.val
     , p2.val
     , p1.time
  FROM params p1
  LEFT JOIN params p2 ON p2.paramid = 7 AND p2.time < p1.time
  LEFT JOIN params p3 ON p3.paramid = 7 AND p3.time < p1.time
                                        AND p3.time > p2.time
 WHERE p3.time IS NULL

回复收藏 0 原文

╭⌒浅淡时光〆 2024-12-18 19:08:27

试试这个：

SELECT t1.val AS param3Val,
    (SELECT t2.val AS latestParam7Val
     FROM your_table t2
     WHERE t2.paramid <> t1.paramid
       AND t2.key < t1.key
     ORDER BY t2.key DESC
     LIMIT 1), t1.time
FROM your_table t1
ORDER BY t1.key

Try this:

SELECT t1.val AS param3Val,
    (SELECT t2.val AS latestParam7Val
     FROM your_table t2
     WHERE t2.paramid <> t1.paramid
       AND t2.key < t1.key
     ORDER BY t2.key DESC
     LIMIT 1), t1.time
FROM your_table t1
ORDER BY t1.key

回复收藏 0 原文

~没有更多了~