C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

SQL

[トピック内 11 記事 (1 - 11 表示)]  << 0 >>

■84769 / inTopicNo.1)  SQL
  
□投稿者/ yapii (1回)-(2017/08/01(Tue) 19:23:30)

分類:[データベース全般] 

Oracle です。

以下のようなデータを取得するSQLをご教示いただけないでしょうか。

売上データより商品別の数量が50個以上となるまで、売上日が直近のデータを取得する

例えば、
以下のような売上データがあるとすると

商品コード「A001」の数量が50以上となるレコードは、No3、No2になります。
商品コード「A002」の数量が50以上となるレコードは、No4になります。


No 売上日 商品コード 販売数
1 2017/01/01 A001 10
2 2017/01/02 A001 30
3 2017/01/03 A001 40
4 2017/01/01 A002 100

1回のSQLで取得できるでしょうか?
よろしくお願いいたします。

引用返信 編集キー/
■84774 / inTopicNo.2)  Re[1]: SQL
□投稿者/ 大谷刑部 (36回)-(2017/08/02(Wed) 09:26:34)
No84769 (yapii さん) に返信
> Oracle です。
>
> 以下のようなデータを取得するSQLをご教示いただけないでしょうか。
>
> 売上データより商品別の数量が50個以上となるまで、売上日が直近のデータを取得する
>
> 例えば、
> 以下のような売上データがあるとすると
>
> 商品コード「A001」の数量が50以上となるレコードは、No3、No2になります。
> 商品コード「A002」の数量が50以上となるレコードは、No4になります。
>
>
> No 売上日 商品コード 販売数
> 1 2017/01/01 A001 10
> 2 2017/01/02 A001 30
> 3 2017/01/03 A001 40
> 4 2017/01/01 A002 100
>
> 1回のSQLで取得できるでしょうか?
> よろしくお願いいたします。
>

Having使えばいいだけでは?

引用返信 編集キー/
■84775 / inTopicNo.3)  Re[1]: SQL
□投稿者/ 魔界の仮面弁士 (1371回)-(2017/08/02(Wed) 09:29:22)
No84769 (yapii さん) に返信
> 売上データより商品別の数量が50個以上となるまで、売上日が直近のデータを取得する

返品レコード(販売数がマイナスの場合)や、販売数 0 のレコードの
扱いが気になりますが、すべて自然数が入っているものと仮定して、
こんな感じで如何でしょう。


DELETE FROM TBL WHERE ROWID IN (
  WITH W1 AS (
    SELECT No, 売上日, 商品コード, 販売数, SUM(販売数)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC) AS 累計
    FROM TBL 
  ), W2 AS (
    SELECT No, 売上日, 商品コード, 販売数, 累計, LAG(累計, 1, 0)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC) AS 前回累計
    FROM W1
  )
  SELECT ROWID FROM W2 WHERE 前回累計 < 50 
)

引用返信 編集キー/
■84776 / inTopicNo.4)  Re[2]: SQL
□投稿者/ 魔界の仮面弁士 (1372回)-(2017/08/02(Wed) 09:36:09)
No84775 (魔界の仮面弁士) に追記
>> 商品コード「A001」の数量が50以上となるレコードは、No3、No2になります。
>> 商品コード「A002」の数量が50以上となるレコードは、No4になります。
> こんな感じで如何でしょう。

全商品を対象とするのではなく、特定の商品(たとえば A001) のみを
対象に抹消する場合には、先の SQL の末尾の『)』という部分を
『) AND 商品コード = 'A001'』にしてみてください。
引用返信 編集キー/
■84782 / inTopicNo.5)  Re[3]: SQL
□投稿者/ furu (112回)-(2017/08/02(Wed) 12:02:21)
No84776 (魔界の仮面弁士 さん) に返信
横槍すみません。

魔界の仮面弁士さん、いつも感動してしまいます。

「抹消する」ってのは何ですか?
引用返信 編集キー/
■84783 / inTopicNo.6)  Re[4]: SQL
□投稿者/ 魔界の仮面弁士 (1373回)-(2017/08/02(Wed) 12:09:42)
No84782 (furu さん) に返信
> 「抹消する」ってのは何ですか?

