例として、スタックオーバーフローを取り上げます。 彼は特定の成果に対する報酬を伴うクールな評判システムを使用しています。 多くのソーシャルプロジェクトと同様に、彼らはユーザーが毎日リソースにアクセスすることを奨励しています。 次の2つの賞に注意してください。
それらの意味を理解するのは簡単です。 初日にサイトをご覧ください。 それから二日目。 その後、3番目に（おそらく数回、それは重要ではありません）。 4番目にログインしていませんか？ 再び数え始めます。
SQLでこれを追跡する方法は？
データにアクセスするには、 Stack Exchange Data Explorerを使用します。
この情報は提供されないため、訪問の日付はリクエストしません。 代わりに、ユーザーが投稿した投稿の日付をリクエストしましょう。
SQL Serverはデータベースとして使用されるため、次のクエリを使用できます。
SELECT DISTINCT CAST(CreationDate AS DATE) AS date FROM Posts WHERE OwnerUserId = ##UserId## ORDER BY 1
...これは次のようになります：
date ---------- 2010-11-26 2010-11-27 2010-11-29 2010-11-30 2010-12-01 2010-12-02 2010-12-03 2010-12-05 2010-12-06 2010-12-07 2010-12-08 2010-12-09 2010-12-13 2010-12-14 ... (769 rows)
（ここで自分でリクエストすることができます）
ご覧のとおり、いくつかの省略があります。
date -------------------------------------- 2010-11-26 2010-11-27 <---- 2 2010-11-29 2010-11-30 2010-12-01 2010-12-02 2010-12-03 <---- 5 2010-12-05 2010-12-06 2010-12-07 2010-12-08 2010-12-09 <---- 5 2010-12-13 2010-12-14 ...
人は、連続した日付が何日も隙間なく続くかを簡単に確認できます。 しかし、SQLでそれを行う方法は？
タスクを簡素化するために、一般化されたテーブル式で個々のクエリを「保存」しましょう。 以前のリクエスト日を呼び出します ：
WITH -- This table contains all the distinct date -- instances in the data set dates(date) AS ( SELECT DISTINCT CAST(CreationDate AS DATE) FROM Posts WHERE OwnerUserId = ##UserId## ) ...
受け取ったリクエストの目標は、すべての連続した日付を同じグループに入れて、それらを結合できるようにすることです。 方法は次のとおりです。
SELECT COUNT(*) AS consecutiveDates, MIN(week) AS minDate, MAX(week) AS maxDate FROM groups GROUP BY grp -- This "grp" value will be explained later ORDER BY 1 DESC, 2 DESC
各grpグループを組み合わせて、グループ内の日付の数をカウントし、各グループの最小値と最大値を見つけます。
連続した日付のグループを作成する
次に、クエリの結果を見てみましょう。明確にするために、日付の省略に関係なく行に番号を付けます。
row number date -------------------------------- 1 2010-11-26 2 2010-11-27 3 2010-11-29 <-- 4 2010-11-30 5 2010-12-01 6 2010-12-02 7 2010-12-03 8 2010-12-05 <--
ご覧のとおり、日付間にギャップがあるという事実（2つの日付は連続していない）に関係なく、行番号は連続しています。 これを行うには、ROW_NUMBER（）関数を使用します。
SELECT ROW_NUMBER() OVER (ORDER BY date) AS [row number], date FROM dates
次に、この興味深いクエリを見てみましょう。
WITH -- This table contains all the distinct date -- instances in the data set dates(date) AS ( SELECT DISTINCT CAST(CreationDate AS DATE) FROM Posts WHERE OwnerUserId = ##UserId## ), -- Generate "groups" of dates by subtracting the -- date's row number (no gaps) from the date itself -- (with potential gaps). Whenever there is a gap, -- there will be a new group groups AS ( SELECT ROW_NUMBER() OVER (ORDER BY date) AS rn, dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp, date FROM dates ) SELECT * FROM groups ORDER BY rn
上記のクエリにより、次の結果が得られます。
rn grp date --- ---------- ---------- 1 2010-11-25 2010-11-26 2 2010-11-25 2010-11-27 3 2010-11-26 2010-11-29 4 2010-11-26 2010-11-30 5 2010-11-26 2010-12-01 6 2010-11-26 2010-12-02 7 2010-11-26 2010-12-03 8 2010-11-27 2010-12-05 9 2010-11-27 2010-12-06 10 2010-11-27 2010-12-07 11 2010-11-27 2010-12-08 12 2010-11-27 2010-12-09 13 2010-11-30 2010-12-13 14 2010-11-30 2010-12-14
（ここで自分でリクエストすることができます）
私たちがしたことは、新しいgrp日付を取得するために、その日から行番号を引くことだけでした。 この方法で取得された日付は意味がなく、単なる補助的な値です。
ただし、連続する日付の場合、「grp」の値が同じになることを保証できます。これは、連続するすべての日付について、次の2つの式が真になるためです
date2 - date1 = 1 // rn2 - rn1 = 1 //
一貫性のない日付の場合、行番号の差も1になりますが、日数の差は複数になります。 グループを簡単に区別できるようになりました：
rn grp date --- ---------- ---------- 1 2010-11-25 2010-11-26 2 2010-11-25 2010-11-27 3 2010-11-26 2010-11-29 4 2010-11-26 2010-11-30 5 2010-11-26 2010-12-01 6 2010-11-26 2010-12-02 7 2010-11-26 2010-12-03 8 2010-11-27 2010-12-05 9 2010-11-27 2010-12-06 10 2010-11-27 2010-12-07 11 2010-11-27 2010-12-08 12 2010-11-27 2010-12-09 13 2010-11-30 2010-12-13 14 2010-11-30 2010-12-14
したがって、最終的な要求は次のようになります。
WITH -- This table contains all the distinct date -- instances in the data set dates(date) AS ( SELECT DISTINCT CAST(CreationDate AS DATE) FROM Posts WHERE OwnerUserId = ##UserId## ), -- Generate "groups" of dates by subtracting the -- date's row number (no gaps) from the date itself -- (with potential gaps). Whenever there is a gap, -- there will be a new group groups AS ( SELECT ROW_NUMBER() OVER (ORDER BY date) AS rn, dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp, date FROM dates ) SELECT COUNT(*) AS consecutiveDates, MIN(week) AS minDate, MAX(week) AS maxDate FROM groups GROUP BY grp ORDER BY 1 DESC, 2 DESC
そしてその結果：
consecutiveDates minDate maxDate ---------------- ------------- ------------- 14 2012-08-13 2012-08-26 14 2012-02-03 2012-02-16 10 2013-10-24 2013-11-02 10 2011-05-11 2011-05-20 9 2011-06-30 2011-07-08 7 2012-01-17 2012-01-23 7 2011-06-14 2011-06-20 6 2012-04-10 2012-04-15 6 2012-04-02 2012-04-07 6 2012-03-26 2012-03-31 6 2011-10-27 2011-11-01 6 2011-07-17 2011-07-22 6 2011-05-23 2011-05-28 ...
（ここで自分でリクエストすることができます）
ボーナス：週の順序を見つける
私たちが日々を使用したという事実は、私たちの選択に過ぎません。 正確な時間を取り、CAST関数を使用してその日を丸めました：
SELECT DISTINCT CAST(CreationDate AS DATE)
たとえば、数週間からシーケンスを知りたい場合、時間を数週間に丸めることができます。
SELECT DISTINCT datepart(year, CreationDate) * 100 + datepart(week, CreationDate)
このクエリは、年と週の数値表現を使用し、2015年の第3週にタイプ201503の数値を作成します。 残りのリクエストは変更されません。
WITH weeks(week) AS ( SELECT DISTINCT datepart(year, CreationDate) * 100 + datepart(week, CreationDate) FROM Posts WHERE OwnerUserId = ##UserId## ), groups AS ( SELECT ROW_NUMBER() OVER (ORDER BY week) AS rn, dateadd(day, -ROW_NUMBER() OVER (ORDER BY week), week) AS grp, week FROM weeks ) SELECT COUNT(*) AS consecutiveWeeks, MIN(week) AS minWeek, MAX(week) AS maxWeek FROM groups GROUP BY grp ORDER BY 1 DESC, 2 DESC
そして、ここに私たちが得るものがあります：
consecutiveWeeks minWeek maxWeek ---------------- ------- ------- 45 201401 201445 29 201225 201253 25 201114 201138 23 201201 201223 20 201333 201352 16 201529 201544 15 201305 201319 12 201514 201525 12 201142 201153 9 201502 201510 7 201447 201453 7 201321 201327 6 201048 201053 4 201106 201109 3 201329 201331 3 201102 201104 2 201301 201302 2 201111 201112 1 201512 201512
（ここで自分でリクエストすることができます）
当然のことながら、著者はStack Overflowで定期的に執筆しているため、連続した週ははるかに長い範囲にわたっています。