SQLITE中与ROW_NUMBER等效的功能

Question

我在SQLite中有一张表

/* Create a table called NAMES */
CREATE TABLE EVENTS(Id integer , Eventtype integer,value integer,Timestamp DATETIME);

/* Create few records in this table */
INSERT INTO EVENTS VALUES(1,2,1,'2009-01-01 10:00:00');  --ROW1
INSERT INTO EVENTS VALUES(1,2,2,'2007-01-01 10:00:00');  --ROW2
INSERT INTO EVENTS VALUES(2,2,3,'2008-01-01 10:00:00’);  --ROW3

查询所需的是ROW1和ROW3。该查询应基于时间戳选择最新的重复ID和Eventtype组合。 ROW1和ROW2具有相同的事件类型和ID，但ROW1是最新的，因此应选择它。

- Puneet

Answer 1

在SQLite 3.7.11或更高版本中，您可以使用GROUP BY和MAX()来选择返回组中的哪一行：

SELECT *, MAX(timestamp)
FROM events
GROUP BY id, eventtype

在早期版本中，您需要使用子查询查找组中最大行的某个唯一ID（例如您的回答中所示）。

- CL.

Answer 2

我有点晚回答这个问题，但我对当前的答案不满意，因为它们大多使用可能会严重影响性能的相关子查询。

在许多情况下，可以使用标准连接模拟单列分析函数：

SELECT e.*
FROM events e
JOIN
(
    -- Our simulated window with analytical result
    SELECT id, eventtype, MAX(timestamp) AS timestamp
    FROM events
    GROUP BY id, eventtype
) win
USING (id, eventtype, timestamp)

一般来说，模式是：

。

SELECT main.*
FROM main
JOIN
(
    SELECT
        partition_columns,
        FUNCTION(analyzed_column) AS analyzed_column
    FROM main
    GROUP BY partition_columns
) win
USING (partition_columns, analyzed_column)

这些模拟窗口并不完美：

1. 如果您的数据在分析列结果中存在并列，则可能需要从结果集中删除重复项。否则，您将选择与分析列结果匹配的每一行。
2. 如果您的分析函数需要按多个列排序，则需要使用相关子查询。其他答案可以修改以实现所需的结果。

- Mr. Llama

Answer 3

我从以下链接得到了帮助： sqlite equivalent of row_number() over ( partition by ...?

这是我想出来的：

select * from events E1 where timestamp in
(select timestamp from events E2 where E2.id = E1.id and E2.eventtype=E1.eventtype
                         order by E2.timestamp desc
                         LIMIT 1  );

另外，针对SQL SERVER，我正在考虑这个解决方案（因为我没有测试的方法）

select id,eventtype,value,ROW_NUMBER() over 
(PARTITION BY id,eventtype,order by timestamp desc) AS RN  from events where RN<=1 ;

- Puneet