sqlite 如何将行与前一行进行比较以检测更改？

zaq34kh6 于 2023-06-23 发布在 SQLite

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

表points：

CREATE TABLE "points" (
    "match_id"    INTEGER,
    "player_id"   INTEGER,
    "opponent_id" INTEGER,
    "set"         INTEGER,
    "point"       INTEGER,
    "won"         INTEGER
);

示例数据：
| 匹配ID|玩家ID|对手ID|集|点|韩元|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 37346937| 14054027| 16471936| 1| 1| 1|
| 三七三四六九三七|14054027| 16471936| 1| 2| 1|
| 三七三四六九三七|14054027| 16471936| 1| 3| 0|
| 三七三四六九三七|14054027| 16471936| 1| 4| 0|
| 三七三四六九三七|14054027| 16471936| 1| 5个|1|
| 三七三四六九三七|14054027| 16471936| 1|六|1|
| 三七三四六九三七|14054027| 16471936| 1|七个|0|
| 三七三四六九三七|14054027| 16471936| 1|八|1|
| 三七三四六九三七|14054027| 16471936| 1|九个|0|
| 三七三四六九三七|14054027| 16471936| 1|十个|1|
| 三七三四六九三七|14054027| 16471936| 1|十一|1|
| 三七三四六九三七|14054027| 16471936| 1|十二岁|1|
| 三七三四六九三七|14054027| 16471936| 1|十三个|0|
| 三七三四六九三七|14054027| 16471936| 1|十四|0|
| 三七三四六九三七|14054027| 16471936| 1|十五|1|
| 三七三四六九三七|14054027| 16471936| 1|十六|0|
| 三七三四六九三七|14054027| 16471936| 1|十七岁|1|
| 三七三四六九三七|14054027| 16471936| 1|十八岁|0|
| 三七三四六九三七|14054027| 16471936| 1|十九|0|
| 三七三四六九三七|14054027| 16471936| 1|二十个|1|
| 三七三四六九三七|14054027| 16471936| 2| 1| 1|
| 三七三四六九三七|14054027| 16471936| 2| 2| 1|
| 三七三四六九三七|14054027| 16471936| 2| 3| 1|
| 三七三四六九三七|14054027| 16471936| 2| 4| 1|
| 三七三四六九三七|14054027| 16471936| 2| 5个|0|
| 三七三四六九三七|14054027| 16471936| 2|六|1|
| 三七三四六九三七|14054027| 16471936| 2|七个|1|
| 三七三四六九三七|14054027| 16471936| 2|八|1|
| 三七三四六九三七|14054027| 16471936| 2|九个|1|
| 三七三四六九三七|14054027| 16471936| 2|十个|1|
| 三七三四六九三七|14054027| 16471936| 2|十一|0|
| 三七三四六九三七|14054027| 16471936| 2|十二岁|0|
| 三七三四六九三七|14054027| 16471936| 2|十三个|0|
| 三七三四六九三七|14054027| 16471936| 2|十四|0|
| 三七三四六九三七|14054027| 16471936| 2|十五|1|
| 三七三四六九三七|14054027| 16471936| 2|十六|1|
| 三七三四六九三七|14054027| 16471936| 3| 1| 0|
| 三七三四六九三七|14054027| 16471936| 3| 2| 1|
| 三七三四六九三七|14054027| 16471936| 3| 3| 0|
| 三七三四六九三七|14054027| 16471936| 3| 4| 1|
| 三七三四六九三七|14054027| 16471936| 3| 5个|1|
| 三七三四六九三七|14054027| 16471936| 3|六|0|
| 三七三四六九三七|14054027| 16471936| 3|七个|0|
| 三七三四六九三七|14054027| 16471936| 3|八|0|
| 三七三四六九三七|14054027| 16471936| 3|九个|0|
| 三七三四六九三七|14054027| 16471936| 3|十个|1|
| 三七三四六九三七|14054027| 16471936| 3|十一|1|
| 三七三四六九三七|14054027| 16471936| 3|十二岁|0|
| 三七三四六九三七|14054027| 16471936| 3|十三个|1|
| 三七三四六九三七|14054027| 16471936| 3|十四|0|
为了确定won中的自相关性，我想比较每个点是否同一个玩家也赢得了上一个点。每场比赛有两名球员。如果一个玩家赢了，另一个就会自动输。所以，我的问题应该集中在一个球员身上。
查询的第二部分：

SELECT cur.match_id, cur.set, cur.point, cur.won, prev.won
FROM points cur 
LEFT JOIN points prev 
ON (cur.match_id, cur.player_id, cur.set, cur.point) =
   (prev.match_id, prev_player_id, prev.set, prev.point + 1);

对于won的以下示例，交换机的数量为3：

[1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0]

如何将cur.won和prev.won与单人条件结合使用，以获得每个match_id的开关数量n_switches？
预期输出：
| 匹配ID|集|n个开关|
| - -----|- -----|- -----|
| 三七三四六九三七|1|十二岁|
| 三七三四六九三七|2| 4|
| 三七三四六九三七|3|八|

sqlite

来源：https://stackoverflow.com/questions/76499602/how-to-compare-row-with-previous-row-to-detect-changes

1条答案

按热度按时间

fivyi3re1#

我们可以在这里使用LAG()解析函数：

WITH cte AS (
    SELECT *, CASE WHEN LAG(won, 1, won) OVER (PARTITION BY match_id, "set"
                                               ORDER BY point) <> won
                   THEN 1 ELSE 0 END AS cnt
    FROM yourTable
)

SELECT match_id, "set", SUM(cnt) AS n_switches
FROM cte
GROUP BY match_id, "set"
ORDER BY match_id, "set";

CASE表达式的逻辑是，对于给定的匹配和集合，将1赋给前一个记录的won值与当前值不同的任何行。然后，我们通过匹配进行聚合，并对这些计数进行设置和求和，以获得最终结果。

赞(0）回复(0）举报 2023-06-23

我来回答

sqlite 如何将行与前一行进行比较以检测更改？

1条答案

相关问题

热门标签

最新问答