在Sqlite中组合同一表的“上一行”和不同表的JOIN

ddrv8njm 于 2022-12-04 发布在 SQLite

关注(0)|答案(1)|浏览(164)

我有下表

CREATE TABLE "shots" (
    "player"    INTEGER,
    "tournament"    TEXT,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "shot"  INTEGER,
    "text"  TEXT,
    "distance"  REAL,
    "x" TEXT,
    "y" TEXT,
    "z" TEXT
);

下面是一个示例数据：

28237   470 2015    717 1   1   1   Shot 1 302 yds to left fairway, 257 yds to hole 10874   11451.596   10623.774   78.251
28237   470 2015    717 1   1   2   Shot 2 234 yds to right fairway, 71 ft to hole  8437    12150.454   10700.381   86.035
28237   470 2015    717 1   1   3   Shot 3 70 ft to green, 4 ft to hole 838 12215.728   10725.134   88.408
28237   470 2015    717 1   1   4   Shot 4 in the hole  46  12215.1 10729.1 88.371
28237   470 2015    717 1   2   1   Shot 1 199 yds to green, 29 ft to hole  7162    12776.03    10398.086   91.017
28237   470 2015    717 1   2   2   Shot 2 putt 26 ft 7 in., 2 ft 4 in. to hole 319 12749.444   10398.854   90.998
28237   470 2015    717 1   2   3   Shot 3 in the hole  28  12747.3 10397.6 91.027
28237   470 2015    717 1   3   1   Shot 1 296 yds to left intermediate, 204 yds to hole    10651   12596.857   9448.27 94.296
28237   470 2015    717 1   3   2   Shot 2 208 yds to green, 15 ft to hole  7478    12571.0 8825.648    94.673
28237   470 2015    717 1   3   3   Shot 3 putt 17 ft 6 in., 2 ft 5 in. to hole 210 12561.831   8840.539    94.362

我想得到每个shot的前一个位置（x，y，z）。

SELECT cur.player, cur.tournament, cur.year, cur.course, cur.round, cur.hole, cur.shot, cur.x, cur.y, cur.z, prev.x, prev.y, prev.z
FROM shots cur
INNER JOIN shots prev 
ON (cur.player, cur.tournament, cur.year, cur.course, cur.round, cur.hole, cur.shot) =
   (prev.player, prev.tournament, prev.year, prev.course, prev.round, prev.hole, prev.shot - 1)

这个查询基本上要花很长时间。我怎样重写它才能使它更快呢？另外，我需要对一个孔的第一个炮孔（shot = 1）进行调整。这个炮孔由tee_x、tee_y和tee_z组成。这些值在表holes中提供

CREATE TABLE "holes" (
        "tournament"    TEXT,
        "year"  INTEGER,
        "course"    INTEGER,
        "round" INTEGER,
        "hole"  INTEGER,
        "tee_x" TEXT,
        "tee_y" TEXT,
        "tee_z" TEXT
    );

带数据：

470  2015    717 1   1   11450   10625   78.25
   470  2015    717 1   2   12750   10400   91
   470  2015    717 1   3   2565    8840.5  95

谢谢

sqlite

来源：https://stackoverflow.com/questions/74666920/combing-previous-row-of-same-table-and-join-from-different-table-in-sqlite

1条答案

按热度按时间

f8rj6qna1#

首先，您需要一个复合index来加速操作：

CREATE INDEX idx_shots ON shots (player, tournament, year, course, round, hole, shot);

有了该索引，您的查询应该会执行得更快：

SELECT cur.player, cur.tournament, cur.year, cur.course, cur.round, cur.hole, cur.shot, cur.x, cur.y, cur.z, 
       prev.x AS prev_x, prev.y AS prev_y, prev.z AS prev_z
FROM shots cur LEFT JOIN shots prev 
ON (cur.player, cur.tournament, cur.year, cur.course, cur.round, cur.hole, cur.shot) =
   (prev.player, prev.tournament, prev.year, prev.course, prev.round, prev.hole, prev.shot + 1);

我所做的更改：

联接应为LEFT联接，以便包括所有行，而不仅仅是具有前一行的行
-1应为+1，因为前一行的镜头比当前行的镜头小1
为上一行的x、y和z添加了别名

但是，如果您的SQLite版本是3.25.0以上，最好使用窗口函数LAG()而不是自连接：

SELECT *,
       LAG(x) OVER w AS prev_x,
       LAG(y) OVER w AS prev_y,
       LAG(z) OVER w AS prev_z
FROM shots
WINDOW w AS (PARTITION BY player, tournament, year, course, round, hole ORDER BY shot);

请参见demo（我包括了两个查询的查询计划，您可以在其中看到复合索引的使用）。

赞(0）回复(0）举报 2022-12-04

我来回答

在Sqlite中组合同一表的“上一行”和不同表的JOIN

1条答案

相关问题

热门标签

最新问答