题解 | #广告点击的高峰期#

/* 初步思路：先统计再降序排，取第一个值 */
SELECT
    hour AS click_hour,
    amount_click AS click_cnt
FROM
    (
        SELECT
            HOUR(click_time) AS hour,
            COUNT(*) AS amount_click
        FROM
            user_ad_click_time
        GROUP BY
            hour
        ORDER BY
            amount_click DESC
        LIMIT
            1
    ) AS subquery
;

/* 思路2：使用窗口函数进行计算，更加直观 */
SELECT
    click_hour,
    click_cnt
FROM
    (
        SELECT
            HOUR(click_time) AS click_hour,
            COUNT(*) AS click_cnt,
            -- 使用窗口函数进行排名，按照点击次数降序
            RANK() OVER(
                ORDER BY
                    COUNT(*) DESC
            ) AS ranking
        FROM
            user_ad_click_time
        GROUP BY
            click_hour
    ) AS subquery
WHERE
    ranking = 1
;

/* 思路3：使用公共表达式使得逻辑更加清晰直观 */
-- 使用公共表达式（CTE，MySQL 8.0及以上支持）来先进行分组统计并计算排名
WITH RankedClicks AS (
    SELECT
        HOUR(click_time) AS click_hour,
        COUNT(*) AS click_count,
        -- 使用DENSE_RANK函数按照点击次数降序排名，方便处理并列情况
        DENSE_RANK() OVER (
		  ORDER BY 
		  	COUNT(*) DESC
		) AS ranking
    FROM
        user_ad_click_time
    WHERE
        click_time IS NOT NULL  -- 排除click_time字段为NULL的数据，保证统计准确
    GROUP BY
        click_hour
)
SELECT
    click_hour,
    click_count
FROM
    RankedClicks
WHERE
    ranking = 1;  -- 筛选出排名为1的，即高峰期对应的小时和点击次数