SQL如何使用MAX()结合不同分组规则获取用户月度总访问及分产品访问量

实现思路 #

你需要的acessos_geral（用户当月不区分产品的总访问天数）仅和用户、月份两个维度相关，不受产品分组的影响，有两种可行实现方案： #

方案1：最简修改，复用原有逻辑 #

你原有代码内层的acessos_geral窗口函数已经按用户+月份分区计算，同一个用户同一个月的所有数据行的acessos_geral最大值是固定值，分组后取max本身逻辑是正确的，确认内层窗口计算无误即可得到正确结果。如果想要彻底避免分组维度的干扰，可以用方案2。

方案2：外层窗口函数单独计算总访问量（更稳定） #

直接在外层查询时用窗口函数按用户+月份维度取最大值，完全不受产品分组的限制：

SELECT    
    max(acessos_origem) AS access_product,
    -- 按用户+月份分区取最大值，完全不受产品分组影响
    max(acessos_geral) OVER(PARTITION BY upper(USUARIO), MES_ANO) AS acessos_geral,
    upper(USUARIO) as USUARIO,
    ORIGEM,
    MES_ANO,
    EQUIPE,
    timestamp_format( to_char(DATA, 'MM/YYYY'), 'MM/YYYY') as DATA
FROM (
    SELECT 
        DENSE_RANK() OVER (PARTITION BY A.USUARIO, A.ORIGEM, A.MES_ANO ORDER BY A.DATA ASC) AS acessos_origem,
        DENSE_RANK() OVER (PARTITION BY A.USUARIO, A.MES_ANO ORDER BY A.DATA ASC) AS acessos_geral,
        USUARIO, 
        EQUIPE,
        ORIGEM,
        MES_ANO,
        DATA
    FROM (
        SELECT 
            EQUIPE, 
            USUARIO, 
            ORIGEM, 
            CONCAT(extract(month FROM data), '/', extract(year FROM data)) AS mes_ano, 
            data
        FROM db2i1c68.DADOS_USUARIOS_PLATAFORMA_BBDATA a 
        WHERE usuario = 'bigbbd01'
    )a 
)b 
WHERE MES_ANO = '7/2020' 
GROUP BY upper(USUARIO) , ORIGEM, MES_ANO, EQUIPE, timestamp_format( to_char(DATA, 'MM/YYYY'), 'MM/YYYY')

逻辑说明 #

• access_product字段按用户+月份+产品分组后取每个产品的最大访问日排名，得到单产品当月的访问天数
• acessos_geral字段通过窗口函数按用户+月份分区取全局最大值，无论分组维度是否包含产品，该值都是用户当月全量访问天数，完全符合需求

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