同じ値が何回連続しているか数える

f:id:accs2014:20180428101211p:plain:right:w200

 順番を持つデータにおいて、ある列に同じ値が何回連続で出現しているのかをカウントする方法についてです。
 例として天気を記録しているこのようなテーブルで、同じ天気が何回続いているのかを求めます。
 同じ値がたびたび現れますので、単純に「天気」列でグループ化してしまうと、値が連続していないレコードもすべて一緒になってしまいうまくいきません。
 もう一つ、この例においては日付が必ずしも連続していないことに注意してください。日付が連続していれば簡単に前日との比較ができますので少しは楽にできそうに思えますが…しかし、実はそのような(値が連続する)列は必要ありません。以下でその理由がわかるように、具体的な方法を示します。



DMax関数を用いて複数のクエリで求める方法

 まずはわかりやすさ優先の手順です。
 考え方としては次のようになります。

  1. 各レコードにおいて「『その日より前の日』で、かつ、『その日とは天気が異なる日』のうち、直近の(最も新しい)日付」を求める
  2. 1で求めた値をもとにグループ化する

 1で求めた値は、同じ天気が連続する限りすべてのレコードで同じものとなります。そして別の天気になれば別の値となりますし、また元の天気に戻ったときにはさらに別の値になります。よって、あとはこの値に基づきグループ化してカウントすればよい、というわけです。

f:id:accs2014:20180428101208p:plain:right:w400

 具体的にこのようなクエリを設けます。
 上記の値は次のような列で求められます。



直近別天気日: DMax("日付","天気テーブル","天気<>'" & [天気] & "' and 日付<#" & [日付] & "#")


f:id:accs2014:20180428113029p:plain:right:w250

 データシートビューです。
 最初にNullが現れますが、これが気になる場合は上記列にNz関数を加えて適宜変換してください。


f:id:accs2014:20180428101202p:plain:right:w400

 そしてもう一つ別のクエリを作ります。上記クエリをもとにグループ化して値をカウントします。ついでにですが連続する天気の開始日と終了日も求めています。


f:id:accs2014:20180428101159p:plain:right:w400

 データシートビューです。めでたしめでたし。


SQLで求める方法

f:id:accs2014:20180428120707p:plain:right:w400


 SQL一発で求めます。考え方は上記例と全く同じですので、あとは気合でGo。



SELECT x.直近別天気日, x.天気, Min(x.日付) AS 開始日, Max(x.日付) AS 終了日, Count(x.天気) AS 連続回数
FROM (SELECT 天気テーブル.日付, 天気テーブル.天気, (SELECT Max(日付) FROM 天気テーブル AS 天気テーブル_1 WHERE 天気テーブル_1.天気<>天気テーブル.天気 AND 天気テーブル_1.日付<天気テーブル.日付) AS 直近別天気日 FROM 天気テーブル)  AS x
GROUP BY x.直近別天気日, x.天気
ORDER BY x.直近別天気日;


f:id:accs2014:20180428120705p:plain:right:w250

 データシートビューです。