ＳSQLサマリーの求め方

Question

create table 売上ロット
( 
cd varchar2(10)
,LOT varchar2(10)
,最古売上月 varchar2(10)
)
;
create table 売上トラン
( 
cd varchar2(10)
,売上額 number
,売上日 varchar2(10)
)
;

insert into 売上ロット values('123','D1','2019/01/01');
insert into 売上ロット values('123','D2','2019/02/01');
insert into 売上ロット values('123','D3','2019/03/01');
insert into 売上トラン values('123',30,'2019/01/01');
insert into 売上トラン values('123',10,'2019/01/06');
insert into 売上トラン values('123',120,'2019/03/01');
commit;

売上ロット
CD LOT 最古売上月
-------+-------+-------
123 D1 2019/01/01
123 D2 2019/02/01
123 D3 2019/03/01

売上トラン
CD 売上額 売上日
-------+-------+-------
123 30 2019/01/01
123 10 2019/01/06
123 120 2019/03/01

上記2つのテーブルの情報から、下記のような情報を得たい

CD LOT 最古売月 売上金額
-------+-------+----------+-------
123 D1 2019/01 40
123 D2 2019/02 0
123 D3 2019/03 120

最古売上日間をSUMすればいいのですが、単純なSQLでは実現できないでしょうか。
plsqlでcursorに入れるという形であればできると思いますが、単一SQLで実現できる方法があれば
教えて頂けませんでしょうか。

下記のようなSQLを売上日を変えて、UNIONでつなげれば、もとまるとは思いますが
1920/01/01～2019/12/01をすべて求める必要があり、すべてパラメータを変えるのは難しい状態です。

select t1.cd,t1.lot,SUBSTR(売上日,1,7)||'/01',sum(t2.売上額) from 売上ロット t1
left join 売上トラン t2
on t1.cd = t2.cd
where 売上日 > = '2019/01/01'
and  売上日 <    '2019/02/01'
group by t1.cd,t1.lot,SUBSTR(売上日,1,7)||'/01'

Comments

[Orlofsky]

データ型によってSQLの書き方が違いますので、最初から質問にCREATE TABLE を追記しては？

[Orlofsky]

データもINSERTにしては？

[yamaguti]

追記しました。

[Orlofsky]

追記ではなく修正してください。Markdownのhttps://teratail.com/help/question-tips#questionTips3-7 の [コード] で載せてください。

[yamaguti]

修正しました。

[Orlofsky]

最初のテーブル定義でSELECTするデータの提示は削除しては？

[yamaguti]

最初のテーブル定義でSELECTするデータの提示は削除しては？
→申し訳ございません。どういう意味でしょうか。

[yodel]

結局どこが分からなくてつまづいていたのでしょうか？

Answer 1

ちょっとやってみました。

SELECT
    T1.CD
,   T1.LOT
,   T1.最古売上月
,   NVL(SUM(T2.売上額), 0)      売上金額
FROM
    (
        SELECT
            CD
        ,   LOT
        ,   最古売上月
        ,   LEAD(最古売上月, 1, '9999/99/99')
            OVER(PARTITION BY CD
                 ORDER BY 最古売上月)   AS  終了日
        FROM
            売上ロット
    )   T1
    LEFT JOIN
        売上トラン  T2
        ON  T2.CD       =   T1.CD
        AND T2.売上日   >=  T1.最古売上月
        AND T2.売上日   <   T1.終了日
GROUP BY
    T1.CD
,   T1.LOT
,   T1.最古売上月
ORDER BY
    T1.CD
,   T1.LOT
,   T1.最古売上月

Comments

[yamaguti]

ありがとうございました。解決しました。

Answer 2

売上ロットに対してウインドウ関数の**lag()**を用いて期間の範囲を作成し、その範囲での集計をとれば良いかと思います。

select CD, LOT, substr(最古売上月, 1, 7) 最古売上, sum(t2.売上額)
from (
    select CD, LOT, 最古売上月
         , lag(最古売上月, 1, '0000/00/00') 
           over(partiton by cd, lot order by 最古売上月) 
           lag_最古売上月
    from 売上ロット
 ) t1
 left join 売上トラン 
 on t1.CD=t2.CD and t1.lag_最古売上月 < t2.売上日 and t2.売上日 <= t1.最古売上月
group by CD, LOT, substr(最古売上月, 1, 7)

Comments

[yamaguti]

ありがとうございました。解決しました。

[sazi]

最古売上だからlead()の方でしたね。

Answer 3

ヒントで
trunc は日付型か数値型に使います。文字型から一部の文字列を取得するのは substr です。SQL言語リファレンスを確認してください。

可能であれば、最古売上月や売上日はdate型にした方がデータにあり得ない日付が入ってしまって想定外の結果になるなど無用なトラブルが少ないです。

Comments

[yamaguti]

承知しました。

テーブル定義は変更できない為、ＳＱＬを修正致しました。