Tríos de palabras más frecuentes por capítulo

Ahora usaremos una consulta como subconsulta dentro de otra más grande. Spark SQL admite funciones avanzadas de SQL. Antes aprendiste a encontrar las secuencias de palabras más comunes en todo un libro con 12 capítulos. Ahora obtendrás el trío de palabras más frecuente para cada uno de los 12 capítulos. Lo harás usando una función de ventana para recuperar la primera fila por grupo.

Hay una tabla con las columnas word, id, chapter.

1. La columna chapter corresponde al número de capítulo.
2. La columna word corresponde a una única palabra del documento.
3. La columna id corresponde a la posición de la palabra en el documento.

También tenemos la siguiente consulta:

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

A partir de esta tabla puedes determinar que la primera fila del resultado deseado será:

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

Tu tarea es usar subquery como una subconsulta dentro de una consulta mayor para obtener el trío más frecuente por capítulo. El resultado deseado tendrá el mismo esquema, pero con una única fila por capítulo. Usa ROW_NUMBER() para obtener el número de fila por fila y por capítulo.