一、要求
在訂單棒坏、銷售燕差、商品、客戶的數(shù)據(jù)庫(kù)中坝冕，求出每個(gè)季度哪個(gè)省份的銷售總額最高徒探。
二、測(cè)試數(shù)據(jù)（數(shù)據(jù)ER圖）
三喂窟、解法與思路
【剁手排行版】求購(gòu)買量最多的省份
1.利用`order_info`獲得訂單年测暗、季度
2.`order_info`和`customer_info`連接，得到省份信息
3.用省份信息磨澡、年度碗啄、季度為聚合條件求和(銷售量、訂單數(shù)钱贯、平均訂單金額數(shù))
4.用年度挫掏、季度排序
核心語(yǔ)句：
返回行無(wú)并列：row_number() over(partition by 分組列 order by 排序列 desc)
并列間斷排序：rank() over(partition by 分組列 order by 排序列 desc
并列連續(xù)排序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
