章ごとの最頻出3語連続（3-tuple）

ここでは、あるクエリをより大きなクエリ内のサブクエリとして使います。Spark SQL は高度な SQL 機能をサポートしています。これまでに、12 章からなる本全体で最も一般的な単語の並びを見つける方法を学びました。今度は、各章ごとに最頻出の 3-tuple を求めます。グループごとに先頭の行を取得するために、ウィンドウ関数を使います。

word、id、chapter の列をもつテーブルがあります。

1. chapter 列は章番号に対応します。
2. word 列は文書中の単語 1 語に対応します。
3. id 列は文書内での単語位置に対応します。

次のクエリも用意されています:

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

この表から、目的の結果の最初の行は次のようになると分かります。

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

あなたの課題は、subquery をより大きなクエリ内のサブクエリとして使い、章ごとの最頻出 3-tuple を取得することです。期待する結果はスキーマは同じですが、各章につき 1 行になります。ROW_NUMBER() を使って、章ごとに行番号を付与してください。