一般的な単語の並び

Pythonで学ぶ Spark SQL 入門

Mark Plutowski

Data Scientist

学習

Pythonで学ぶ Spark SQL 入門

予測

Pythonで学ぶ Spark SQL 入門

語末予測

Pythonで学ぶ Spark SQL 入門

シーケンス

Pythonで学ぶ Spark SQL 入門

最後のシーケンス

Pythonで学ぶ Spark SQL 入門

素早い茶色の狐

Pythonで学ぶ Spark SQL 入門

文の括弧

Pythonで学ぶ Spark SQL 入門

別の集計タイプ

Pythonで学ぶ Spark SQL 入門

動画

Pythonで学ぶ Spark SQL 入門

カテゴリ型データ

Pythonで学ぶ Spark SQL 入門

カテゴリ vs 順序

  • カテゴリ: he, hi, she, that, they
  • 順序: 1, 2, 3, 4, 5
Pythonで学ぶ Spark SQL 入門

系列解析

Pythonで学ぶ Spark SQL 入門

前後の単語

Pythonで学ぶ Spark SQL 入門

3-タプル

query3 = """
   SELECT 
   id,
   word AS w1,
   LEAD(word,1) OVER(PARTITION BY part ORDER BY id ) AS w2,
   LEAD(word,2) OVER(PARTITION BY part ORDER BY id ) AS w3
   FROM df
"""
Pythonで学ぶ Spark SQL 入門

副問合せでのウィンドウ関数SQL

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

spark.sql(query3agg).show()
Pythonで学ぶ Spark SQL 入門

副問合せでのウィンドウ関数SQL：出力

+-----+-----+-----+-----+
|   w1|   w2|   w3|count|
+-----+-----+-----+-----+
|  one|   of|  the|   49|
|    i|think| that|   46|
|   it|   is|    a|   46|
|   it|  was|    a|   45|
| that|   it|  was|   38|
|  out|   of|  the|   35|
|.....|.....|.....|.....|
Pythonで学ぶ Spark SQL 入門

最頻の3-タプル

+-----+-----+-----+-----+
|   w1|   w2|   w3|count|
+-----+-----+-----+-----+
|  one|   of|  the|   49|
|    i|think| that|   46|
|   it|   is|    a|   46|
|   it|  was|    a|   45|
| that|   it|  was|   38|
|  out|   of|  the|   35|
| that|    i| have|   35|
|there|  was|    a|   34|
|    i|   do|  not|   34|
| that|   it|   is|   33|
| that|   he|  was|   30|
| that|   he|  had|   30|
| that|    i|  was|   28|
+-----+-----+-----+-----+
Pythonで学ぶ Spark SQL 入門

別の集計タイプ

query3agg = """
SELECT w1, w2, w3, length(w1)+length(w2)+length(w3) as length FROM (
   SELECT 
   word AS w1,
   LEAD(word,1) OVER(PARTITION BY part ORDER BY id ) AS w2,
   LEAD(word,2) OVER(PARTITION BY part ORDER BY id ) AS w3
   FROM df
   WHERE part <> 0 and part <> 13
)
GROUP BY w1, w2, w3 
ORDER BY length DESC
""" 

spark.sql(query3agg).show(truncate=False)
Pythonで学ぶ Spark SQL 入門

別の集計タイプ

+-------------------+-------------------+---------------+------+
|                 w1|                 w2|             w3|length|
+-------------------+-------------------+---------------+------+
|comfortable-looking|           building|    two-storied|    38|
|         widespread|comfortable-looking|       building|    37|
|      extraordinary|      circumstances|      connected|    35|
|      simple-minded|      nonconformist|      clergyman|    35|
|       particularly|          malignant|  boot-slitting|    34|
|       unsystematic|        sensational|     literature|    33|
|       oppressively|        respectable|     frock-coat|    33|
|         relentless|        keen-witted|   ready-handed|    33|
|   travelling-cloak|                and|  close-fitting|    32|
|        ruddy-faced|      white-aproned|       landlord|    32|
|  fellow-countryman|            colonel|       lysander|    32|
+-------------------+-------------------+---------------+------+
Pythonで学ぶ Spark SQL 入門

練習しましょう！

Pythonで学ぶ Spark SQL 入門

Preparing Video For Download...