質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Spark

Spark(Apache Spark)とは、膨大なデータを迅速、柔軟に分散並行処理を行うフレームワークです。分析ツールであるApache Hadoopと比較し、最大で100倍の速度でデータ処理ができるとされています。

Q&A

解決済

1回答

718閲覧

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

piyopiyo_pi

総合スコア6

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Spark

Spark(Apache Spark)とは、膨大なデータを迅速、柔軟に分散並行処理を行うフレームワークです。分析ツールであるApache Hadoopと比較し、最大で100倍の速度でデータ処理ができるとされています。

0グッド

0クリップ

投稿2020/02/21 06:02

編集2020/02/21 06:04

前提・実現したいこと

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

エラー内容からすると、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

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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様のご回答で解消済みでした。 お手を煩わせてしまい申し訳ございません。自分でももう少し試行錯誤してみます。 ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問