werun-wxapp/nova-werun/service/day.js

const req = require('../../utils/request');
const company = getApp().globalData.company;

async function getConsecutiveSignIns(params) {
    let { userId } = params; // 从参数中获取用户ID

    // 构建 SQL 查询
    let sql = `
        WITH ranked_sign_ins AS (
            SELECT
                "user",
                "createdAt",
                DATE("createdAt") - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY "user" ORDER BY "createdAt") AS grp,
                (CASE WHEN DATE("createdAt") = CURRENT_DATE OR (DATE("createdAt") + INTERVAL '1 day') = CURRENT_DATE THEN TRUE ELSE FALSE END) "isToday"
            FROM
                "EventLog"
            WHERE "user" = '${userId}'
        ),
        continuous AS (
            SELECT "user", grp
            FROM ranked_sign_ins
            WHERE "isToday" = TRUE
            GROUP BY "user", grp
            LIMIT 1
        ),
        grouped_sign_ins AS (
            SELECT
                ranked_sign_ins."user",
                ranked_sign_ins.grp,
                COUNT(*) AS consecutive_days
            FROM ranked_sign_ins
            LEFT JOIN continuous 
            ON continuous.grp = ranked_sign_ins.grp AND continuous."user" = ranked_sign_ins."user"
            WHERE continuous.grp IS NOT NULL
            GROUP BY ranked_sign_ins."user", ranked_sign_ins.grp
        )
        SELECT
            "user",
            consecutive_days
        FROM grouped_sign_ins
        GROUP BY "user", consecutive_days;
    `;

    // 执行 SQL 查询
    let res = await req.customSQL(sql);
    return res;
}

module.exports = {
    getConsecutiveSignIns
};