活跃用户(七天内进行了第二次购买的用户) 30天内日活用户（count(distinct user_id)）where卡三十天区间

1. leetcode 2688 日活用户

Users 表：

+-------------+----------+ 
| 字段名       | 类型      | 
+-------------+----------+ 
| user_id     | int      | 
| item        | varchar  |
| created_at  | datetime |
| amount      | int      |
+-------------+----------+
在这个表可能包含重复的记录。
每一行包括 user_id、购买的商品、购买日期和购买金额。

编写一个解决方案，找出活跃用户。活跃用户是指在其任何一次购买之后的七天内进行了第二次购买的用户。

例如，如果结束日期是 2023年5月31日，那么在 2023年5月31日和 2023年6月7日之间（包括这两天）的任何日期都被视为"在7天内"。

返回任意顺序的 user_id 列表，表示活跃用户列表。

结果的格式如下示例：

示例 1：

输入：
Users 表:
+---------+-------------------+------------+--------+ 
| user_id | item              | created_at | amount |  
+---------+-------------------+------------+--------+
| 5       | Smart Crock Pot   | 2021-09-18 | 698882 |
| 6       | Smart Lock        | 2021-09-14 | 11487  |
| 6       | Smart Thermostat  | 2021-09-10 | 674762 |
| 8       | Smart Light Strip | 2021-09-29 | 630773 |
| 4       | Smart Cat Feeder  | 2021-09-02 | 693545 |
| 4       | Smart Bed         | 2021-09-13 | 170249 |
+---------+-------------------+------------+--------+ 
输出：
+---------+
| user_id | 
+---------+
| 6       | 
+---------+
解释：
– user_id 为 5 的用户只有一笔交易，因此他不是活跃用户。
– user_id 为 6 的用户有两笔交易，第一笔交易是在2021年9月10日，第二笔交易是在2021年9月14日。第一笔和第二笔交易之间的时间间隔小于等于7天。因此，他是一个活跃用户。
– user_id 为 8 的用户只有一笔交易，因此他不是活跃用户。
– user_id 为 4 的用户有两笔交易，第一笔交易是在2021年9月2日，第二笔交易是在2021年9月13日。第一笔和第二笔交易之间的时间间隔大于7天。因此，他不是活跃用户。

============解答==========如下是三种答案===

# Write your MySQL query statement below
with c as(
select user_id ,created_at ,lag(created_at) OVER(PARTITION BY user_id ORDER BY created_at ) AS b_date from Users 
),
c1 as(
    select user_id, datediff(created_at,b_date)  as das from c where b_date is not null
)
select distinct user_id  from c1 where das <=7

作者：张晓
链接：https://leetcode.cn/problems/find-active-users/solutions/2274955/lag-kai-chuang-by-zhang-xiao-esylmvqida-nq30/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。


select 
distinct
user_id
from(
select
user_id,
created_at,
lead(created_at,1)over(partition by user_id order by created_at) as pn
from Users
) as t 
where
datediff(pn,created_at)<=7

作者：001
链接：https://leetcode.cn/problems/find-active-users/solutions/1/xiao-bai-jian-dan-zhi-bai-xie-fa-hen-ron-odpg/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。


# Write your MySQL query statement below
#手动造一个唯一标识
with ctr1 as (
    select 
        *,
        row_number() over() as id
    from users
)


select 
    distinct u1.user_id
from ctr1 u1 
join ctr1 u2 
on u1.user_id = u2.user_id 
and ( u1.created_at between u2.created_at and DATE_ADD(u2.created_at, INTERVAL 7 DAY))
and u1.id <> u2.id


作者：Priceless TaussignhK
链接：https://leetcode.cn/problems/find-active-users/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

分析最后一种答案.

    select 
        *,
        row_number() over() as id
    from users

    
Case 1
输入
Users =
| user_id | item              | created_at | amount |
| ------- | ----------------- | ---------- | ------ |
| 5       | Smart Crock Pot   | 2021-09-18 | 698882 |
| 6       | Smart Lock        | 2021-09-14 | 11487  |
| 6       | Smart Thermostat  | 2021-09-10 | 674762 |
| 8       | Smart Light Strip | 2021-09-29 | 630773 |
| 4       | Smart Cat Feeder  | 2021-09-02 | 693545 |
| 4       | Smart Bed         | 2021-09-13 | 170249 |
输出
| user_id | item              | created_at | amount | id |
| ------- | ----------------- | ---------- | ------ | -- |
| 5       | Smart Crock Pot   | 2021-09-18 | 698882 | 1  |
| 6       | Smart Lock        | 2021-09-14 | 11487  | 2  |
| 6       | Smart Thermostat  | 2021-09-10 | 674762 | 3  |
| 8       | Smart Light Strip | 2021-09-29 | 630773 | 4  |
| 4       | Smart Cat Feeder  | 2021-09-02 | 693545 | 5  |
| 4       | Smart Bed         | 2021-09-13 | 170249 | 6  |

