sql - Dataframe 中的前两个最新记录

Question

我有一个 Apache Spark 数据框，其中包含以下数据(ID、名称、日期):

ID,Name,DATE
1,Anil,2000-06-02
1,Anil,2000-06-03
1,Anil,2000-06-04
2,Arun,2000-06-05
2,Arun,2000-06-06
2,Arun,2000-06-07
3,Anju,2000-06-08
3,Anju,2000-06-09
3,Anju,2000-06-10
4,Ram,2000-06-11
4,Ram,2000-06-02
4,Ram,2000-06-03
4,Ram,2000-06-04
5,Ramu,2000-06-05
5,Ramu,2000-06-06
5,Ramu,2000-06-07
5,Ramu,2000-06-08
6,Renu,2000-06-09
7,Gopu,2000-06-10
7,Gopu,2000-06-11

但是我想要ID的前两条最新记录，我想得到如下输出:

ID,Name,DATE
1,Anil,2000-06-03
1,Anil,2000-06-04
2,Arun,2000-06-06
2,Arun,2000-06-07
3,Anju,2000-06-09
3,Anju,2000-06-10
4,Ram,2000-06-03
4,Ram,2000-06-04
5,Ramu,2000-06-07
5,Ramu,2000-06-08
6,Renu,2000-06-09
7,Gopu,2000-06-10
7,Gopu,2000-06-11

我需要使用像 Lag 这样的窗口函数吗？

Answer 1

使用 LEFT OUTER JOIN 和 COUNT < 2。

SELECT d.ID, d.Name, d.Date
FROM Dataframetable d
LEFT OUTER JOIN Dataframetable d2 ON d2.ID = d.ID AND d.Date < d2.Date
GROUP BY d.ID, d.Name, d.Date
HAVING COUNT(*) < 2

输出

ID  Name    Date
1   Anil    2000-06-03T00:00:00Z
1   Anil    2000-06-04T00:00:00Z
2   Arun    2000-06-06T00:00:00Z
2   Arun    2000-06-07T00:00:00Z
3   Anju    2000-06-09T00:00:00Z
3   Anju    2000-06-10T00:00:00Z
4   Ram     2000-06-04T00:00:00Z
4   Ram     2000-06-11T00:00:00Z
5   Ramu    2000-06-07T00:00:00Z
5   Ramu    2000-06-08T00:00:00Z
6   Renu    2000-06-09T00:00:00Z
7   Gopu    2000-06-10T00:00:00Z
7   Gopu    2000-06-11T00:00:00Z

SQL fiddle :http://sqlfiddle.com/#!6/8dcc2/1/0

使用子查询而不是自连接。

SELECT ID, name, date FROM (SELECT d.ID, d.Name, MAX(d.Date) Date
FROM Dataframetable d
GROUP BY d.ID, d.Name
UNION ALL
SELECT d.ID, d.Name, MAX(d.Date)
FROM Dataframetable d
WHERE d.Date NOT IN 
(SELECT date FROM (SELECT d.ID, d.Name, MAX(d.Date) Date
FROM Dataframetable d
GROUP BY d.ID, d.Name) a)
GROUP BY d.ID, d.Name) b
ORDER BY ID

SQL fiddle :http://sqlfiddle.com/#!6/8dcc2/19/0