fix: tweak retention rate aggregation

5 files changed, 43 insertions, 12 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8686693397..27d72388ac 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -40,6 +40,7 @@ You should also include the user name that made the change.
 - fix(frontend): Safariでプラグインが複数ある場合に正常に読み込まれない問題を修正
 - Bookwyrmのユーザーのプロフィールページで「リモートで表示」をタップしても反応がない問題を修正
 - `disableCache: true`を設定している場合に絵文字管理操作でエラーが出る問題を修正
+- リテンション分析が上手く機能しないことがあるのを修正
 
 ## 13.9.2 (2023/03/06)
 
diff --git a/packages/backend/migration/1678869617549-retention-date-key.js b/packages/backend/migration/1678869617549-retention-date-key.js
new file mode 100644
index 0000000000..1a31b9a750
--- /dev/null
+++ b/packages/backend/migration/1678869617549-retention-date-key.js
@@ -0,0 +1,14 @@
+export class retentionDateKey1678869617549 {
+    name = 'retentionDateKey1678869617549'
+
+    async up(queryRunner) {
+			await queryRunner.query(`TRUNCATE TABLE "retention_aggregation"`, undefined);
+        await queryRunner.query(`ALTER TABLE "retention_aggregation" ADD "dateKey" character varying(512) NOT NULL`);
+        await queryRunner.query(`CREATE UNIQUE INDEX "IDX_f7c3576b37bd2eec966ae24477" ON "retention_aggregation" ("dateKey") `);
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`DROP INDEX "public"."IDX_f7c3576b37bd2eec966ae24477"`);
+        await queryRunner.query(`ALTER TABLE "retention_aggregation" DROP COLUMN "dateKey"`);
+    }
+}
diff --git a/packages/backend/src/models/entities/RetentionAggregation.ts b/packages/backend/src/models/entities/RetentionAggregation.ts
index c79b762d71..c7bf38b3af 100644
--- a/packages/backend/src/models/entities/RetentionAggregation.ts
+++ b/packages/backend/src/models/entities/RetentionAggregation.ts
@@ -18,6 +18,12 @@ export class RetentionAggregation {
 	})
 	public updatedAt: Date;
 
+	@Index({ unique: true })
+	@Column('varchar', {
+		length: 512, nullable: false,
+	})
+	public dateKey: string;
+
 	@Column({
 		...id(),
 		array: true,
diff --git a/packages/backend/src/queue/processors/AggregateRetentionProcessorService.ts b/packages/backend/src/queue/processors/AggregateRetentionProcessorService.ts
index 02324c6cd4..fcfba75909 100644
--- a/packages/backend/src/queue/processors/AggregateRetentionProcessorService.ts
+++ b/packages/backend/src/queue/processors/AggregateRetentionProcessorService.ts
@@ -7,6 +7,7 @@ import { bindThis } from '@/decorators.js';
 import type { RetentionAggregationsRepository, UsersRepository } from '@/models/index.js';
 import { deepClone } from '@/misc/clone.js';
 import { IdService } from '@/core/IdService.js';
+import { isDuplicateKeyValueError } from '@/misc/is-duplicate-key-value-error.js';
 import { QueueLoggerService } from '../QueueLoggerService.js';
 import type Bull from 'bull';
 
@@ -49,13 +50,21 @@ export class AggregateRetentionProcessorService {
 		});
 		const targetUserIds = targetUsers.map(u => u.id);
 
-		await this.retentionAggregationsRepository.insert({
-			id: this.idService.genId(),
-			createdAt: now,
-			updatedAt: now,
-			userIds: targetUserIds,
-			usersCount: targetUserIds.length,
-		});
+		try {
+			await this.retentionAggregationsRepository.insert({
+				id: this.idService.genId(),
+				createdAt: now,
+				updatedAt: now,
+				dateKey,
+				userIds: targetUserIds,
+				usersCount: targetUserIds.length,
+			});
+		} catch (err) {
+			if (isDuplicateKeyValueError(err)) {
+				this.logger.succ('Skip because it has already been processed by another worker.');
+				done();
+			}
+		}
 
 		// 今日活動したユーザーを全て取得
 		const activeUsers = await this.usersRepository.findBy({
diff --git a/packages/frontend/src/components/MkRetentionHeatmap.vue b/packages/frontend/src/components/MkRetentionHeatmap.vue
index 8326ec7ef3..85c009f746 100644
--- a/packages/frontend/src/components/MkRetentionHeatmap.vue
+++ b/packages/frontend/src/components/MkRetentionHeatmap.vue
@@ -36,9 +36,11 @@ async function renderChart() {
 	const wide = rootEl.offsetWidth > 600;
 	const narrow = rootEl.offsetWidth < 400;
 
-	const maxDays = wide ? 15 : narrow ? 5 : 10;
+	const maxDays = wide ? 10 : narrow ? 5 : 7;
 
-	const raw = await os.api('retention', { });
+	let raw = await os.api('retention', { });
+
+	raw = raw.slice(0, maxDays);
 
 	const data = [];
 	for (const record of raw) {
@@ -60,10 +62,9 @@ async function renderChart() {
 	const color = defaultStore.state.darkMode ? '#b4e900' : '#86b300';
 
 	// 視覚上の分かりやすさのため上から最も大きい3つの値の平均を最大値とする
-	//const max = raw.readWrite.slice().sort((a, b) => b - a).slice(0, 3).reduce((a, b) => a + b, 0) / 3;
-	const max = 4;
+	const max = raw.map(x => x.users).slice().sort((a, b) => b - a).slice(0, 3).reduce((a, b) => a + b, 0) / 3;
 
-	const marginEachCell = 6;
+	const marginEachCell = 12;
 
 	chartInstance = new Chart(chartEl, {
 		type: 'matrix',