【LeetCode1454. 活跃用户】[MySQL 用户变量/Pandas]面向过程编程解决"连续天数"的问题 (需要处理同一天重复多次登录)

目录

• 题目地址
• 题目描述
• 代码
  • • MySQL
    • 逐行翻译为Pandas代码

题目地址

https://leetcode.cn/problems/active-users/description/

题目描述

活跃用户 是指那些至少连续 5 天登录账户的用户。

编写解决方案,  找到 活跃用户 的 id 和 name。

返回的结果表按照 id 排序 。

 
 
 

注意需要处理，同一天多次登录的情形。

代码

MySQL

# Write your MySQL query statement below

WITH t1  AS (
    SELECT
        #------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的-------------------------------
        login_date,
        id,


        (case when login_date = @prev_date + INTERVAL 1 DAY AND id = @prev_author_id then @consec_days := @consec_days + 1
        when login_date = @prev_date AND id = @prev_author_id then @consec_days := @consec_days 
        else @consec_days := 1 end)  AS consec_days,


        @prev_date := login_date,
        @prev_author_id := id
        #-------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的------------------------------
    FROM
        (SELECT @prev_date := NULL, @prev_author_id := NULL, @consec_days := 1) vars,
        (SELECT login_date, id FROM Logins ORDER BY id , login_date) ordered_dates
)


select a.* from Accounts a 
where a.id in (SELECT id from t1 where consec_days>=5)
order by a.id asc

逐行翻译为Pandas代码

import pandas as pd

def active_users(accounts: pd.DataFrame, logins: pd.DataFrame) -> pd.DataFrame:
    logins = logins.sort_values(by=['id', 'login_date'])
    
    prev_date = None
    prev_author_id = None
    consec_days = 1
    consec_days_list = []

    for index, row in logins.iterrows():
        if (prev_date is not None and prev_author_id is not None and
                (row['login_date'] == prev_date + pd.Timedelta(days=1) and row['id'] == prev_author_id)):
            consec_days += 1
        elif (prev_date is not None and prev_author_id is not None and
                (row['login_date'] == prev_date and row['id'] == prev_author_id)):
            pass
        else:
            consec_days = 1

        consec_days_list.append(consec_days)
        prev_date = row['login_date']
        prev_author_id = row['id']

    logins['consec_days'] = consec_days_list

    active_ids = logins[logins['consec_days'] >= 5]['id'].unique()
    result = accounts[accounts['id'].isin(active_ids)].sort_values(by='id')

    return result