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

■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 
)

■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;

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 
)

■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
  )