将MySQL WEEK()函数转换为PostgreSQL

如果有人能解决这个问题，我将非常感激！我正在做一个个人项目，来自以下网站：https://ubiq.co/database-blog/how-to-calculate-retention-rate-in-sql/
我复制了代码，但得到了不同的结果，不知道为什么
Here is the result they got, which is what I should have gotten
Here is the table I got with the same code
他们的代码在这里：https://ubiq.co/database-blog/how-to-calculate-retention-rate-in-sql/
我的代码如下（稍微调整了一下，因为他们使用mysql，而我使用的是pgadmin v6.19）：

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;

MySQL的WEEK函数有几种模式，很难判断该网页的MySql服务器上的默认设置是什么（看起来它是以mode=0运行的）。
PostgreSQL对周提取有不同的规则，这就是为什么你会得到不同的结果。
您可以尝试通过创建自己的函数来复制MySQL mode=0行为，然后尝试“几乎原始”的查询。

create or replace function week_number(p_date timestamp)
RETURNS integer AS
$body$
    SELECT
    CASE
    WHEN EXTRACT(DOW FROM p_date) = 0
    THEN EXTRACT(WEEK FROM p_date)
    ELSE EXTRACT(WEEK FROM p_date) - 1
END
$body$
LANGUAGE 'sql';

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 (
    SELECT a.user_id
        ,a.login_week
        ,b.first_week AS first_week
        ,a.login_week - first_week AS week_number
    FROM (
        SELECT user_id
            ,week_number(login_date) AS login_week
        FROM LOGIN
        GROUP BY user_id
            ,week_number(login_date)
        ) a
        ,(
            SELECT user_id
                ,min(week_number(login_date)) AS first_week
            FROM LOGIN
            GROUP BY user_id
            ) b
    WHERE a.user_id = b.user_id
    ) AS with_week_number
GROUP BY first_week
ORDER BY first_week;

将MySQL WEEK()函数转换为PostgreSQL

1条答案

相关问题

热门标签

最新问答