如何用SQL获取最近1个月每天Top10城市交易总额？

内容纲要

本文详细讲解如何用 SQL 实现“获取最近1个月每天Top10城市交易总额”的需求，内容涵盖问题背景、表结构说明、思路分析、SQL实现及注意点，方便你以后直接查阅和分享。

标签：SQL, 数据仓库, TopN查询, 窗口函数, Hive, SparkSQL, 数据分析

背景介绍

在数据仓库（数仓）或业务数据分析中，我们经常需要对订单数据进行多维度统计和排名，比如统计每一天不同城市的交易额排名，挑选出Top10的重点城市，方便业务进行重点关注和策略制定。

今天我们就以一个实际订单表为例，演示如何写SQL语句获取“最近1个月每天Top10城市交易总额”。

表结构说明

假设数仓中存在如下订单表：

CREATE TABLE IF NOT EXISTS dm.sales_order (
    id BIGINT COMMENT '订单ID，自增',
    cal_dt DATE COMMENT '订单日期',
    order_no STRING COMMENT '订单编号',
    city_id INT COMMENT '城市ID',
    city_name STRING COMMENT '城市名称',
    price DECIMAL(12,2) COMMENT '订单支付价格',
    create_time DATETIME COMMENT '创建订单时间',
    update_time DATETIME COMMENT '更新订单时间'
) COMMENT '销售订单表';

表中每条记录代表一笔订单，包含订单日期、城市信息及订单金额。

需求拆解与分析

需求是：

获取最近1个月内（即从当前日期往前推30天或1个月）
每一天
城市维度的交易总额排名Top10

这里的“交易总额”是订单金额的累计和。

实现难点是如何在SQL中完成每天城市汇总和Top10的排序筛选。

实现思路

时间过滤
先筛选出最近1个月的订单数据，减少计算量。
每天每个城市聚合交易额
使用GROUP BY cal_dt, city_id, city_name，计算每天每个城市的交易总额。
对每天的城市交易额进行排名
使用窗口函数ROW_NUMBER()按每天的交易额降序排序，为每个城市编号。
筛选出每天排名前10的城市
最终只取排名小于等于10的数据。

SQL代码示例

以下SQL适用于大部分支持窗口函数的数据库（如Hive、SparkSQL、PostgreSQL、SQL Server等）：

WITH daily_city_sales AS (
    SELECT
        cal_dt,
        city_id,
        city_name,
        SUM(price) AS total_amt
    FROM dm.sales_order
    WHERE cal_dt >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
    GROUP BY cal_dt, city_id, city_name
),
ranked_city_sales AS (
    SELECT
        cal_dt,
        city_id,
        city_name,
        total_amt,
        ROW_NUMBER() OVER (PARTITION BY cal_dt ORDER BY total_amt DESC) AS rn
    FROM daily_city_sales
)
SELECT
    cal_dt,
    city_id,
    city_name,
    total_amt
FROM ranked_city_sales
WHERE rn <= 10
ORDER BY cal_dt, rn;

关键点详解

DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)：获取最近1个月的起始日期（部分数据库函数名和用法略有差异，需根据具体环境调整）
SUM(price)：计算订单金额总和
ROW_NUMBER() OVER (PARTITION BY cal_dt ORDER BY total_amt DESC)：为每天的城市总额排名
通过WHERE rn <= 10限制输出Top10
ORDER BY cal_dt, rn按日期和排名排序，方便阅读和后续使用

常见问题与建议

函数兼容性：不同数仓或数据库对日期函数支持不完全一致，注意调整DATE_SUB、CURRENT_DATE等函数写法。
性能优化：大数据量时，考虑先做时间过滤再聚合，避免全表扫描。
数据准确性：确保订单金额字段无异常，注意NULL值处理。
TopN数量可变：如果需要Top5、Top20，只需调整WHERE rn <= N即可。
排序规则：如果存在金额相同，可以根据城市ID或名称做次排序，避免排名不确定。

总结

通过本文的讲解和示例，你应该掌握了如何用SQL解决“最近1个月每天Top10城市交易总额”这类业务问题。窗口函数是解决TopN问题的利器，结合合理的过滤和分组，能让SQL既简洁又高效。

下次遇到类似需求，记得复用这个思路和SQL模板，不用再从零开始！

如果你想了解更多数据仓库实战SQL技巧，欢迎留言交流！