ひとまず、削除する/DELETE する、の書き間違いとでも捉えて頂ければ。m(_ _)m


# 大谷刑部さんが HAVING 案を出されていましたが、
# 実例を思いつかなかったため、断念して別案で回答してしまいました…。
引用返信 編集キー/
■84784 / inTopicNo.7)  Re[5]: SQL
□投稿者/ 大谷刑部 (37回)-(2017/08/02(Wed) 13:19:57)
No84783 (魔界の仮面弁士 さん) に返信
> ■No84782 (furu さん) に返信
>>「抹消する」ってのは何ですか?
>
> ひとまず、削除する/DELETE する、の書き間違いとでも捉えて頂ければ。m(_ _)m
>
>
> # 大谷刑部さんが HAVING 案を出されていましたが、
> # 実例を思いつかなかったため、断念して別案で回答してしまいました…。

実際のところのやりたいことは質問主さんに書いてもらわないとわかりませんが、
質問の字面だけ見ると、商品ごとの累計(SUM)で判断していいように思えたので、
単にHaving使えば?と書きました。

「直近の2営業日の合計」とか文面にかかれてない影の要件があれば、
単純なHavingではむりです。
当然魔界の仮面弁士の提示したような構文での対応が必要になりますが、
程度問題になりますが、売上データが云千万、云億レコードからの抽出なら、
無理してSQL1本にこだわるとかえって性能を落とします。
カーソルで引っ張って、Loopで判定処理でもいい場合もあり得ます。
質問主さんはもうちょっと環境を具体的に書いた方がいいと思いますね。



引用返信 編集キー/
■84786 / inTopicNo.8)  Re[1]: SQL
□投稿者/ furu (113回)-(2017/08/02(Wed) 14:49:21)
No84769 (yapii さん) に返信

SQLは日進月歩で方言が増えてくので
もしかしたら、便利な機能があるんではないかと
思うことは悪くないと思います。

ローテクで

select c.*
from
    TBL c,
    (
        select 商品コード, max(売上日) 売上日
        from
        (
            select a.商品コード, b.売上日
            from TBL a, TBL b
            where a.商品コード = b.商品コード and a.売上日 >= b.売上日
            group by a.商品コード, b.売上日
            having sum(a.販売数) >= 50
        )
        group by 商品コード
    ) d
where c.商品コード = d.商品コード and c.売上日 >= d.売上日
order by c.商品コード, c.売上日 desc;


引用返信 編集キー/
■84797 / inTopicNo.9)  Re[2]: SQL
□投稿者/ yapii (2回)-(2017/08/02(Wed) 21:50:05)
furu さん

特に日付の指定はありませんので、furuさんのhavingを使用した方法で
希望のデータが取得できました。

魔界の仮面弁士さん
havingのSQLは理解できたのですが、仮面弁士さんのSQLが難しすぎて理解できませんでした。
DELETEでは何をしているのでしょうか?
軽くでいいのでSQLの解説をお願いできないでしょうか


DELETE FROM TBL WHERE ROWID IN (
  WITH W1 AS (
    SELECT No, 売上日, 商品コード, 販売数, SUM(販売数)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC) AS 累計
    FROM TBL 
  ), W2 AS (
    SELECT No, 売上日, 商品コード, 販売数, 累計, LAG(累計, 1, 0)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC) AS 前回累計
    FROM W1
  )
  SELECT ROWID FROM W2 WHERE 前回累計 < 50 
)


引用返信 編集キー/
■84801 / inTopicNo.10)  Re[3]: SQL
□投稿者/ 魔界の仮面弁士 (1376回)-(2017/08/03(Thu) 11:44:13)
No84797 (yapii さん) に返信
> 軽くでいいのでSQLの解説をお願いできないでしょうか

・「ウィンドウ関数」は分かりますか? (OVER 句など)
・「共通テーブル式」は分かりますか? (WITH 句のこと)
・「ROWID」は分かりますか?


> DELETEでは何をしているのでしょうか?

DELETE より前の部分の認識確認ということで、
まずは「W1」部の SQL から見ていきます。



  SELECT
    No, 売上日, 商品コード, 販売数,
    SUM(販売数) OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 累計
  FROM TBL
  ORDER BY NO ASC

