Spark-SQLの「外部クエリを参照する式…」エラーを修正する方法

前提・実現したいこと

SPARK SQL 2.4.4を使用しています。
別起票した質問でご回答いただいた内容を反映してコーディングしたところ、SparkSQLではエラーとなってしまいました。

実現したいこととしては以下の通りです。

重複した値を持つ複数のレコードを一つにまとめて見やすく成型するSQLを考えています。
GROUP BYしたレコードにて判定分を書いているのですが、
複数項目があてはまる場合は優先順位が高いほうを項目として取り出したいと考えています。

例）
★「生徒名簿」上のレコード
生徒ID｜学年｜年度｜クラブID｜優先順位｜…
0001｜ 1｜2019｜ 1｜ 1｜…
0001｜ 1｜2019｜ 2｜ 2｜…
0001｜ 2｜2020｜ 2｜ 1｜…

★「クラブ名簿」上のレコード
クラブID｜クラブ名｜…
1｜水泳｜…
2｜手芸｜…

★「クラブ履歴」
生徒ID｜2019_所属クラブ｜2020_所属クラブ｜…
0001｜水泳｜手芸｜…

上記の例では、生徒名簿とクラブ名簿というテーブルを、クラブ履歴というテーブルにまとめてみれるようにしたいのです。

連日こちらの皆様に甘えてしまって恐縮ではございますが、解消方法などご存じの方がいらっしゃいましたらご教示いただけますと幸いです。

発生している問題・エラーメッセージ

Error in SQL statement:AnalysisException Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses: Aggregate[min(outer(優先順位#3638)) AS min(outer())#3694]""

該当のソースコード

select 
生徒名簿.生徒ID as 生徒ID
MAX(CASE 生徒名簿.年度 WHEN '2019' THEN クラブ名簿.クラブ名 ELSE NULL END) AS 2019_所属クラブ,
MAX(CASE 生徒名簿.年度 WHEN '2020' THEN クラブ名簿.クラブ名 ELSE NULL END) AS 2020_所属クラブ

FROM 生徒名簿
INNER JOIN クラブ名簿
ON 生徒名簿.クラブID = クラブ名簿.クラブID

where 優先順位 = (
        select Min(優先順位) from 生徒名簿 where 生徒ID=生徒名簿.生徒ID and 年度=生徒名簿.年度 
      )

GROUP BY 生徒名簿.生徒ID

行動規範の内容に同意します

回答1件

ベストアンサー

エラー内容からすると、SPARK SQLでは相関副問合せが許されていないか、group by項目が必要かもしれません。

以下で実行してみて下さい。

SQL
1select  t1.生徒ID
2      , Max(CASE t1.年度 WHEN '2019' THEN クラブ名 END) AS 2019_所属クラブ
3      , Max(CASE t1.年度 WHEN '2020' THEN クラブ名 END) AS 2020_所属クラブ
4FROM  生徒名簿 t1 INNER JOIN クラブ名簿 t2
5      ON t1.クラブID=t2.クラブID
6      inner join (
7        select 生徒ID, 年度, Min(優先度) as 優先度 from 生徒名簿 group by 生徒ID, 年度
8      ) t3
9      ON t1.生徒ID=t3.生徒ID and t1.年度=t3.年度 and t1.優先度=t3.優先度
10group by t1.生徒ID

投稿2020/02/21 06:57

sazi

総合スコア25327

piyopiyo_pi

2020/02/21 13:26

教えていただいた方法で、エラーになることなく値を取得することができました！ただ、私が作成した例が誤っており、取得したい値とは別の表が作成されてしまいました。優先順位が入っているのは、結合先である「クラブ名簿」の状態が正しいテストテーブルです。つまり以下のような形となります。例） ★「生徒名簿」上のレコード生徒ID｜学年｜年度｜クラブID｜… 0001 ｜ 1｜ 2019｜ 1｜… 0001 ｜ 1｜ 2019｜ 2｜… 0001 ｜ 2｜ 2020｜ 2｜… ★「クラブ名簿」上のレコードクラブID｜クラブ名｜優先順位｜… 1｜水泳｜ 1｜… 2｜手芸｜ 2｜… 3｜バレー｜ 3｜… ★「クラブ履歴」生徒ID｜2019_所属クラブ｜2020_所属クラブ｜… 0001 ｜水泳｜手芸｜… 生徒名簿を「生徒ID」でGROUP BYをかけて、取得できたレコードのうち、年度ごとに優先順位の高い項目を一つ「XXXX_所属クラブ」という項目に設定します。質問をする身でありながら、正しい前提をお出しできず大変申し訳ございませんでした。可能でしたら、もう少しだけご助力いただけますと幸いです。

sazi

2020/02/21 14:16

私の回答で一歩答えに近づいたなら、応用してみて下さい。優先順位を条件にする考え方は同じです。その場所が別なテーブルだったなら、そこまでたどり着くようにテーブルを結合するなどすれば良いので。

piyopiyo_pi

2020/02/26 12:08

自分でもいろいろと調べてみましたが、結合後に優先順位を条件にする方法がわかりませんでした。 sazi様が以前ご回答なさっていたhttps://teratail.com/questions/122046も、結合前のテーブルでMAXの値の取得が可能なため、私が行いたいことと外れていました。もしsazi様が過去にご回答された内容で、私の件と類似の内容がございましたら、ご紹介いただくことは可能でしょうか。甘えきっていることは承知の上ですので、「もっと自分でなんとかするべきだ！」と思われたようでしたら、このコメントは無視していただいて構いません。

piyopiyo_pi

2020/02/28 11:35

QAの本題としてはSparkSQLのエラーの解消でしたので、はじめのsazi様のご回答で解消済みでした。お手を煩わせてしまい申し訳ございません。自分でももう少し試行錯誤してみます。ありがとうございました。

行動規範の内容に同意します