データベース

データベースに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Microsoft SQL Server)
LAG関数について
投稿日時: 16/12/06 11:30:18
投稿者: 富山の初心者

おねがいします。
 
AA_MST:前月末在庫数マスタ
AA_TRN:当月入出庫データ があります。
ほしいのは、各入出庫時点での在庫数です。
下記にデータ例を示します。
 
AA_MST
ID1    月末在庫数
AA    10,000
BB    20,000
 
AA_TRN            
ID1    日付       入庫数    出庫数
AA    20160101     100      1
AA    20160102     200      2
AA    20160103     400      4
AA    20160104     800      8
BB    20160201    1,000     16
BB    20160202    2,000     32
BB    20160203    4,000     64
 
ほしい結果                                            
ID1    日付      入庫数    出庫数    月末在庫数    時点在庫数                        
AA    20160101     100     1    10,000    10,099    ←前行+入庫数-出庫数
AA    20160102     200     2    10,000    10,297     (ID1が変わればAA_MST.月末在庫数より)
AA    20160103     400     4    10,000    10,693                        
AA    20160104     800     8    10,000    11,485                        
BB    20160201    1,000    16    20,000    20,984                        
BB    20160202    2,000    32    20,000    22,952                        
BB    20160203    4,000    64    20,000    26,888                        
                                            
下記のSQLのLAG関数の部分の記述を教えてください
SELECT V_TBL.ID1    
      ,V_TBL.日付    
      ,V_TBL.入庫数    
      ,V_TBL.出庫数    
      ,V_TBL.月末在庫数    
      ,LAG(時点在庫数,1,V_TBL.月末在庫数) OVER(PARTITION BY ID1 ORDER BY V_TBL.ID1,V_TBL.日付) + V_TBL.入庫数 - V_TBL.出庫数 時点在庫数    
  FROM (    
         SELECT AA_TRN.ID1    
               ,AA_TRN.日付    
               ,AA_TRN.入庫数    
               ,AA_TRN.出庫数    
               ,AA_MST.月末在庫数    
           FROM AA_TRN    
           LEFT JOIN AA_MST ON AA_TRN.ID1 = AA_MST.ID1    
       ) V_TBL    
 ORDER BY V_TBL.ID1,V_TBL.日付    
 
上記sqlにお実行結果は
ID1    日付    入庫数    出庫数    月末在庫数    時点在庫数
AA    20160101    100     1     10,000     10,099
AA    20160102    200     2     10,000     10,198
AA    20160103    400     4     10,000     10,396
AA    20160104    800     8     10,000     10,792
BB    20160201    1,000     16     20,000     20,984
BB    20160202    2,000     32     20,000     21,968
BB    20160203    4,000     64     20,000     23,936
となり、時点在庫数は前月末在庫数+「当日」の入出庫分であり、「当日まで」の入出庫分ではありません。
「当日まで」とするにはどのようなSQLにするのがよいのでしょうか。
また、もっと簡易な方法があれば、それも教えてください。
 
よろしくお願いします。
 
 
 
 
 
 
 

投稿日時: 16/12/06 12:18:15
投稿者: 富山の初心者

訂正です。
 
正しくできなかったSQLの修正です。
 
修正前
      ,LAG(時点在庫数,1,V_TBL.月末在庫数) 〜
 
修正後
      ,LAG(月末在庫数,1,V_TBL.月末在庫数) 〜
 
よろしく、お願いします

回答
投稿日時: 16/12/06 13:45:31
投稿者: sk

引用:
下記のSQLのLAG関数の部分の記述を教えてください

引用:
時点在庫数は前月末在庫数+「当日」の入出庫分であり、「当日まで」の入出庫分ではありません。
「当日まで」とするにはどのようなSQLにするのがよいのでしょうか。

それは LAG 関数の使いどころではなく
SUM 関数の使いどころです。
 
引用:
LAG(月末在庫数,1,V_TBL.月末在庫数)
   OVER(PARTITION BY ID1
        ORDER BY V_TBL.ID1,V_TBL.日付)
+ V_TBL.入庫数 - V_TBL.出庫数 時点在庫数

V_TBL.月末在庫数
+ SUM(V_TBL.入庫数 - V_TBL.出庫数)
     OVER(PARTITION BY ID1
          ORDER BY V_TBL.日付) 時点在庫数

投稿日時: 16/12/06 15:42:37
投稿者: 富山の初心者

sk 様 ありがとうございました。
 
「前行」にこだわりすぎていたみたいですね。
下記のシンプルなSQLで想定していた結果となりました。
 
「OVER(PARTITION BY 〜 ORDER BY 〜)」には、
LAG/DENSE_RANKなどと併用するものだと、思い込んでいました。
 
SELECT AA_TRN.ID1    
      ,AA_TRN.日付    
      ,AA_TRN.入庫数    
      ,AA_TRN.出庫数    
      ,AA_MST.月末在庫数    
      ,AA_MST.月末在庫数 + SUM(AA_TRN.入庫数 - AA_TRN.出庫数) OVER(PARTITION BY AA_TRN.ID1 ORDER BY AA_TRN.ID1,AA_TRN.日付) 時点在庫数    
  FROM AA_TRN
  LEFT JOIN AA_MST ON AA_TRN.ID1 = AA_MST.ID1    
 ORDER BY AA_TRN.ID1,AA_TRN.日付    
 
ありがとうございました。