拼多多大数据开发工程师SQL实战解析

不久前，裸考国内知名电商平台拼多多的大数据岗位在线笔试，问答题（写SQL）被虐的很惨，完了下来默默学习一波。顺便借此机会复习一下SQL语句的用法。

本文主要涉及到的SQL知识点包括CREATE创建数据库和表、INSERT插入数据、SUM()求和、GROUP BY分组、DATE_FORMAT()格式化日期、ORDER BY排序、COUNT()统计行数、添加排名、MySQL实现统计排名、并列排名等，如果你对这些操作还有点不熟练，那么相信你读完本文会有收获的，如果自己再实现一遍效果更好。

准备工作

根据笔试时遗留的线索，在本地MySQL创建数据库和表，为后续铺垫。

创建数据库和表

CREATE DATABASE语句用于创建数据库，基本语法如下：

CREATE DATABASE database_name

在本地创建一个名为test的测试数据库：

CREATE TABLE test;

CREATE TABLE语句用于创建表，基本语法如下：

CREATE TABLE table_name( column_name1 type, column_name2 type, column_name3 type, ... )

在test数据库下面创建一张名为orders的表：

USE test; CREATE TABLE orders( id INT PRIMARY KEY AUTO_INCREMENT, order_time TIMESTAMP, cate VARCHAR(255), goods_id int, order_amount int )

插入数据

INSERT INTO 语句用于向表格中插入新的行，基本语法如下：

INSERT INTO table_name VALUES (value1, value2,....)

向orders表中插入一些测试数据：

INSERT INTO orders(order_time,cate,goods_id,order_amount) VALUES ('2018-02-28 00:00:01', '水果',223,100), ('2018-02-28 01:01:01', '花茶',444,111), ('2018-02-28 06:06:06', '花茶',444,666), ('2018-03-01 07:01:10', '花茶',5555,170), ('2018-03-01 08:00:00', '花茶',5555,180), ('2018-03-01 00:00:01', '花茶',333,100), ('2018-03-01 00:00:01', '花茶',444,188), ('2018-03-01 00:00:01', '数码',45454,5399)

结果如图所示：

题目解析

请统计2018年全年每月销售金额，按下表格式返回。

日期 销售金额
2018-01   ****  
2018-02   ****  
...   ...  

分析：统计每月的销售金额，需要用到求和函数SUM()。SUM()函数用于返回数值列的总和。基本语法如下：

SELECT SUM(column_name) FROM table_name

求和通常需要用到GROUP BY，GROUP BY可以根据一个或多个列对结果集进行分组，本题也是这个套路，需要根据月份进行分组统计。GROUP BY的基本语法如下：

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

当然本题还有其他附加要求，按照规定形式返回，需要对日期进行进行格式化处理。DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据，基本语法如下：

DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。可以使用的格式有：

格式 描述
%a   缩写星期名  
%b   缩写月名  
%c   月，数值  
%D   带有英文前缀的月中的天  
%d   月的天，数值(00-31)  
%e   月的天，数值(0-31)  
%f   微秒  
%H   小时 (00-23)  
%h   小时 (01-12)  
%I   小时 (01-12)  
%i   分钟，数值(00-59)  
%j   年的天 (001-366)  
%k   小时 (0-23)  
%l   小时 (1-12)  
%M   月名  
%m   月，数值(00-12)  
%p   AM 或 PM  
%r   时间，12-小时（hh:mm:ss AM 或 PM）  
%S   秒(00-59)  
%s   秒(00-59)  
%T   时间, 24-小时 (hh:mm:ss)  
%U   周 (00-53) 星期日是一周的第一天  
%u   周 (00-53) 星期一是一周的第一天  
%V   周 (01-53) 星期日是一周的第一天，与 %X 使用  
%v   周 (01-53) 星期一是一周的第一天，与 %x 使用  
%W   星期名  
%w   周的天 （0=星期日, 6=星期六）  
%X   年，其中的星期日是周的第一天，4 位，与 %V 使用  
%x   年，其中的星期一是周的第一天，4 位，与 %v 使用  
%Y   年，4 位  
%y   年，2 位  

本题中的形式可以用DATE_FORMAT(t.order_time,'%Y-%m')把时间格式化成表格中的形式（年份-月份），然后按照题目要求的别名返回即可。