とあるデータを集計したいのですが

Question

以下のようなデータを、集計後のデータにしたい。
年月が連続していた場合（下のデータはでは二月ですが、三月の場合もあり）に合算させたいのですが、ここが上手い具合にグループ化できずに困ってます。
何かよい方法ありませんでしょうか。（アクセスのクエリでやっています）

⚫️集計前データ
No. 年月 数
１００ ２０００１２ １００
１００ ２００１０１ ２００
１００ ２００００４ ４００
２００ ２００００１ ５００
３００ ２０１００１ ６００

⚫️集計後データ
No. 年月 数
１００ ２０００１２ ３００
１００ ２００００４ ４００
２００ ２００００１ ５００
３００ ２０１００１ ６００
or
No. 数
１００ ３００
１００ ４００
２００ ５００
３００ ６００

Answer 1

100 の 2000/12 と 2001/01 が 2000/12 の方に合算されているということから、年月が連続していた場合に、最初の年月のほうへ合算させたい。という理解でよろしいでしょうか？

感覚的に行いたい集計に向かない構造になっているように感じますね。連続性の判定が非常に厄介です。クエリを使って処理するにしても、１回でまとめて処理するのではなく、前処理を行って作業用テーブルにレコードを整えてから集計を行う。など、段階を分けたほうが楽でしょう。

最適化した解決策を出すと理解が難しくなりますし、応用も効きにくいと思いますので、以下１レコード毎にベタな処理をしていく例を挙げておきます。（純粋なクエリだけを使う方法ではありません）

---

仮に以下のようなカラムを持つ集計補助テーブルを準備し、初期化しておく。

No, 年月, 数, 年月連番, グループ化項目

集計対象レコードを並び順を考慮して列挙

SELECT No, 年月, 数
  FROM 集計前データ
 ORDER BY No, 年月

ここから、得られた全レコードをループを使って処理していく

『年月』の連続性を判定したい場合、例えば、一つの考え方として『(年 * 12) + 月』のような式で
年月だけの数列を作れば判定が簡単にできます。このように何らかの判定項目を用意すべきでしょう。
ここで計算した値を『年月連番』としておきます。
元の年月が文字列なら部分的に取り出して数値として計算すると求められるでしょう。
年月が数値なら、INT と MOD 関数辺りを併用すれば計算できるはずです。

次に、直前に処理したレコードの No と 年月連番 + 1 が今のレコードと一致しているかどうかを判定。
一致していない：独立あるいは先頭のレコードであるため『グループ化項目』は自分の年月を設定
一致している：連続したレコードであるため、直前の『グループ化項目』を引き継ぐ

集計補助テーブルに今準備したレコード内容を登録。

今処理したレコードは直前に処理したレコードとして内容を保持。

ループの終端、次のレコードが無くなるまで処理を続ける。

ここまでの流れで、示されている集計前データを処理すれば、以下の内容が準備できるはずです。

集計補助テーブル
No, 年月, 数, 年月連番, グループ化項目
１００ ２００００４ ４００ ２４００４ ２００００４
１００ ２０００１２ １００ ２４０１２ ２０００１２
１００ ２００１０１ ２００ ２４０１３ ２０００１２
２００ ２００００１ ５００ ２４００１ ２００００１
３００ ２０１００１ ６００ ２４１２１ ２０１００１

この結果に対して以下のように集計をかければ、あとは並び順を調整するぐらいでしょうか？

SELECT No, グループ化項目 AS 年月, SUM(数) AS 数
  FROM 集計補助テーブル
 GROUP BY No, グループ化項目
 ORDER BY No, グループ化項目

以上、ご参考になれば幸いです。

Answer 2

ps13zierさんがご指摘の通り、今回の集計には不向きなテーブル構造ですね。

これを無理矢理にでも集計するのであれば、Access利用とのことなので中間クエリをいくつか作成すれば実現は可能です。（さらにゴリ押しすれば中間クエリなしでもいけそうですが。）
なお、以下のサンプルは最大連続３か月までの合算しか考慮していません。
また、集計元テーブル名はWK、カラム名はそれぞれNO, YM, SUとして作成しています。

クエリ① ：集計テーブルのYYYYMM形式の年月から、前月・当月・翌月・翌々月を生成する。

select
    WK.[NO]
  , WK.YM
  , WK.SU
  , CDATE(left ([ym], 4) & "/" & right ([ym], 2) & "/01") as YMD
  , DATEADD("m" , -1, CDATE(left ([ym], 4) & "/" & right ([ym], 2) & "/01") ) as PREV1
  , DATEADD("m" ,  1, CDATE(left ([ym], 4) & "/" & right ([ym], 2) & "/01") ) as NEXT1
  , DATEADD("m" ,  2, CDATE(left ([ym], 4) & "/" & right ([ym], 2) & "/01") ) as NEXT2
from WK; 

クエリ② ：クエリ①を元に、前月データの有無、翌月・翌々月の数量の取得する。

select
    W1.[NO]
  , W1.YM
  , W1.SU
  , P1.[NO] as PREV1_FLG
  , N1.SU   as NEXT1_SU
  , N2.SU   as NEXT2_SU
from 
( ( ( WK_TEMP1 W1
     left join WK_TEMP1 as P1 
            on W1.[NO]  = P1.[NO] 
           and W1.PREV1 = P1.YMD
    )
     left join WK_TEMP1 as N1 
            on W1.[NO]  = N1.[NO] 
           and W1.NEXT1 = N1.YMD
  )
     left join WK_TEMP1 as N2 
            on W1.[NO]  = N2.[NO] 
           and W1.NEXT2 = N2.YMD 
);

クエリ③ ：クエリ②を元に、前月データがある場合は抽出対象外、それ以外は連続する月分の数量を合算する。

select
    W2.[NO]
  , W2.YM
  , IIF( W2.PREV1_FLG is null
       , W2.SU + NZ(W2.NEXT1_SU, 0) + IIF(W2.NEXT1_SU is null, 0, NZ(W2.NEXT2_SU, 0))
       , 0
       ) as SU_TOTAL 
from
  WK_TEMP2 as W2 
where
  W2.PREV1_FLG is null; 

こんな感じでかなり無理矢理感がありますが、やればできなくはないです。
参考までに。