Bộ 3 từ xuất hiện nhiều nhất theo từng chương

Bây giờ bạn sẽ dùng một truy vấn như một truy vấn con bên trong một truy vấn lớn hơn. Spark SQL hỗ trợ các tính năng nâng cao của SQL. Trước đó bạn đã học cách tìm các chuỗi từ phổ biến nhất trên toàn bộ một cuốn sách gồm 12 chương. Giờ bạn sẽ lấy bộ 3 từ xuất hiện nhiều nhất cho từng chương trong 12 chương. Bạn sẽ làm điều này bằng cách dùng window function để lấy hàng đứng đầu trong mỗi nhóm.

Có một bảng với các cột word, id, chapter.

1. Cột chapter tương ứng với số thứ tự chương.
2. Cột word tương ứng với một từ đơn trong tài liệu.
3. Cột id tương ứng với vị trí của từ trong tài liệu.

Ta cũng có truy vấn sau:

subquery = """
SELECT chapter, w1, w2, w3, COUNT(*) as count
FROM
(
    SELECT
    chapter,
    word AS w1,
    LEAD(word, 1) OVER(PARTITION BY chapter ORDER BY id ) AS w2,
    LEAD(word, 2) OVER(PARTITION BY chapter ORDER BY id ) AS w3
    FROM text
)
GROUP BY chapter, w1, w2, w3
ORDER BY chapter, count DESC
"""

spark.sql(subquery).show(5)

+-------+---+-----+----+-----+
|chapter| w1|   w2|  w3|count|
+-------+---+-----+----+-----+
|      1| up|   to| the|    6|
|      1|one|   of| the|    6|
|      1| in|front|  of|    5|
|      1| up|  and|down|    5|
|      1| it|  was|   a|    5|
+-------+---+-----+----+-----+
only showing top 5 rows

Từ bảng này bạn có thể xác định rằng hàng đầu tiên của kết quả mong muốn sẽ là:

+-------+---+-----+----+-----+
|chapter| w1|   w2|  w3|count|
+-------+---+-----+----+-----+
|      1| up|   to| the|    6|
+-------+---+-----+----+-----+

Nhiệm vụ của bạn là dùng subquery như một truy vấn con trong một truy vấn lớn hơn để lấy bộ 3 từ xuất hiện nhiều nhất cho mỗi chương. Kết quả mong muốn sẽ có cùng lược đồ, nhưng chỉ một hàng cho mỗi chương. Hãy dùng ROW_NUMBER() để lấy số thứ tự hàng theo từng hàng trong từng chương.