on u1.user_id = u2.user_id 
and ( u1.created_at between u2.created_at and DATE_ADD(u2.created_at, INTERVAL 7 DAY))
and u1.id <> u2.id

  | user_id | item              | created_at | amount | id |     | user_id | item              | created_at | amount | id |
  | ------- | ----------------- | ---------- | ------ | -- |     | ------- | ----------------- | ---------- | ------ | -- |
  | 5       | Smart Crock Pot   | 2021-09-18 | 698882 | 1  |     | 5       | Smart Crock Pot   | 2021-09-18 | 698882 | 1  |
  | 6       | Smart Lock        | 2021-09-14 | 11487  | 2  |     | 6       | Smart Lock        | 2021-09-14 | 11487  | 2  |
  | 6       | Smart Thermostat  | 2021-09-10 | 674762 | 3  |     | 6       | Smart Thermostat  | 2021-09-10 | 674762 | 3  |
  | 8       | Smart Light Strip | 2021-09-29 | 630773 | 4  |     | 8       | Smart Light Strip | 2021-09-29 | 630773 | 4  |
  | 4       | Smart Cat Feeder  | 2021-09-02 | 693545 | 5  |     | 4       | Smart Cat Feeder  | 2021-09-02 | 693545 | 5  |
  | 4       | Smart Bed         | 2021-09-13 | 170249 | 6  |     | 4       | Smart Bed         | 2021-09-13 | 170249 | 6  |

2. 30天日活用户。

题目链接：https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/

SELECT activity_date as day
      ,count(distinct user_id) as active_users
FROM   Activity
WHERE  datediff('2019-07-27',activity_date) between 0 and 29
GROUP BY activity_date;

==================
select activity_date day, count(distinct user_id) active_users
from Activity
-- where datediff("2019-07-27",activity_date) between 0 and 29
where activity_date between date_add("2019--07-27",interval -29 day) and "2019-07-27"
group by activity_date

3.连续三天登录

目前表结构：

-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-06|
James06|2022-02-07|
James06|2022-02-08|
James23|2022-02-01|
James23|2022-02-03|
James23|2022-02-04|
James23|2022-02-05|
Paul03 |2022-02-06|
Paul03 |2022-02-07|
Paul03 |2022-02-10|
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/qq_35825178/article/details/133663912

方法1 ：

要分析当天用户是否连续3天，那我把当T，T-1，T-2三天数据都拿出来看看都有的用户就是3天连续登录的，SQL角度相当于我要最低是从日志结果集至少取3次数据进行关联，但是如果规则改为连续4天，就要再加代码一共取4次灵活性很差不易拓展。

SELECT 
   T01.cust_id       -- 用户ID
  ,T01.login_date    -- 登录日期
FROM DWPDATA.cust_login_date T01 
     LEFT JOIN DWPDATA.cust_login_date T02
          ON  T01.cust_id = T02.cust_id
          AND T01.login_date = T02.login_date + 1          
     LEFT JOIN DWPDATA.cust_login_date T03
          ON  T01.cust_id = T03.cust_id
          AND T01.login_date = T03.login_date + 2
WHERE T02.cust_id IS NOT NULL 
  AND T03.cust_id IS NOT NULL
ORDER BY 1,2
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-08|
James23|2022-02-05|

方法2：

取当天日期T数据然后和日期小于等于T，但是日期大于T-3的数据进行笛卡尔关联，我们再看如果统计用户不同日期出现次数等于3说明就是连续3天了，这种方式是取两个结果集关联，如果改为其它连续次数只需要改下限制条件中日期范围和判断次数的比较值即可；

SELECT 
   T01.cust_id              -- 用户ID
  ,T01.login_date           -- 登录日期
  ,COUNT(T02.login_date)    -- 近三天登录次数
  ,MAX(T02.login_date)      -- 近三天登录过最大日期
  ,MIN(T02.login_date)      -- 近三天登录过最小日期
FROM DWPDATA.cust_login_date T01 
     LEFT JOIN DWPDATA.cust_login_date T02
          ON  T01.cust_id = T02.cust_id
          AND T01.login_date - 2 <= T02.login_date  -- 限定比较范围为近3天
          AND T01.login_date     >= T02.login_date  
GROUP BY 1,2
HAVING COUNT(T02.login_date) = 3 -- 近三天登录次数 为3 说明连续登录3天了
;
-- 查询结果
cust_id|login_date|count|max       |min       |
-------+----------+-----+----------+----------+
James23|2022-02-05|    3|2022-02-05|2022-02-03|
James06|2022-02-08|    3|2022-02-08|2022-02-06|

方法3：

