一、要求
在订单、销售、商品、客户的数据库中，求出每个季度哪个省份的销售总额最高。
二、测试数据（数据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
