如何用SQL将日期列按月份拆分并验证用户月度登录情况

需求说明 #

需要通过SQL完成两项操作：

• 将日期列按月份拆分为多列，每个月份作为单独的列
• 验证用户的月度登录情况，用1表示该月有登录记录，0表示无登录记录

原始数据 #

user id             date
P1302              2023-11-01
P1302              2023-10-01
P1302              2023-09-01
P1302              2023-08-01
P1302              2023-07-01
P1302              2023-06-01
P1301              2023-11-01
P1301              2023-10-01
P1301              2023-08-01
P1301              2023-07-01
P1301              2023-06-01

期望结果 #

user id     Jun     Jul     Aug   Sep   Oct     Nov
1302         1       1       1     1     1       1
1301         1       1       1     0     1       1

通用SQL解决方案（适用于多数数据库） #

这种写法用CASE WHEN实现列转行，兼容性强：

SELECT
    REPLACE(user_id, 'P', '') AS user_id,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Jun' THEN 1 ELSE 0 END) AS Jun,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Jul' THEN 1 ELSE 0 END) AS Jul,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Aug' THEN 1 ELSE 0 END) AS Aug,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Sep' THEN 1 ELSE 0 END) AS Sep,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Oct' THEN 1 ELSE 0 END) AS Oct,
    MAX(CASE WHEN DATE_FORMAT(date, '%b') = 'Nov' THEN 1 ELSE 0 END) AS Nov
FROM
    your_table_name -- 替换为你的实际表名
GROUP BY
    REPLACE(user_id, 'P', '')
ORDER BY
    user_id DESC;

代码说明 #

1. REPLACE(user_id, 'P', '')：移除用户ID前缀的P，和期望结果格式保持一致
2. DATE_FORMAT(date, '%b')：提取日期的月份英文缩写（不同数据库语法略有差异：
   • SQL Server 用 DATENAME(MONTH, date)
   • Oracle 用 TO_CHAR(date, 'Mon')
   • PostgreSQL 用 TO_CHAR(date, 'Mon')
3. MAX(CASE...)：同一用户同一月份可能有多条登录记录，用MAX确保只要有登录就返回1，无登录则返回0
4. GROUP BY：按处理后的用户ID分组，汇总每个用户的月度登录情况

支持PIVOT语法的数据库写法（如SQL Server、Oracle） #

如果你的数据库支持PIVOT，可以用更简洁的写法：

SELECT
    REPLACE(user_id, 'P', '') AS user_id,
    Jun, Jul, Aug, Sep, Oct, Nov
FROM (
    SELECT
        user_id,
        DATE_FORMAT(date, '%b') AS month_abbr,
        1 AS login_flag
    FROM your_table_name
) AS src
PIVOT (
    MAX(login_flag)
    FOR month_abbr IN (Jun, Jul, Aug, Sep, Oct, Nov)
) AS pivot_table
ORDER BY user_id DESC;

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