我们直接把用户+日期，按照日期顺序加上个递增序号，那么通过这个递增序号考虑，假如这个用户连续三天登录了，日期T的序号-2对应那条数据用户对应日期就应该是T-2,否则就不是连续3天了因为序号每天递增和日期递增量是保持一致的。

-- 使用 with 相当于建一个临时表，把排序后存储一下，然后可以自身关联，不用取原表两次
WITH VT_CUST_RK_INFO AS 
(
  SELECT 
     T01.cust_id              -- 用户ID
    ,T01.login_date           -- 登录日期
    ,ROW_NUMBER()OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) rk -- 使用开窗函数，每个客户按照登录日期排序，加上序号
  FROM DWPDATA.cust_login_date T01
)
SELECT
      P01.cust_id              -- 用户ID
     ,P01.login_date           -- 登录日期 
FROM VT_CUST_RK_INFO P01
     LEFT JOIN VT_CUST_RK_INFO P02
          ON  P01.cust_id = P02.cust_id
          AND P01.rk - 2  = P02.rk
WHERE P01.login_date - 2 =  P02.login_date  -- 序号和日期的差值一样证明此段是连续的
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-08|
James23|2022-02-05|

方法4:

在分析SQL③利用排序之后序号与日期的相同差值的解决方法后，思考可以直接利用函数LAG、LEAD。这两个函数，允许我们从窗口分区中，根据给定的相对于当前行的前偏移量（ LAG ）或后偏移量（ LEAD ），并返回对应行的值

SELECT
T01.cust_id -- 用户ID
,T01.login_date -- 登录日期
,LAG(T01.login_date,2,NULL)OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) lag_date -- 当前向前偏移2行对应日期，获取不到给NULL
FROM DWPDATA.cust_login_date T01
ORDER BY 1,2
-- 查询结果
cust_id|login_date|lag_date |
-------+----------+----------+
James06|2022-02-06| |
James06|2022-02-07| |
James06|2022-02-08|2022-02-06|
James23|2022-02-01| |
James23|2022-02-03| |
James23|2022-02-04|2022-02-01|
James23|2022-02-05|2022-02-03|
Paul03 |2022-02-06| |
Paul03 |2022-02-07| |
Paul03 |2022-02-10|2022-02-06|

参考资料，非常重要：

https://blog.csdn.net/qq_35825178/article/details/133663912

4.活跃用户

SQL拿捏：活跃用户数统计案例类型SQL 全网拿捏同款SQL

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/m0_49303490/article/details/130476010

思路
明确需求和思路
当日活跃用户数，我们对当日的数据（where）进行统计(count)
周活跃用户数，对本周内只要活跃过的用户进行统计，也就是活跃日期在本周一到当前日期范围内
月活跃用户数，对本月内只要活跃过的用户数进行统计，也就是活跃日期在本月内到当前日期范围内
注意，为了后期需求方便，可以对统计的日期进行在这进行判断是否是本周的最后一天或者本月的最后一天
用统计日期进行关联三个查询的数据，为后期方便，在这里做了统计日是否为本周或者本月末。然后数据一并写入到活跃表中

---日活跃数统计
with daycount as (
    SELECT
        '2023-01-01' dt,
        count(user_id) day_cnt
    from tb
    where activity_date = '$today_dt'
),
---周跃数统计
wkcount as(
    SELECT
        '2023-01-01' dt,
        count(user_id) wk_cnt
    from tb
    where activity_date >=date_add(next_date(activity_date,'MO'),-7)
    and activity_date <= '$dt'
),
---月跃数统计
mncount as (
    SELECT
        '2023-01-01' dt,
        count(user_id) mn_cnt
    from tb
    where date_format(activity_date,'yyyy-MM') = date_format('$dt','yyyy-MM') 
)
--是将查询的活跃数据进行整合在一起一起写入活跃表中
insert into table.usr_active_cnt 
SELECT
    dt,
    day_cnt,
    wk_cnt,
    mn_cnt,
    if(date_add(next_day(dt,'MO'),-1)=daycount.dt,'Y','N') as is_weekend,  
    if(last_day(dt)=mncount.dt,'Y','N') as is_mn
from daycount
left  join wkcount on daycount.dt  = wkcount.dt
left join mncount on daycount.dt  = mncount.dt

另外获取月末初简单直接截取日期前七位即可.
获取week日期可以这样子：  关于hive的: select date_add(current_date, 1-dayofweek(current_date) ) as  start_of_week。

posted @ 2024-08-23 13:09 萌哥-爱学习阅读(17) 评论(0) 编辑收藏举报

刷新页面返回顶部

萌哥

爱学习

活跃用户(七天内 进行了第二次购买的用户) 30天内日活用户（count(distinct user_id)）where卡三十天区间

1. leetcode 2688 日活用户

2. 30天日活用户。

3.连续三天登录

4.活跃用户

公告

活跃用户(七天内进行了第二次购买的用户) 30天内日活用户（count(distinct user_id)）where卡三十天区间