SQLのSELECT文の記述方法について

Question

SQL初学者です。
SELECT文の記述方法がわからないので教えてください。
利用しているRDBMSはSQLiteです。

前提

以下のようなテーブルとデータがあり、
tableBには同一user_idでpointが２個ずつ登録されているものとします。

tableA

user_id	point1	point2
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL

tableB

user_id	point
1	50
1	78
2	66
2	69
3	90
3	71

実現したいこと

tableBの同一user_idのpoint２つを、tableAの対応するuser_idのpoint1, point2に埋め込んだものをSELECTで取得して、以下のような結果を得たいです。
２つのpointはpoint1とpoint2のどちらに振られても構いません。

期待する結果

user_id	point1	point2
1	50	78
2	66	69
3	90	71

恥ずかしながら実現方法が何も思い浮かびません。
考え方やヒントとなるようなことでも構いませんのでご教授ください。

Comments

[Orlofsky]

SQLをシンプルにパフォーマンス良く実行できるように、テーブルは
データベースの正規化 https://oss-db.jp/measures/dojo_info_04.shtml に沿って設計します。
point1, point2 は繰り返し(配列)になります。第1正規化で繰り返しを排除します。
通常第3正規化まで行います。

[takasima20]

双方のテーブルで user_id の過不足はある前提なのかな? ないんだったら tableB だけで処理できそう

[m.ts10806]

point1とpoint2の規則がデータ構造上不明な状態ではかなり複雑なコードを組まざるを得ないように思います。SQLiteで一発でできるかどうか。
テーブル構造変えられないのでしたらアプリケーション側からアクセスしたほうが。
それか、わざわざ別テーブルにしなくていいのでは。

Answer 1

絶対に２件のデータがtableBにあるのであれば

select id
     , min(point) as point1
     , max(point) as point2
from tableB
group by id

これでok

tableAにあってtableBにないidも出したい場合は
上記をleftjoinする感じになります

Comments

[YT0014]

user_idが３の結果が異なります。値の大小ではなく、出現順で、point1とpoint2を決める仕様かと。

[senb]

min/maxはこういう使い方もできるのですね。
今回やりたかったことに対して必要十分でした。
ありがとうございます。

[YT0014]

ごめんなさい、順番には規則がなかったんですね。読み落としていました。失礼いたしました。

Answer 2

縦のものを横にするのはクロス集計と呼ばれます。
この変換の際にはどのような並びにするかの条件が必要になります。
tableBには条件が無いので、row_number()を用いてID毎の連番を生成しこれを条件にします。

select id
     , max(case when seq=1 then point end) as point1
     , max(case when seq=2 then point end) as point2
from (
   select *, row_number() over(partition by id) as seq
   from tableB
) step1
group by id

tableAに反映したいなら上記を元にした更新(UPDATE FROM)を行って下さい。

Comments

[senb]

並びの条件を作るというのがとても参考になりました。
ありがとうございます。