在 POSTGRESQL 中获取新用户与回访者列表-解网

问：

我想获取新用户与回访用户的用户列表。每周报告一次。如果用户在一周内首次登录，则视为新用户，如果用户在一周内多次登录，他仍然是新用户。如果用户在上周登录并在本周重新登录，则他将成为回访用户。

我有一张桌子： user_name、login_date、操作 [作为登录或注销]

我有查询，目前给我的回复如下：

WITH first_visit AS (
         SELECT v.user_name,
            min(v.login_date) AS first_login_date
           FROM db.user v
          GROUP BY v.user_name
        ), visitors_list AS (
         SELECT u.doc_id,
            u.user_name,
            u.login_date
                CASE
                    WHEN fv.first_login_date > (CURRENT_DATE - '7 days'::interval) THEN 'New user'::text
                    ELSE 'Returning User'::text
                END AS user_status
           FROM db.user u
             JOIN first_visit fv ON u.user_name::text = fv.user_name::text
          WHERE u.login_date < CURRENT_DATE AND u.login_date > (CURRENT_DATE - 7) AND u.action::text = 'Login'::text
        )
 SELECT v.doc_id, v.user_name,
    v.user_status,
    v.first_login_date
   FROM visitors_list v
  GROUP BY v.user_name, v.user_status, v.first_login_date ,v.doc_id ;

这里是同一个用户的重复数据，我需要帮助来了解如何获取每个用户的单个数据以及doc_id和login_Date，如果我删除doc_id我确实会得到独特的结果，但我也想要doc_id。

SQL PostgreSQL

SELECT user,
       EXISTS ( SELECT NULL
                FROM users u2
                WHERE u1.user = u2.user
                  AND u2.date < CURRENT_DATE - INTERVAL 1 WEEK
             --   AND u2.date < u1.date - INTERVAL 1 WEEK
               ) AS is_returned_user
FROM users u1
WHERE date >= CURRENT_DATE - INTERVAL 1 WEEK

对于在上周内登录的每个用户，查询将测试当前周外的登录行是否存在。如果存在，则返回 TRUE，否则返回 FALSE。is_returned_user

0赞 bi_noob 11/17/2023 #2

在 CTE 中添加了访问排名，并选择了最近的访问以获得所需的输出。代码如下：

WITH first_visit AS (
     SELECT v.user_name,
            min(v.login_date) AS first_login_date
       FROM user v
      GROUP BY v.user_name
    ), visitors_list AS (
     SELECT u.doc_id,
            u.user_name,
            u.login_date,
      -- added a rank every visit
            dense_rank() OVER (partition by u.user_name order by u.login_date desc) user_rank,
            CASE
                WHEN fv.first_login_date > (CURRENT_DATE - INTERVAL 7 DAY) THEN 'New user'
                ELSE 'Returning User'
            END AS user_status
       FROM user u
      INNER JOIN first_visit fv ON u.user_name = fv.user_name
      WHERE u.login_date <= CURRENT_DATE AND u.login_date > (CURRENT_DATE - 7) AND u.action = 'Login'
    )
 SELECT v.doc_id,
        v.user_name,
        v.user_status,
        v.login_date
   --  ,v.user_rank
   FROM visitors_list v
  WHERE v.user_rank = 1 -- filter recent visits only
  -- GROUP BY v.user_name, v.user_status, v.login_date  ,v.doc_id 
  ;

DB fiddle for reference 返回用户 sql

上一个：我正在寻找一种通用且快速的策略来预测查询结果？

下一个：逐行显示一个记录字段值

在 POSTGRESQL 中获取新用户与回访者列表

Get new user vs returning visitor list in POSTGRESQL

评论