複数レコードを1レコードにまとめる方法

Question

商品名 入荷日 　　　価格 産地 都道府県 国名
りんご 2019/05/01 100 国内 千葉 null
りんご 2019/03/01 100 国外 null アメリカ
ばなな 2019/01/01 150 国内 沖縄 null
ばなな 2019/02/01 170 国外 null フィリピン
めろん 2019/11/01 150 国内 北海道 null

上記のテーブルがあります。
これを以下のような形で取得したいです。

名称 入荷日 　　　　価格 国内産地 国外産地
りんご 2019/05/01 100 千葉 アメリカ
ばなな 2019/02/01 170 沖縄 フィリピン
メロン 2019/11/01 150 北海道 null

※入荷日は最も未来の日付を設定します
※同一名称のデータは最大２つです。(上記でいうところの「りんご」「ばなな」)
※取得時の価格は入荷日と同じレコードの価格です
※入荷日が同じで価格が異なる場合は価格が高い方を取得します

前回こちら(https://teratail.com/questions/170177)で質問させていただき、
既存の解決策を見ながらSQLを作成したのですがうまくいきませんでした...
(「group byの式ではありません」エラーとなりました)

select	
	名称
     , 入荷日
     , 価格
     ,(select 都道府県  from フルーツテーブル FT where FT.名称=FT2.名称 and FT.都道府県=FT2.都道府県) as 国内産地
     ,(select 国名      from フルーツテーブル FT where FT.名称=FT2.名称 and FT.国名=FT2.国名) as 国外産地
from フルーツテーブル FT2
group by
	名称
     , 入荷日
     , 価格

お手数おかけして大変申し訳ないのですが助けていただけませんでしょうか。
よろしくお願いいたします。

Comments

[sazi]

Nullとの比較になっていますが、これがNullでなくて値が変わった時にどうなるかのパターンもし示して下さい。
前回の質問内容より難易度高めです。
SQLなら再帰かなって感じですけど、javaで編集した方が楽そうですね。

[ttyp03]

価格の仕様は？表示している入荷日と同じレコードの価格でしょうか？入荷日が同じで価格が異なる場合はどうするのでしょうか？

[usagi518]

見てくださってありがとうございます！
＞Nullでなくて値が変わった時にどうなるか
察しが悪い故、ご指摘の内容に沿っているか自信がないのですが
商品名 入荷日 　　　価格 産地 都道府県 国名
りんご 2019/05/01 100 国内 千葉 null
↓update↓
商品名 入荷日 　　　価格 産地 都道府県 国名
りんご 2019/05/01 100 国外 null メキシコ
このようになることはありません。
また、１つのレコードにおいて都道府県・国名両方に値が入る、NULLが入ることはありません。(都道府県・国名どちらか一方にのみ必ず値が入ります)
ご指摘の内容と食い違っていましたら大変申し訳ないのですが
もう少し詳しくお聞かせ願えませんでしょうか。
javaの方が楽、ですか。前回の質問時に簡略化したデータを書いた私は愚かでした...

[usagi518]

ttyp03さん
ご覧いただきありがとうございます！
ご指摘の通り「表示している入荷日と同じレコードの価格」です。
入荷日が同じで価格が異なる場合は価格が高い方を取得します。
説明が足らず申し訳ありません！追記します！

Answer 1

手打ちで検証もしていないので、集約されるかちょっと不安ですが。

select 名称
    , (select max(価格) from フルーツテーブル where 名称=step3.名称 and 入荷日=step3.最終入荷日) as 最新価格
    ,  国内産地, 国外産地
from (
  select 名称, max(産地別最終入荷日) as 最終入荷日, max(国内産地) as 国内産地, max(国外産地) as 国外産地
  from (
    select  名称, 産地別最終入荷日
         , (select 都道府県 from フルーツテーブル 
            where 産地='国内' and 名称=step1.名称 and 産地=step1.産地 and 入荷日=step1.産地別最終入荷日
            ) as 国内産地
         , (select 国名 from フルーツテーブル 
            where 産地='国外' and 名称=step1.名称 and 産地=step1.産地 and 入荷日=step1.産地別最終入荷日
            ) as 国外産地
    from (
        select  名称, 産地, max(入荷日) as 産地別最終入荷日
        from フルーツテーブル FT2
        group by  名称, 産地
    ) step1
  ) step2
  group by 名称
) step3

Comments

[usagi518]

ご回答ありがとうございます！
やはりそれなりに複雑になりますね...
早速検証させていただきます！お忙しいところありがとうございました！

[usagi518]

解決に至るヒントを多く示してくださったのでベストアンサーとさせていただきます。
みなさま本当にありがとうございました！

Answer 2

ぱっとみこうかなと思ったんですが
都道府県と国名を見てわからなくなりました。
りんご 2019/05/01 100 千葉 アメリカ
ばなな 2019/02/01 170 沖縄 フィリピン
↑これはあってるんですかね…

CREATE TABLE Table1
    ("商品名" varchar2(10), "入荷日" timestamp, "価格" int, "産地" varchar2(10), "都道府県" varchar2(10), "国名" varchar2(20))
;

INSERT ALL 
    INTO Table1 ("商品名", "入荷日", "価格", "産地", "都道府県", "国名")
         VALUES ('りんご', '01-May-2019 12:00:00 AM', 100, '国内', '千葉', NULL)
    INTO Table1 ("商品名", "入荷日", "価格", "産地", "都道府県", "国名")
         VALUES ('りんご', '01-Mar-2019 12:00:00 AM', 100, '国外', NULL, 'アメリカ')
    INTO Table1 ("商品名", "入荷日", "価格", "産地", "都道府県", "国名")
         VALUES ('ばなな', '01-Jan-2019 12:00:00 AM', 150, '国内', '沖縄', NULL)
    INTO Table1 ("商品名", "入荷日", "価格", "産地", "都道府県", "国名")
         VALUES ('ばなな', '01-Feb-2019 12:00:00 AM', 170, '国外', NULL, 'フィリピン')
    INTO Table1 ("商品名", "入荷日", "価格", "産地", "都道府県", "国名")
         VALUES ('めろん', '01-Nov-2019 12:00:00 AM', 150, '国内', '北海道', NULL)
SELECT * FROM dual
;

select * from Table1 A
where not exists(
  select 0
  from Table1 B
  where B.商品名=A.商品名
    and B.入荷日>A.入荷日
);

Comments

[usagi518]

ご回答ありがとうございます！
取得後は「国内産地」「国外産地」と名前を変えるので大丈夫かと思います。
わざわざSQLを書いてくださりありがとうございます。参考にさせていただきます！

[sousuke]

私の実行結果だと
りんご　千葉　NULL
ばなな　NULL　フィリピン
になるんですよ。国内と国外両方に値が入ることはありません。
なので取得したいデータ通りにはならないです。

追記でありますが同じ入荷日で違う価格が存在する場合が
あるようなので上記のSQLは正しくないです。その場合には対応できません。

[usagi518]

そうなのですね...
わざわざコメントくださりありがとうございました。

Answer 3

価格の因果関係が不明ですが…

SELECT 商品名 名称, MAX(入荷日) 入荷日, MAX(価格) 価格, MAX(都道府県) 国内産地, MAX(国名) 国外産地
FROM フルーツテーブル
GROUP BY 商品名

Comments

[usagi518]

ご回答ありがとうございます！
価格について説明が足りず申し訳ありません...(追記させていただきました)
SQL考えてくださりありがとうございます！参考にさせていただきます。