Go to Qiita Advent Calendar 2024 Top
LoginSignup
da202308051601
@da202308051601

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

この難しいoracleのSQL文を考えて欲しいです。

Q&A

Closed

OracleDatabase

前提

Oracle Databaseを使用しています。
以下のSQL文を実行します。

CREATE文です。
CREATE TABLE PURCHASE_HISTORY (PURCHASER_ID CHAR(5), ORDER_ID VARCHAR(30), PURCHASED_PRODUCTS_ID CHAR(5));
CREATE TABLE SHIPPING_HISTORY (ORDER_ID VARCHAR(30));

一部のINSERT文です。
INSERT INTO PURCHASE_HISTORY (PURCHASER_ID, ORDER_ID, PURCHASED_PRODUCTS_ID) VALUES ('1', '1_20200101', 'A');
INSERT INTO SHIPPING_HISTORY (ORDER_ID) VALUES (''1_20200101');

下記のようなテーブルが2つできます。
PURCHASE_HISTORY

PURCHASER_ID ORDER_ID PURCHASED_PRODUCTS_ID
1 1_20200101 A
1 1_20200101 B
1 1_20200102 C
1 1_20200102 A
2 2_20200102 D
2 2_20200102 E
2 2_20200103 C
2 2_20200103 A

SHIPPING_HISTORY

ORDER_ID
1_20200101
1_20200102

実現したいこと

以下の手順を1つのSQL文で実現したいです。

  1. SHIPPING_HISTORYテーブルに存在しないORDER_IDを持つレコードをPURCHASE_HISTORYテーブルから全カラムを指定して取得する。
    2.1で取得したレコードをORDER_IDで1つのレコードにまとめる。
    なのでSQL実行の結果、以下のような結果になるようにしたいです。
    PURCHASED_PRODUCTS_ID_1,PURCHASED_PRODUCTS_ID_2 の部分は固定です。
    |PURCHASER_ID|ORDER_ID|PURCHASED_PRODUCTS_ID_1|PURCHASED_PRODUCTS_ID_2|
    |:--|:--:|--:|--:|
    |2| 2_20200102 |D|E|
    |2| 2_20200103 |C|A|

試したこと

group byとmaxを使えばいけるそうですが、まだできていません。

0

2Answer

nak435
@nak435

なのでSQL実行の結果、以下のような結果になるようにしたいです。

PURCHASER_ID ORDER_ID PURCHASED_PRODUCTS_ID_1 PURCHASED_PRODUCTS_ID_2
2 2_20200102 D E
2 2_20200103 C A

PURCHASED_PRODUCTS_ID_3以上は存在しない前提でしょうか？
だとしても、行x列の入れ替えが必要で、簡単では無い気がします。

SQL 行列 入れ替えで検索すると、いくつか方法が出てきますね。試してみてはどうですか？

1Like

Comments

  1. @nak435

    結果の2行目の PURCHASED_PRODUCTS_ID_1 と PURCHASED_PRODUCTS_ID_2 が逆ですが、↓こんな感じで出てきました。

    mysql> select PURCHASER_ID, ORDER_ID
    -> ,min(PURCHASED_PRODUCTS_ID) as PURCHASED_PRODUCTS_ID_1
    -> ,max(PURCHASED_PRODUCTS_ID) as PURCHASED_PRODUCTS_ID_2
    -> from PURCHASE_HISTORY
    -> where ORDER_ID not in (select * from SHIPPING_HISTORY)
    -> group by PURCHASER_ID, ORDER_ID;
+--------------+------------+-------------------------+-------------------------+
| PURCHASER_ID | ORDER_ID   | PURCHASED_PRODUCTS_ID_1 | PURCHASED_PRODUCTS_ID_2 |
+--------------+------------+-------------------------+-------------------------+
|            2 | 2_20200102 | D                       | E                       |
|            2 | 2_20200103 | A                       | C                       |
+--------------+------------+-------------------------+-------------------------+
2 rows in set (0.00 sec)

    Oracleでも同じシンタックスだと思います。
    色々アレンジしてみてください。

  2. @da202308051601

    Questioner

    ありがとうございます。
    試してみます。

Yamazin
@Yamazin

一応PURCHASED_PRODUCTS_ID_3以降に対応して
PURCHASED_PRODUCTS_IDが重複していた場合はまとめるようにしたサンプルを
貼っておきます。（多分動く）

サンプル
WITH T1 AS (
  SELECT
  PH.PURCHASER_ID
  ,PH.ORDER_ID
  ,PH.PURCHASED_PRODUCTS_ID
  ,ROW_NUMBER() OVER(PARTITION BY PH.PURCHASER_ID, PH.ORDER_ID ORDER BY PH.PURCHASED_PRODUCTS_ID) AS COL_NO
  FROM PURCHASE_HISTORY PH
  WHERE NOT EXISTS (
    SELECT 1
    FROM SHIPPING_HISTORY SH
    WHERE PH.ORDER_ID = SH.ORDER_ID
  )
  GROUP BY
  PH.PURCHASER_ID,
  PH.ORDER_ID,
  PH.PURCHASED_PRODUCTS_ID
)
SELECT
PURCHASER_ID
,ORDER_ID
,MAX(CASE WHEN COL_NO = 1 THEN PURCHASED_PRODUCTS_ID ELSE NULL END) PURCHASED_PRODUCTS_ID_1
,MAX(CASE WHEN COL_NO = 2 THEN PURCHASED_PRODUCTS_ID ELSE NULL END) PURCHASED_PRODUCTS_ID_2
--,MAX(CASE WHEN COL_NO = 3 THEN PURCHASED_PRODUCTS_ID ELSE NULL END) PURCHASED_PRODUCTS_ID_3
--,MAX(CASE WHEN COL_NO = 4 THEN PURCHASED_PRODUCTS_ID ELSE NULL END) PURCHASED_PRODUCTS_ID_4
FROM T1
GROUP BY
PURCHASER_ID
,ORDER_ID
ORDER BY
PURCHASER_ID
,ORDER_ID
0Like

Comments

  1. @da202308051601

    Questioner

    ありがとうございます。
    試してみます。

Your answer might help someone💌

LoginSign Up