SQLite:每个字段只保留n行

Question

我有张桌子

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+

我需要为每一位画家拿两幅画，以最高的速度。

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+

删除所有其他的东西！

Answer 1

WITH 
cte AS ( SELECT author, 
                name, 
                rating, 
                ROW_NUMBER() OVER ( PARTITION BY author 
                                    ORDER BY rating DESC ) rn
         FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
               FROM cte
               WHERE ratings.author = cte.author
                 AND ratings.name = cte.name
                 AND cte.rn > 2 );

小提琴

Answer 2

另一个使用row_number()窗口函数的版本(因此需要sqlite3.25或更高版本)：

DELETE FROM ratings
WHERE rowid IN (SELECT rowid
                FROM (SELECT rowid
                           , row_number() OVER (PARTITION BY painter ORDER BY rate DESC) AS rn
                      FROM ratings)
                WHERE rn > 2);

Answer 3

使用python脚本。

connection = sqlite3.connect("YOUR_DB.db")
cursor = connection.cursor()

# Select painters with number of its occurance
sql = "SELECT DISTINCT Painter, COUNT(*) FROM Paintings GROUP BY PainterORDER BY COUNT(*) DESC"

# Get only artists with occurance greater than N
data = [artist[0] for artist in selected_data if artist[1] > 150]

while data:

    # One by one get artists and select N-th max rate
    artist = data.pop(0)
    sql = "SELECT Champ51 FROM Paintings WHERE Champ1 = ? ORDER BY Champ51 DESC LIMIT 1 OFFSET 149"
    selected_data = cursor.execute(sql, [artist]).fetchall()
    last = selected_data[0][0]

    # Delete rate less than N-th
    sql = "DELETE FROM Paintings WHERE Champ1 = ? AND Champ51 < ?"
    cursor.execute(sql, [artist, last])
    connection.commit()