sql - 尝试查询 postgresql 以获取当前和最长的连续记录

Question

我有一个带有 Postgres 数据库的 Express API，并使用 Knex/ObjectionJS/Raw SQL 进行查询。

在数据库中，我有一个 Acts 表 s.t.一个用户每天可以创建 1 个 Act。这是架构:

t.increments('id').primary()
t.string('deed')
t.integer('users_id').references('users.id').onDelete('CASCADE')

t.datetime("created_at");
t.datetime("updated_at");

我的目标是创建 2 个查询以返回 (1) 用户创建和操作的连续天数的当前连续记录，以及 (2) 用户创建操作的最长连续连续天数。

我见过各种声称这样做的示例查询，但无法让它们为我工作。

根据@Jonathan 的建议更新代码。

这是我的路线:

// return longest streaks of daily acts by user
router.get('/longest_streak', (req, res, next) => {
  let results = knex.raw(`
  WITH RECURSIVE cte 
  AS
  (
    SELECT users_id, created_at, 1 AS cnt
    FROM acts a
    UNION ALL
    SELECT acts.users_id, acts.created_at, c.cnt + 1
    FROM acts a INNER JOIN cte c
    ON acts.users_id = c.users_id
    AND acts.created_at = c.created_at - interval '1' day
  )
  SELECT users_id, MAX(cnt) AS most_consecutive
  FROM cte
  GROUP BY users_id;
  `);
  console.log('getting current streak');
  res.json(results);
})

这是痕迹:

Running on localhost:8000
getting current streak
Cannot find module 'pg-native'
TypeError: Converting circular structure to JSON
    at JSON.stringify (<anonymous>)
    at stringify (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/response.js:1123:12)
    at ServerResponse.json (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/response.js:260:14)
    at router.get (/Users/dariusgoore/development/writerboard/writerboard-express-api/src/routes/acts.js:103:7)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/route.js:137:13)
    at Route.dispatch (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/route.js:112:3)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at /Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:281:22
    at Function.process_params (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:335:12)
    at next (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:275:10)
    at Function.handle (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:174:3)
    at router (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:47:12)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:317:13)
    at /Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:284:7

Answer 1

您可以使用递归 cte 执行此操作，类似于:

WITH RECURSIVE cte
AS
(
    select users_id, created_at, 1 as cnt
    FROM acts 
    UNION ALL
    select a.users_id, a.created_at, c.cnt + 1
    FROM acts a inner JOIN cte c
    ON a.users_id = c.users_id
    AND a.created_at = c.created_at - interval '1' day
)
SELECT users_id, MAX(cnt) AS most_consecutive
FROM cte
GROUP BY users_id;

要获得当前连胜(我假设当前是指在给定日期结束 - 今天或可能是昨天？)，那么您需要做的就是在 cte 的第一部分添加一个 where 子句以限制第一个日期选在今天或昨天。

在 most consecutive 的情况下，您还需要知道每个连续出现的时间，那么您将需要添加第二个 cte，因此查询变为:

WITH RECURSIVE cte
AS
(
    select users_id, created_at, 1 as cnt
    FROM acts 
    UNION ALL
    select a.users_id, a.created_at, c.cnt + 1
    FROM acts a inner JOIN cte c
    ON a.users_id = c.users_id
    AND a.created_at = c.created_at - interval '1' day
),
ctemax AS
(SELECT users_id, MAX(cnt) as most_consecutive 
FROM cte
GROUP BY users_id)
SELECT c.users_id, c.created_at, m.most_consecutive
FROM cte c 
INNER JOIN ctemax m ON c.users_id = m.users_id AND c.cnt = m.most_consecutive;

这会为您提供最长连胜记录以及该连胜记录开始的日期。

更新

有一件事我忘了提:要让它起作用，你需要将 created_at 强制转换为 date!