snapshots.ts•4.96 kB
import { and, asc, desc, eq, like, or, sql, gte, lte } from "drizzle-orm";
import { db } from "../../index";
import { snapshots, snapshotsMonthly, snapshotsWeekly, apps } from "../../schema";
export const snapshotsDataAccess = {
/**
* 获取指定应用过去一年中的stars数据，按月分组
* 返回一个数组，每个元素包含year, month, stars, delta
*/
findStarsByAppIdOfLastYear: async (repoId: string): Promise<{ year: number; month: number; stars: number; delta: number }[]> => {
if (!repoId || repoId.trim() === "") {
return [];
}
// 查询过去一年的月度快照数据
const monthlySnapshotsData = await db
.select({
year: snapshotsMonthly.year,
month: snapshotsMonthly.month,
stars: snapshotsMonthly.stars,
})
.from(snapshotsMonthly)
.where(eq(snapshotsMonthly.repoId, repoId))
.orderBy(asc(snapshotsMonthly.year), asc(snapshotsMonthly.month))
.limit(12);
// 转换为数组并计算delta
const result = monthlySnapshotsData
.map((item, index, array) => {
// 计算delta：当前月的stars减去上个月的stars
const delta = index > 0 ? (item.stars ?? 0) - (array[index - 1]?.stars ?? 0) : 0;
return {
year: item.year,
month: item.month,
stars: item.stars ?? 0,
delta,
};
});
console.info("[snapshots.ts] [findStarsByAppIdOfLastYear] result", result, monthlySnapshotsData);
return result;
},
/**
* 获取指定应用的trends数据，用于计算不同时间段的平均增长
* 使用更高效的方法，基于year/month/day/week字段而不是createdAt
*/
getProjectTrends: async (repoId: string): Promise<{ daily: number; weekly: number; monthly: number; yearly: number }> => {
if (!repoId || repoId.trim() === "") {
return { daily: 0, weekly: 0, monthly: 0, yearly: 0 };
}
const now = new Date();
const currentYear = now.getFullYear();
const currentMonth = now.getMonth() + 1; // getMonth() 返回 0-11
// 计算上月的日期
const lastMonth = new Date(now);
lastMonth.setMonth(lastMonth.getMonth() - 1);
const lastMonthYear = lastMonth.getFullYear();
const lastMonthMonth = lastMonth.getMonth() + 1;
// 计算去年最后一个月
const lastYear = currentYear - 1;
const lastYearLastMonth = 12;
// 获取今日数据
const last2DaysSnapshot = await db
.select({ stars: snapshots.stars })
.from(snapshots)
.where(
and(
eq(snapshots.repoId, repoId),
eq(snapshots.year, currentYear),
eq(snapshots.month, currentMonth),
)
)
.orderBy(desc(snapshots.year), desc(snapshots.month), desc(snapshots.day))
.limit(2);
// 获取最近2周数据
const current2WeekSnapshot = await db
.select({ stars: snapshotsWeekly.stars })
.from(snapshotsWeekly)
.where(
and(
eq(snapshotsWeekly.repoId, repoId),
eq(snapshotsWeekly.year, currentYear),
)
)
.orderBy(desc(snapshotsWeekly.year), desc(snapshotsWeekly.week))
.limit(2);
// 获取上月数据
const lastMonthSnapshot = await db
.select({ stars: snapshotsMonthly.stars })
.from(snapshotsMonthly)
.where(
and(
eq(snapshotsMonthly.repoId, repoId),
eq(snapshotsMonthly.year, lastMonthYear),
eq(snapshotsMonthly.month, lastMonthMonth)
)
)
.orderBy(desc(snapshotsMonthly.createdAt))
.limit(1);
// 获取去年最后一个月数据
const lastYearLastMonthSnapshot = await db
.select({ stars: snapshotsMonthly.stars })
.from(snapshotsMonthly)
.where(
and(
eq(snapshotsMonthly.repoId, repoId),
eq(snapshotsMonthly.year, lastYear),
eq(snapshotsMonthly.month, lastYearLastMonth)
)
)
.orderBy(desc(snapshotsMonthly.createdAt))
.limit(1);
// 计算各个时间段的增长
const todayStars = last2DaysSnapshot.length >= 1 ? (last2DaysSnapshot[0]?.stars ?? 0) : 0;
const yesterdayStars = last2DaysSnapshot.length >= 2 ? (last2DaysSnapshot[1]?.stars ?? 0) : 0;
const currentWeekStars = current2WeekSnapshot.length >= 1 ? (current2WeekSnapshot[0]?.stars ?? 0) : 0;
const lastWeekStars = current2WeekSnapshot.length >= 2 ? (current2WeekSnapshot[1]?.stars ?? 0) : 0;
const lastMonthStars = lastMonthSnapshot[0]?.stars ?? 0;
const lastYearLastMonthStars = lastYearLastMonthSnapshot[0]?.stars ?? 0;
return {
daily: todayStars - yesterdayStars, // 今日与昨日数据之差
weekly: currentWeekStars - lastWeekStars, // 本周与上周数据之差
monthly: todayStars - lastMonthStars, // 今日与上月数据之差
yearly: todayStars - lastYearLastMonthStars, // 今日与去年最后一个月数据之差
};
},
};