MySQL WEEK()函数转PostgreSQL后留存率计算结果不一致求助

数据老陈

2026-7-18

适配MySQL留存率计算代码到PostgreSQL后结果与预期不符，请求排查原因

我参考某博客的MySQL用户留存率计算方法开展个人项目，将代码适配为PostgreSQL（使用pgAdmin v6.19）后，复现的结果与原文预期不一致：

原文预期结果：原文预期留存率表格
我的计算结果：我的PostgreSQL计算结果

以下是我修改后的PostgreSQL代码：

create table login(login_date date,user_id int, id bigserial);
insert into login(login_date,user_id)
values  (TO_DATE('2020-01-01', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-02', 'YYYY-MM-DD'), 12),
(TO_DATE('2020-01-03', 'YYYY-MM-DD'), 15),
(TO_DATE('2020-01-04', 'YYYY-MM-DD'), 11),
(TO_DATE('2020-01-05', 'YYYY-MM-DD'), 13),
(TO_DATE('2020-01-06', 'YYYY-MM-DD'), 9),
(TO_DATE('2020-01-07', 'YYYY-MM-DD'), 21),
(TO_DATE('2020-01-08', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-09', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-10', 'YYYY-MM-DD'), 2),
(TO_DATE('2020-01-11', 'YYYY-MM-DD'), 16),
(TO_DATE('2020-01-12', 'YYYY-MM-DD'), 12),
(TO_DATE('2020-01-13', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-14', 'YYYY-MM-DD'), 18),
(TO_DATE('2020-01-15', 'YYYY-MM-DD'), 15),
(TO_DATE('2020-01-16', 'YYYY-MM-DD'), 12),
(TO_DATE('2020-01-17', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-18', 'YYYY-MM-DD'), 18),
(TO_DATE('2020-01-19', 'YYYY-MM-DD'), 14),
(TO_DATE('2020-01-20', 'YYYY-MM-DD'), 16),
(TO_DATE('2020-01-21', 'YYYY-MM-DD'), 12),
(TO_DATE('2020-01-22', 'YYYY-MM-DD'), 21),
(TO_DATE('2020-01-23', 'YYYY-MM-DD'), 13),
(TO_DATE('2020-01-24', 'YYYY-MM-DD'), 15),
(TO_DATE('2020-01-25', 'YYYY-MM-DD'), 20),
(TO_DATE('2020-01-26', 'YYYY-MM-DD'), 14),
(TO_DATE('2020-01-27', 'YYYY-MM-DD'), 16),
(TO_DATE('2020-01-28', 'YYYY-MM-DD'), 15),
(TO_DATE('2020-01-29', 'YYYY-MM-DD'), 10),
(TO_DATE('2020-01-30', 'YYYY-MM-DD'), 18);

SELECT * from Login
ORDER BY login_Date;

SELECT
user_id,
extract(week FROM login_date)-1 AS login_week
FROM
login
GROUP BY
user_id, extract(week FROM login_date)
ORDER by user_id asc;

SELECT user_id, 
min(extract(week FROM login_date)-1) AS first_week
FROM login
GROUP BY user_id
ORDER BY user_id;

WITH with_week_number AS (
SELECT
a.user_id,
EXTRACT(WEEK FROM a.login_date) - MIN(EXTRACT(WEEK FROM a.login_date)) AS login_week,
b.first_week,
EXTRACT(WEEK FROM a.login_date) - b.first_week AS week_number
FROM
(
  SELECT
    user_id,
    login_date
  FROM
    login
  GROUP BY
    user_id,
    login_date
) a
JOIN
(
  SELECT
    user_id,
    MIN(EXTRACT(WEEK FROM login_date)) AS first_week
  FROM
    login
  GROUP BY
    user_id
) b ON a.user_id = b.user_id
GROUP BY
a.user_id, a.login_date, b.first_week
)
SELECT
first_week,
SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0,
SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1,
SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2,
SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3,
SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4,
SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5,
SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6,
SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7,
SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8,
SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9
FROM with_week_number
GROUP BY first_week
ORDER BY first_week;

问题排查与修正

核心问题1：周数计算基准不一致

PostgreSQL的EXTRACT(WEEK FROM date)遵循ISO 8601标准，每周从周一开始，2020年1月1日属于第1周；而MySQL默认WEEK()函数以周日为一周起始，2020年1月1日会被算作第0周。你的代码中，部分查询用extract(week FROM login_date)-1对齐MySQL逻辑，但CTE子查询b中直接用MIN(EXTRACT(WEEK FROM login_date))，未做减1处理，导致周数基准混乱，最终week_number计算全部偏移。

核心问题2：CTE中冗余且错误的计算逻辑

CTE里的EXTRACT(WEEK FROM a.login_date) - MIN(EXTRACT(WEEK FROM a.login_date))完全无意义：在GROUP BY a.user_id, a.login_date的前提下，每个分组只有一个周数值，MIN结果等于自身。这部分逻辑属于冗余，且进一步干扰了周数计算。

修正后的代码

WITH user_first_week AS (
  -- 统一减1，对齐MySQL的周起始逻辑
  SELECT
    user_id,
    MIN(EXTRACT(WEEK FROM login_date) - 1) AS first_week
  FROM login
  GROUP BY user_id
),
user_login_weeks AS (
  SELECT
    a.user_id,
    EXTRACT(WEEK FROM a.login_date) - 1 AS login_week,
    b.first_week,
    -- 计算与首次登录周的差值
    (EXTRACT(WEEK FROM a.login_date) - 1) - b.first_week AS week_number
  FROM (
    -- 用DISTINCT去重，替代冗余的GROUP BY
    SELECT DISTINCT user_id, login_date
    FROM login
  ) a
  JOIN user_first_week b ON a.user_id = b.user_id
)
SELECT
  first_week,
  SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0,
  SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1,
  SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2,
  SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3,
  SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4,
  SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5,
  SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6,
  SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7,
  SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8,
  SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9
FROM user_login_weeks
GROUP BY first_week
ORDER BY first_week;

修正说明

统一周数基准：所有周数计算都执行-1操作，对齐MySQL的周起始逻辑，确保和原文的周数定义一致。
拆分CTE逻辑：将用户首次周计算与登录周差值计算拆分为两个独立CTE，逻辑更清晰易维护。
简化去重逻辑：用DISTINCT替代GROUP BY user_id, login_date，消除冗余代码。

内容的提问来源于stack exchange，提问作者Karina