题解 | #小破站大会员最多同时有多少#

重点1:针对业务逻辑理解打标签 重点2:全连接 重点3:sum over partition by order by窗口函数句法的使用

SELECT 
    -- 5.对和求最大背包，即最大num
    MAX(num) AS max_num
FROM
    (
        SELECT
            user_id,
            -- 4.开窗，用户为分区键，时间为排序键位求和
            SUM(flag) OVER(PARTITION BY user_id ORDER BY tms ASC) AS num
        FROM
            -- 3.用户是否有效的所有合集
            (
                -- 1.flag为1标记为生效
                SELECT
                    user_id,
                    begin_date AS tms,
                    1 AS flag
                FROM 
                    detail_list_tb

                UNION ALL 
                -- 2.flag为-1标记为不生效
                SELECT
                    user_id,
                    end_date AS tms,
                    -1 AS flag
                FROM 
                    detail_list_tb
            )a
    )b;