【postgresql】1週間分のユーザー数を集計する方法

やりたいデータ集計

1週間のデータを集計した結果を出力したい。

ただし、データは歯抜けのこともある。

登録されているデータイメージ。

これを2023/3/7日を実行日とした場合、当日から過去1週間分のデータを取得して以下のように表示させたいとする

Aは期間対象外なので、外す。

3/3は、Bが1件。

3/4は、C、Dで2件。

他はデータなしなので、0件とする。

SQL文を考える

データテーブルを仮に「test_table」とする。

そうした場合、単純に考えると以下のようSQLになる。

SELECT
	count(id) AS count,
	date
FROM
	test_table
WHERE
	AND date > now() - interval '7day'
	AND date < now()
GROUP BY
	date
ORDER BY
	date

日付をGroup byして、カウントするだけ。

Whereに条件である「過去1週間」も入れてる状態。

ただ、これだけだと歯抜け状態になってしまう。

2023/3/1~3/7を基準としてテーブルを作成して、上のデータをleft joinできれば解決できるはず。

ということで、「generate_series」を使用する！

これを使えば、仮テーブルを作れる。

例えば、

SELECT * FROM generate_series(2,4);

とすれば、2~4までの仮テーブルが作れる。

これを応用して、3/1~3/7までの1週間分の仮テーブルを作成する。

完成したSQL

case文で、データ取得できない場合のデフォルト値を0に設定。

これで、データがない日は0が入り想定通りのデータが返却されるようになる！

SELECT generate_series::date as date,
		case when test.count is not null then test.count
		else 0
		end
FROM generate_series(now()::DATE- interval '7day', now(), '1 days')
	LEFT JOIN (
		SELECT
			count(id) AS count,
			date
		FROM
			test_table
		WHERE
			AND event_date > now()::DATE - interval '7day'
			AND event_date < now()::DATE
		GROUP BY
			date
		ORDER BY
			date
	)as test  on test.date = generate_series.date

まとめ

気になる点は、explainしたとき総costが16くらいだった。

10件満たないデータでこれだったので、1週間で10000件とかだと重くなるのかなぁ。。。

group byとかleft joinは重くなる傾向があるので注意。