最新の継続日数を取得するSQLを作ろうとした
このブログは技術記事メインで運用しようとして始めましたが、気付いたら本のアウトプットメインになってました。笑 たまには技術記事を書こうということで今週取り組んでて苦しんだPostgreSQLを使った最新の継続日数取得について書きます。 経緯 今コツコツと取り組んでいる個人開発でGitHubとかで見る最新の継続日数の取得をしたいと考えたところから始まりました。 ちなみにこんな感じのやつです。 Current StreakをSQLで実現しようとしました。 実装 ネットで最新の継続日数を取得するSQLの情報が全然なくどうしようかなと迷っていたら、Latest Number of Consecutive Days(Current Streak)を見つけました。 この記事を見ていると何やらSQL Serverで実現したいことを実装しているっぽい！ということでこれをPostgreSQLに書き換えてみようと試みました。 上記記事で最終的にできてたSQLはこんな感じでした。これをPostgreSQLに変えていきます。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 DECLARE @habits TABLE (ID INT IDENTITY, [Target] INT) INSERT INTO @habits ([Target]) VALUES (1) DECLARE @entries TABLE (ID INT IDENTITY, HabitID INT, EntryDate datetime) INSERT INTO @entries (HabitID, EntryDate) VALUES (1, '20150221'),(1, '20150222'),(1,'20150223') ;WITH distinct_dates AS ( SELECT DISTINCT HabitID, convert(date, dateadd(HOUR, -3, EntryDate)) AS Day FROM @entries ), numbering AS ( SELECT distinct_dates.HabitID, Day, lastDateApply.lastDate, row_number() OVER(PARTITION BY distinct_dates.HabitID ORDER BY Day DESC) AS rowno FROM distinct_dates outer apply (select max(ee.EntryDate) 'lastDate' from @entries ee where ee.habitid = distinct_dates.HabitID) lastDateApply ) SELECT a.HabitID, a.lastDate 'LastEntryDate', DATEDIFF(day, getdate(), a.lastDate) 'DateDifference', case when DATEDIFF(day, getdate(), a.lastDate) = 0 THEN MIN(a.rowno) else DATEDIFF(day, getdate(), a.lastDate) + 1 end 'CurrentStreak' FROM numbering a LEFT JOIN numbering b ON b.HabitID = a.HabitID AND b.rowno = a.rowno + 1 WHERE b.Day IS NULL OR datediff(DAY, b.Day, a.Day) > 1 GROUP BY a.HabitID, a.lastDate で実際にやってみたらこんなSQLができ上がりました。SQL ServerとPostgreSQLで書き方の差があって結構苦労しました。 outer applyの書き換えとかも訳わからなくて疲れました。。 ...