get-current-streak-postgresql

このブログは技術記事メインで運用しようとして始めましたが、気付いたら本のアウトプットメインになってました。笑
たまには技術記事を書こうということで今週取り組んでて苦しんだ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の書き換えとかも訳わからなくて疲れました。。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

WITH distinct_dates AS (
SELECT DISTINCT ON (e.HabitID, CAST(e.EntryDate AS DATE)) e.HabitID, CAST(e.EntryDate AS DATE) AS Day FROM @entries e
), numbering AS (
SELECT dd.HabitID, dd.Day, lastDateApply.lastDate AS lastDate, ROW_NUMBER() OVER(PARTITION BY dd.HabitID ORDER BY dd.Day DESC) AS rowno
from distinct_dates dd 
LEFT JOIN LATERAL (select CAST(max(ee.EntryDate) AS DATE) lastDate FROM @entries ee WHERE ee.HabitID = dd.HabitID FETCH FIRST 1 ROW ONLY) lastDateApply ON TRUE
)

SELECT a.HabitID, a.lastDate AS LastEntryDate, CURRENT_DATE - a.lastDate AS DateDifference,
CASE WHEN CURRENT_DATE - a.lastDate = 0 THEN MIN(a.rowno) ELSE CURRENT_DATE - a.lastDate + 1 END AS CurrentStreak
FROM numbering a
LEFT OUTER JOIN numbering b ON b.HabitID = a.HabitID AND b.rowno = a.rowno + 1 
WHERE b.Day IS NULL OR b.Day - a.Day > 1
GROUP BY a.HabitID, a.lastDate;

で結果どうなったのか

SQLの書き換えをしてこれを自分の個人開発のテーブルに合わせてみましたが、「今日のデータがあったときに今まで投稿したデータを日付毎にまとめてその日付のトータルを返す」って感じにしかなりませんでした。。 今回求めたかったのは最新の継続日数だったのでちょっと違うなーとなりまして、SQLで頑張るのはもうやめました。（SQLしっかり理解している人なら簡単なのかなこれは）

こういった実装はSQLで基本せずフロントに返したデータを元にJavaScriptでいい感じにするのが楽だし管理もしやすいなと思ったのでそっち路線に変更しました。 ついつい絶対SQLで解いてやると頑固に取り組んでいましたが、頑固も考えものです。