【LeetCode1747. 应该被禁止的 Leetflex 账户】[MySQL 用户变量/Pandas]面向过程编程；尝试维护一个multiset

目录

• 题目地址
• MySQL代码
• 等效pandas代码

题目地址

https://leetcode.cn/problems/leetflex-banned-accounts/description/

MySQL代码

with t1 as(
    select account_id,ip_address,
    login as tick,
    "login" as mytype
    from LogInfo
    union all
    select account_id,ip_address,
    logout as tick,
    "logout" as mytype
    from LogInfo
)
,
t2 as(
    SELECT 
        t.*,

        # ----只需要改动这里的逻辑，其他不要动-----------
        @prev_online_ips := @online_ips,   -- 本次事件发生之前的online_ips，存一下
        @search_for := CONCAT(',', t.ip_address) as search_for,  -- 声明一个变量，存一下经常要用到的东西，尝试使代码简洁
        @first_occurrence := LOCATE(@search_for, @online_ips) as first_occurrence, -- 声明一个变量，存一下经常要用到的东西，尝试使代码简洁


        @online_ips := (case when account_id=@prev_account_id then
            (CASE 
            WHEN t.mytype = 'login' THEN CONCAT_WS(',', @online_ips, t.ip_address)
            WHEN t.mytype = 'logout' THEN 

            (CASE
                WHEN @first_occurrence > 0 THEN -- 找到第一个匹配的ip，移除它
                    CONCAT(
                        SUBSTRING(@online_ips, 1, @first_occurrence - 1), 
                        SUBSTRING(@online_ips, @first_occurrence + LENGTH(@search_for))
                    )
                ELSE
                    @online_ips
            END)

            ELSE @online_ips END)
        else (CASE 
            WHEN t.mytype = 'login' THEN CONCAT_WS(',', '', t.ip_address)
            WHEN t.mytype = 'logout' THEN 

            (CASE
                WHEN @first_occurrence > 0 THEN -- 找到第一个匹配的ip，移除它
                    CONCAT(
                        SUBSTRING(@online_ips, 1, @first_occurrence - 1), 
                        SUBSTRING(@online_ips, @first_occurrence + LENGTH(@search_for))
                    )
                ELSE
                    @online_ips
            END)

            ELSE @online_ips END)
        end) AS dummy -- This is a dummy column to update the @online_ips variable

        ,

        @online_count := 
        (case when account_id=@prev_account_id then 
            (CASE 
            WHEN t.mytype = 'login'  THEN  
                (case when FIND_IN_SET(t.ip_address, @online_ips) > 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) = 0
                    then @online_count+1
                    else @online_count
                end)
            WHEN t.mytype = 'logout' THEN
                (case when FIND_IN_SET(t.ip_address, @online_ips) = 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) > 0  
                  then @online_count-1
                    else @online_count
                end)
            ELSE @online_count END)
         else (CASE 
            WHEN t.mytype = 'login'  THEN  1
            WHEN t.mytype = 'logout' THEN 
                (case when FIND_IN_SET(t.ip_address, @online_ips) = 0 and FIND_IN_SET(t.ip_address, @prev_online_ips) > 0  
                    then @online_count-1
                        else @online_count
                end)
            ELSE @online_count END)
        end )
        
        AS online_ip_count

        , @prev_account_id := account_id

        # ----只需要改动这里的逻辑，其他不要动-----------
    FROM 
        (SELECT @online_ips := '', @online_ip_count := 0, @prev_account_id := null) vars,
        (SELECT * FROM t1 ORDER BY account_id asc, tick ASC) t
)


select 
distinct account_id 
from t2
where online_ip_count>=2
order by account_id asc, tick ASC

等效pandas代码

import pandas as pd
from collections import Counter

def leetflex_banned_accnts(log_info: pd.DataFrame) -> pd.DataFrame:
    login_df = log_info[['account_id', 'ip_address', 'login']].rename(columns={'login': 'tick'})
    login_df['mytype'] = 'login'
    logout_df = log_info[['account_id', 'ip_address', 'logout']].rename(columns={'logout': 'tick'})
    logout_df['mytype'] = 'logout'
    
    events = pd.concat([login_df, logout_df])
    events.sort_values(by=['account_id', 'tick'], inplace=True)
    
    # Initialize the state dictionaries
    account_ips = {}
    account_online_counts = {}
    
    # Record of banned accounts
    banned_accounts = set()
    
    for _, row in events.iterrows():
        acc_id = row['account_id']
        ip = row['ip_address']
        event_type = row['mytype']
        
        if acc_id not in account_ips:
            account_ips[acc_id] = Counter()
            account_online_counts[acc_id] = 0
        
        if event_type == 'login':
            account_ips[acc_id][ip] += 1
            # We only increase the count of online IPs if this IP wasn't already counted
            if account_ips[acc_id][ip] == 1:
                account_online_counts[acc_id] += 1
            # Check if there are 2 or more unique IPs for the current account
            if account_online_counts[acc_id] >= 2:
                banned_accounts.add(acc_id)
        else:  # logout
            if account_ips[acc_id][ip] > 0:
                account_ips[acc_id][ip] -= 1
                # We only decrease the count of online IPs if this IP is completely logged out
                if account_ips[acc_id][ip] == 0:
                    account_online_counts[acc_id] -= 1
    
    # Convert set of banned accounts to DataFrame
    banned_accounts_df = pd.DataFrame({'account_id': list(banned_accounts)})
    return banned_accounts_df.sort_values(by='account_id')