一、要求

在订单、销售、商品、客户的数据库中，求出每个季度哪个省份的销售总额最高。

二、测试数据（数据ER图）

三、解法与思路

【剁手排行版】求购买量最多的省份

1.利用`order_info`获得订单年、季度

2.`order_info`和`customer_info`连接，得到省份信息

3.用省份信息、年度、季度为聚合条件求和(销售量、订单数、平均订单金额数)

4.用年度、季度排序

核心语句：

返回行无并列：row_number() over(partition by 分组列 order by 排序列 desc)

并列间断排序：rank() over(partition by 分组列 order by 排序列 desc

并列连续排序dense_rank() over(partition by 分组列 order by 排序列 desc

解法如下：

SELECT

*

FROM

(SELECT

*,

RANK ()OVER(PARTITION BY `year`,`quarter` ORDER BY `province_total_sales` DESC) AS `rank`

FROM

(SELECT

`province`,

YEAR(`create_time`) AS `year`,

QUARTER(`create_time`) AS `quarter`,

SUM(`payment_amount`) AS `province_total_sales`,

COUNT(`order_id`) AS `order_count`,

SUM(`payment_amount`)/COUNT(`order_id`) AS `avg_payment`

FROM `customer_info`

INNER JOIN `order_info`

ON customer_info.`customer_id`=order_info.`customer_id`

GROUP BY `province`,`year`,`quarter`

) AS `province_quarter_sales`

)AS `province_quarter_sales_rank`

WHERE `rank`=1