横扫SQL面试——TopN问题

横扫SQL面试

电商平台的"销量Top10商品"🛍️，内容社区的"热度Top5文章“”🔥，还是金融领域的"交易额Top3客户"💰——TopN问题无处不在！

无论是日常业务分析📊，还是技术面试💡，能否优雅地解决TopN问题，往往决定了你是一个"只会写基础查询"的SQL用户🧑‍💻，还是一个"能高效处理复杂需求"的数据专家🎯！

TopN问题 前置知识

1. 窗口函数：

   • row_number(): 无并列排名 (1,2,3,4)
   • rank(): 有并列会跳过名次 (1,2,2,4)
   • dense_rank(): 有并列不跳名次 (1,2,2,3)

2. 执行流程：

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

一、销量Top10商品🛍️

在电商业务场景中，为了更好地了解商品销售情况，以便进行库存管理、营销推广等工作，需要找出 2023 年每个商品类目下销量最高的前 10 个商品。

在统计销量时，需要考虑并列情况，即如果有多个商品销量相同且都能进入前 10 名，这些商品都应被选出来。

思路💡

1. 筛选出 2023 年的订单数据。
2. 按商品类目和商品分组，计算每个商品的总销量。

4. 对每个商品类目内的商品按总销量降序排名。
5. 筛选出排名前 10 的商品。

完整代码💻🚀💻🚀：

-- 第一步：创建一个公共表表达式 (CTE) category_sales，用于计算每个商品在 2023 年的总销量
with category_sales as (
    -- 选择需要的列，包括商品类目 ID、类目名称、商品 ID、商品名称和总销量
    select 
        category_id,
        category_name,
        product_id,
        product_name,
        -- 使用 sum 函数计算每个商品的总销量
        sum(quantity) as total_sales
    from 
        orders
    -- 筛选出 2023 年的订单数据
    where 
        extract(year from sale_date) = 2023
    -- 按商品类目和商品分组
    group by 
        category_id, category_name, product_id, product_name
),
-- 第二步：创建另一个 CTE ranked_products，用于对每个商品类目内的商品按总销量降序排名
ranked_products as (
    select 
        category_id,
        category_name,
        product_id,
        product_name,
        total_sales,
        -- 使用 dense_rank 函数对每个商品类目内的商品按总销量降序排名
        dense_rank() over (partition by category_id order by total_sales desc) as sales_rank
    from 
        category_sales
)
-- 第三步：从 ranked_products 中选择排名前 10 的商品
select 
    category_id,
    category_name,
    product_id,
    product_name,
    total_sales
from 
    ranked_products
-- 筛选出排名前 10 的商品
where 
    sales_rank <= 10;

二、社区热度Top5文章🔥

在内容社区里，为了向用户推荐热门文章，需要计算过去 7 天各板块内综合热度排名前 5 的文章。

综合热度的计算方式为：点赞数×0.6 + 收藏数×0.3 + 评论数×0.1。通过这种方式可以更全面地衡量文章在社区内的受欢迎程度。

思路💡

1. 筛选出过去 7 天的文章数据。
2. 计算每篇文章的综合热度。

3. 对每个板块内的文章按综合热度降序排名。
4. 筛选出排名前 5 的文章。

完整代码：💻🚀💻🚀

-- 第一步：创建一个 CTE article_heat，用于计算每篇文章在过去 7 天的综合热度
with article_heat as (
    select 
        article_id,
        board_id,
        board_name,
        title,
        -- 计算综合热度：点赞数 * 0.6 + 收藏数 * 0.3 + 评论数 * 0.1
        (likes * 0.6 + favorites * 0.3 + comments * 0.1) as heat_score
    from 
        articles
    -- 筛选出过去 7 天的文章数据
    where 
        publish_time >= current_date - interval 7 day
),
-- 第二步：创建另一个 CTE ranked_articles，用于对每个板块内的文章按综合热度降序排名
ranked_articles as (
    select 
        article_id,
        board_id,
        board_name,
        title,
        heat_score,
        -- 使用 dense_rank 函数对每个板块内的文章按综合热度降序排名
        dense_rank() over (partition by board_id order by heat_score desc) as heat_rank
    from 
        article_heat
)
-- 第三步：从 ranked_articles 中选择排名前 5 的文章
select 
    article_id,
    board_id,
    board_name,
    title,
    heat_score
from 
    ranked_articles
-- 筛选出排名前 5 的文章
where 
    heat_rank <= 5;

三、交易额Top3客户💰

在金融业务中，为了对不同区域的优质客户进行精准营销和服务，需要按季度统计 2023 年各区域交易金额最高的 3 个客户，并且要求显示客户层级。

思路💡

1. 筛选出 2023 年的交易数据。
2. 按区域、季度和客户分组，计算每个客户在每个季度的总交易金额。

3. 对每个区域和季度内的客户按总交易金额降序排名。
4. 筛选出排名前 3 的客户。

完整代码：💻🚀💻🚀

-- 第一步：创建一个 CTE quarterly_transactions，用于计算每个客户在每个季度的总交易金额
with quarterly_transactions as (
    select 
        client_id,
        client_name,
        client_level,
        region,
        -- 提取交易日期的季度
        extract(quarter from trans_date) as quarter,
        -- 使用 sum 函数计算每个客户在每个季度的总交易金额
        sum(trans_amount) as total_trans_amount
    from 
        transactions
    -- 筛选出 2023 年的交易数据
    where 
        extract(year from trans_date) = 2023
    -- 按区域、季度和客户分组
    group by 
        client_id, client_name, client_level, region, extract(quarter from trans_date)
),
-- 第二步：创建另一个 CTE ranked_clients，用于对每个区域和季度内的客户按总交易金额降序排名
ranked_clients as (
    select 
        client_id,
        client_name,
        client_level,
        region,
        quarter,
        total_trans_amount,
        -- 使用 dense_rank 函数对每个区域和季度内的客户按总交易金额降序排名
        dense_rank() over (partition by region, quarter order by total_trans_amount desc) as trans_rank
    from 
        quarterly_transactions
)
-- 第三步：从 ranked_clients 中选择排名前 3 的客户
select 
    client_id,
    client_name,
    client_level,
    region,
    quarter,
    total_trans_amount
from 
    ranked_clients
-- 筛选出排名前 3 的客户
where 
    trans_rank <= 3;