ermutuxia

2020-10-01   阅读量: 1220

Python

如何求每个用户的最长连续登录天数?

扫码加入数据分析学习群

题目要求如下:

用户登陆表:user_login

user_id 用户ID

login_time 登陆日期


(1,'2019-06-01')

(1,'2019-06-02')

(1,'2019-06-03')

(1,'2019-06-06')

(1,'2019-06-07')

(1,'2019-06-08')

(1,'2019-06-11')

(1,'2019-06-12')

(2,'2019-06-01')

(2,'2019-06-02')

(2,'2019-06-04')

(3,'2019-06-01')

(3,'2019-06-02')

(4,'2019-06-01')

(5,'2019-06-01')

(5,'2019-06-02')


请写一条sql(或者其他方法)语句,查询每位用户的最长连续登陆天数

user_login.txt

添加CDA认证专家【维克多阿涛】,微信号:【cdashijiazhuang】,提供数据分析指导及CDA考试秘籍。已助千人通过CDA数字化人才认证。欢迎交流,共同成长!
198.6743 2 1 关注作者 收藏

评论(1)

ermutuxia
2020-10-01
import pprint


def get_max_date(user_id, login_date):
    """基于双指针的算法实现用户连续登录天数"""
    n = len(login_date)
    if len(login_date) == 1:
        return 1
    max_day = 0
    cur_day = 1
    for i in range(n - 1):
        d1 = datetime.strptime(login_date[i], "%Y-%m-%d")
        d2 = datetime.strptime(login_date[i + 1], "%Y-%m-%d")
        if (d2 - d1).days == 1:
            cur_day += 1
        elif (d2 - d1).days > 1:
            cur_day = 0
        max_day = max(cur_day, max_day)
    return max_day


# 1. 读取数据信息
from datetime import datetime
users = {}  # 字典存储用户信息, {user_id:login_date}
with open('C:\\Users\\Administrator\\Desktop\\用户连续登录天数\\用户连续登录天数\\user_login.txt') as f:
    for line in f:
        user_id, login_date = line.strip().split('\t')
        login_date = login_date.strip("'")
        if user_id not in users:
            users[user_id] = [login_date]
        else:
            users[user_id].append(login_date)

pprint.pprint(users)

# 2. 加载数据信息
for user_id, login_date in users.items():
    max_day = get_max_date(user_id, login_date)
    print("%s的最长连续登录天数为%s" % (user_id, max_day))

image.png

0.0000 0 0 回复

推荐课程

推荐帖子