上記により、下記の結果が得られます。
 1 2017/01/01 A001  10  80
 2 2017/01/02 A001  30  70
 3 2017/01/03 A001  40  40
 4 2017/01/01 A002 100 100

商品コードごとにグループ化して、日付の降順に加算したものが「累計」列です。

※先の回答とは異なり、上記ではウィンドウ内の順序を
 「ORDER BY 売上日 DESC」から「ORDER BY 売上日 DESC, No DESC」に
 変更しています。
 これは、同一日時の売上が上がってきた場合の順序を定めるために必要です。



さらにその「累計」を、LAG 関数に渡して「前回累計」列を作っています。
これが「W2」の SQL にあたります。

LAG 関数は、その前の行の値を読み取るウィンドウ関数であり、試しに
共通テーブル式(WITH 句のこと)を使わずに書いてみると、こうなります。

  SELECT
    No, 売上日, 商品コード, 販売数, 累計,
    LAG(累計, 1, 0) OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 前回累計
   FROM (
       SELECT
         No, 売上日, 商品コード, 販売数,
         SUM(販売数) OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 累計
       FROM TBL
   ) W1
   ORDER BY No ASC

上記により、下記の結果が得られます。
 1 2017/01/01 A001  10  80 70
 2 2017/01/02 A001  10  70 40
 3 2017/01/03 A001  40  40  0
 4 2017/01/01 A002 100 100  0


こうして得られた「前回累計」が、削除処理の条件として使われます。
この列が 50 未満のものが、今回の削除対象のレコードです。


階層が深くなってきたので、副問い合わせではなく
WITH 句を使って書いてみると、こういう SQL になります。

  WITH W1 AS (
    SELECT No, 売上日, 商品コード, 販売数, SUM(販売数)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 累計
    FROM TBL 
  ), W2 AS (
    SELECT No, 売上日, 商品コード, 販売数, 累計, LAG(累計, 1, 0)
    OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 前回累計
    FROM W1
  )
  SELECT ROWID, W2.* FROM W2 WHERE 前回累計 < 50
  ORDER BY W2.No DESC

※ W2.* は削除には不要ですが、確認のために記載しています)


なお、最終結果に ROWID 擬似列を付与したのは、削除処理のためです。

たとえば、主キーが {売上日, 商品コード} であるとか、
ユニークキーが {No} であるのならば、それを使って削除できたのですが、
最初の質問では、Primary Key / Uniquie Key が何であるか不明だったので
削除行を特定するために ROWID を付与しています。


ということで、最終的な SQL が

   DELETE TBL WHERE ROWID IN (上記問い合わせ) 

となりました。

削除に不要な列も削ぎ落とすとこんな感じです。


  DELETE FROM TBL WHERE ROWID IN (
    WITH W1 AS (
      SELECT No, 売上日, 商品コード, SUM(販売数)
        OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 累計
      FROM TBL 
    ), W2 AS (
      SELECT LAG(累計, 1, 0)
        OVER (PARTITION BY 商品コード ORDER BY 売上日 DESC, No DESC) AS 前回累計
      FROM W1
    )
    SELECT ROWID FROM W2 WHERE 前回累計 < 50
  )

引用返信 編集キー/
■84819 / inTopicNo.11)  Re[4]: SQL
□投稿者/ yapii (3回)-(2017/08/04(Fri) 14:13:01)
魔界の仮面弁士 さん

丁寧に解説していただきありがとうございました。

WITH句は使用したことはありました。
ウインドウ関数は存在こそ知っていましたが、ごく簡単に使用したことがある程度でした。


今回は、はじめてLAG関数というものを知りました。
たとえ知っていたとしても、今回の例に使用すればよいと思いつけなかったと思います。

余談ですが、
LAG関数を教えていただき日頃の勉強不足(…というよりSQL軽視)を反省し
SQLをもっとしっかり使えこなせるようになろうと痛感いたしました。
とりあえず「達人に学ぶ SQL徹底指南書」をポチって来ました


あとデータの取得なので、SELECTでよいと思うのですが、
教えていただいたSQLがDELETEだったので???になってしまいました。




引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