如何用SQL和Python统计每月新增用户数（排除历史用户）

需要使用SQL和Python统计每月新增用户数，统计时需排除在该月之前任何时间已出现过的用户，最终输出格式为类似{nov2022:45, dec2022:82, jan2023:29, feb2023:9, march2023:91}的字典。

数据库结构及示例数据 #

sender_id  timestamp
abc        1662367190.3912148
hsj        1662367190.3912363
hsj        1662367190.3912811
kfj        1662367190.8625183
abc        1662367190.8732119
ytf        1662367190.8732183
plw        1662367190.873222
hsj        1662367190.8732316
pws        1662367190.880536
pws        1662367209.621818
dlj        1662367209.6316075
lsf        1662367209.641992
lsf        1662367209.642001
qwe        1662367209.653683

已尝试的Python代码 #

def new_users():
    sql_query = """SELECT COUNT(SENDER_ID), DATE_TRUNC('MONTH', TO_TIMESTAMP(TIMESTAMP)) FROM 
                    EVENTS GROUP BY DATE_TRUNC('MONTH', TO_TIMESTAMP(TIMESTAMP))"""
    cursor.execute(sql_query)
    newusers = dict(cursor.fetchall())
    return newusers

说明：当前代码仅统计了每月的用户行为次数，未排除历史用户，无法得到正确的新增用户数。

解决方案 #

步骤1：修正SQL查询 #

先找出每个用户的首次出现月份，再按该月份分组统计新增用户数量：

SELECT 
    COUNT(DISTINCT sender_id) AS new_user_count,
    DATE_TRUNC('MONTH', TO_TIMESTAMP(timestamp)) AS month
FROM (
    SELECT 
        sender_id,
        MIN(timestamp) AS first_appear_time
    FROM EVENTS
    GROUP BY sender_id
) AS user_first_appear
GROUP BY month
ORDER BY month;

子查询通过MIN(timestamp)获取每个用户的首次出现时间，外层查询按月份分组统计该月首次出现的用户，确保只计算真正的新增用户。

步骤2：Python代码处理格式 #

将SQL返回的月份格式转换为需求的nov2022样式，并组装成目标字典：

from datetime import datetime

def new_users():
    sql_query = """
        SELECT 
            COUNT(DISTINCT sender_id) AS new_user_count,
            DATE_TRUNC('MONTH', TO_TIMESTAMP(timestamp)) AS month
        FROM (
            SELECT 
                sender_id,
                MIN(timestamp) AS first_appear_time
            FROM EVENTS
            GROUP BY sender_id
        ) AS user_first_appear
        GROUP BY month
        ORDER BY month;
    """
    cursor.execute(sql_query)
    results = cursor.fetchall()
    
    newusers = {}
    for count, month_dt in results:
        # 将日期转换为小写的"月份缩写+年份"格式
        month_str = month_dt.strftime('%b%Y').lower()
        newusers[month_str] = count
    
    return newusers

关键说明 #

• MIN(timestamp)确保每个用户仅被记录首次出现的时间，避免重复统计
• DATE_TRUNC('MONTH', ...)将时间戳统一截断到自然月级别，保证分组准确性
• Python的strftime('%b%Y')生成月份缩写+年份的字符串，转小写后完全匹配需求格式

内容的提问来源于stack exchange，提问作者prj