編集履歴

質問編集履歴

誤字を修正しました

2020/05/08 03:01

投稿

sumAA

スコア14

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -238,7 +238,7 @@
     CONCAT(u.l_name, u.f_name),
-    CONCAT(u.l_name_kana, u.f_name_kana) AS,
+    CONCAT(u.l_name_kana, u.f_name_kana) ,
     CONCAT(u.prefecture, u.city, u.o_address)
@@ -372,7 +372,7 @@
-なので、ゆくゆくデータ数が数万件レベルを想定し、サブクエリの利用は避ける方向性で、クエリ文を仕上げてみたいと考えておりますが、結合で理想のビューを出力するのに方向性が全くわきません。
+ゆくゆくデータ数が数万件レベルを想定し、サブクエリの利用は避けて、クエリ文を仕上げたいと考えておりますが、結合で理想のビューを出力するのに方向性が全くわきません。

説明の補足、試行結果を追記しました。

2020/05/08 03:01

投稿

sumAA

スコア14

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -132,7 +132,7 @@
 　・
-全5人くらい
+全5人(絞り込んだ結果仮に5人とする)
 ```
@@ -164,7 +164,9 @@
 ーーーーーーー
- 14人  15人 (数字はざっくりです)
+ 14人  15人
+(全レコード数は29件、IDがA始まりが14人、Bが15人とする)
 ```
@@ -174,40 +176,206 @@
 usrテーブルから [問題2]、[問題3]の情報を取り出せ。但し結果は一緒に表示しろ
-```
 条件：サブクエリは使用しない・・実際環境はデータ数が数万件規模を想定
-内容としては、レイアウトが一定でないため、NULLを活用し1つの表として出力するとのこと。
-なのですが、全くレイアウトの異なるテーブルをいかに1つの表にするのか、
-方向性すらつかめない状況です。統合（UNION）は無理そうですし、
-結合を活用するにしても結局、集計関数利用によるグループ化の必要があるため
-問題2の列数に集計が引っ張られてしまいます。
-```
-出力内容としては、
-問題2、問題3で間違いないのは確認できているのですが、
-クエリ文を書き上げられずにいます。
-何かお気付きの方がいらっしゃいましたら、
-ぜひ知恵をお借りできれば幸いです。
+ ↓↓
+答えとして欲しいのは下のような出力結果です
+```OutPut
+user_id     名前       名前カナ      住所        A人数   B人数
+ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
+B0001     音無響子    オトナシキョウコ  岡山県・・・    14       15
+　・　　　　　 ・・・　　　　　・・・・　　　　　・・・・　　　　NULL     NULL
+　・         ・・・　　　　　・・・・　　　　　・・・・　　　　NULL     NULL
+全5人
+```
+現状思いつく限りで書いたクエリ文が、
+```
+SELECT
+    u.user_id,
+    CONCAT(u.l_name, u.f_name) AS 名前,
+    CONCAT(u.l_name_kana, u.f_name_kana) AS 名前カナ,
+    CONCAT(u.prefecture, u.city, u.o_address) AS 住所,
+    SUM( CASE WHEN cont.user_id LIKE 'A%' THEN 1 ELSE 0 END ) AS A,
+    SUM( CASE WHEN cont.user_id LIKE 'B%' THEN 1 ELSE 0 END ) AS B
+FROM
+    usr AS u INNER JOIN usr AS cont
+ON
+    u.user_id = cont.user_id
+WHERE
+    u.f_name_kana LIKE '%コ'
+GROUP BY
+    u.user_id,
+    CONCAT(u.l_name, u.f_name),
+    CONCAT(u.l_name_kana, u.f_name_kana) AS,
+    CONCAT(u.prefecture, u.city, u.o_address)
+```
+なのですが、この結果は、
+```
+user_id     名前       名前カナ      住所        A人数   B人数
+ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
+B0001     音無響子    オトナシキョウコ  岡山県・・・     0        1
+　・　　　　  　・・・　　　　　・・・・　　　　　・・・・　　　　 0        1
+　・          ・・・　　　　　・・・・　　　　　・・・・　　　　 0        1
+全5人
+```
+となってしまいます。
+集計関数SUMを使用しているのでGROUP BYは必須となります。
+GROUP BYが必須となれば必然的に集計列以外をグループ化しないとなりません。
+（今回だと、user_id, 名前, 名前カナ, 住所）
+自分で書いたクエリ文の結果が上記のようになるのは当然ではあるのですが、
+理想の出力結果を得るクエリ文を記載するにあたり
+方向性を見出せない状況になってしまっています。
+UNIONでもできないか以下のように試してみました。
+```
+SELECT
+     u.user_id,
+     CONCAT(u.l_name, u.f_name) AS 名前,
+     CONCAT(u.l_name_kana, u.f_name_kana) AS 名前カナ,
+     CONCAT(u.prefecture, u.city, u.o_address) AS 住所
+FROM
+     usr AS u
+WHERE
+     u.f_name_kana LIKE '%コ'
+UNION
+SELECT
+    SUM( CASE WHEN user_id LIKE 'A%' THEN 1 ELSE 0 END ) AS A
+    SUM( CASE WHEN user_id LIKE 'B%' THEN 1 ELSE 0 END ) AS B
+FROM
+    usr
+```
+結果はエラー文が返ってきました。
+問題2と問題3とではテーブルの構造が異なるのでそうなるのは当然でしょう。
+以下サブクエリを利用すれば似たような次のような出力結果は得ることができました。
+```
+SELECT
+    user_id,
+    CONCAT(l_name, f_name) AS 名前,
+    CONCAT(l_name_kana, f_name_kana) AS 名前カナ,
+    CONCAT(prefecture, city, o_address) AS 住所,
+    (SELECT DISTINCT COUNT(user_id) FROM usr WHERE user_id LIKE 'A%') AS A,
+    (SELECT DISTINCT COUNT(user_id) FROM usr WHERE user_id LIKE 'B%') AS B,
+FROM
+    usr
+WHERE
+    f_name_kana LIKE '％コ'
+```
+```output
+user_id     名前       名前カナ      住所        A人数   B人数
+ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
+B0001     音無響子    オトナシキョウコ  岡山県・・・     14      15
+　・　　　　　・・・　　　　　・・・・　　　　　・・・・　　      14      15
+　・         ・・・　　　　　・・・・　　　　　・・・・　　     14      15
+全5人
+```
+A, B人数の集計結果が全行に入ってしまっているので、一行目だけに集計結果を表示できれば、と思っていましたが、サブクエリ文は便利な一方で処理数が増加傾向にあり重くなりがちです。
+なので、ゆくゆくデータ数が数万件レベルを想定し、サブクエリの利用は避ける方向性で、クエリ文を仕上げてみたいと考えておりますが、結合で理想のビューを出力するのに方向性が全くわきません。
+何かご存知の方がいらっしゃればぜひともご教示いただけますと幸いです。
 よろしくお願いします。

こんばんは。問題2、問題3の間違いでした。ご指摘ありがとうございます！

2020/05/08 02:53

投稿

sumAA

スコア14

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -172,7 +172,7 @@
 **__問題4：__**
-usrテーブルから [問題2]、[問題4]の情報を取り出せ。但し結果は一緒に表示しろ
+usrテーブルから [問題2]、[問題3]の情報を取り出せ。但し結果は一緒に表示しろ
 ```
@@ -198,7 +198,7 @@
 出力内容としては、
-問題2、問題4で間違いないのは確認できているのですが、
+問題2、問題3で間違いないのは確認できているのですが、
 クエリ文を書き上げられずにいます。