SQL,資料分析崗的必備技能,你可以不懂Python,R,不懂可視化,不懂機器學習。但SQL,你必須懂。要不然上司讓你跑個資料來彙......,哦不,你不懂SQL都無法入職資料分析崗,更别說上司了。
SQL難嗎?說實話,要寫好,很難很難。但要通過SQL筆試這關,并不難。相信大夥都使用過Excel,用SQL實作excel 常用操作去學,感覺會比較具體。我自身也剛入資料崗不久,本文也是為自己鞏固一下SQL。
資料是網上找到的銷售資料,命名為sale,長這樣:
01. 關聯公式:Vlookup
vlookup是excel幾乎最常用的公式,一般用于兩個表的關聯查詢等。是以我先建立一個新表:複制sale表并篩選出地區僅為廣州的,命名為sale_guang。
create table sale_guang
SELECT * from sale where city="廣州";
需求:根據訂單明細号關聯兩表,并且sale_guang隻有訂單明細号與利潤兩列
SELECT * from sale a
inner JOIN
(SELECT ordernum,profit from sale_guang) b
on a.ordernum=b.ordernum
02. 對比兩列差異
需求:對比sale的訂單明細号與sale_guang訂單明細号的差異;
SELECT * from sale a
WHERE a.ordernum not in
(SELECT b.ordernum from sale_guang b);
03. 去除重複值
需求:去除業務員編碼的重複值
SELECT * FROM sale
where salesnum not in
(SELECT salesnum from sale
GROUP BY salesman
HAVING COUNT(salesnum)>1)
04. 缺失值處理
需求:用0填充缺失值或則删除有地區名稱缺失值的行。
--用0填充:
update sale set city = 0 where city = NULL
--删除有缺失值的行:
delete from sale where city = NULL;
05. 多條件篩選
需求:想知道業務員張愛,在北京區域賣的商品訂單金額大于等于6000的資訊。
SELECT * from sale
where salesman = "張愛"
and city = "北京"
and orderaccount >=6000;
06. 模糊篩選資料
需求:篩選存貨名稱含有"三星"或則含有"索尼"的資訊。
SELECT * from sale
where inventoryname like "%三星%"
or 存貨名稱 like "%索尼%";
07. 分類彙總
需求:北京區域各業務員的利潤總額。
SELECT city,sum(`profit`)
from sale
WHERE city = "北京"
GROUP BY `city`;
08. 條件計算
需求:存貨名稱含“三星字眼”并且稅費高于1000的訂單有幾個?這些訂單的利潤總和和平均利潤是多少?
--有多少個?
SELECT COUNT(*) from sale
where inventoryname like "%三星%"
and `tax` > 1000 ;
--這些訂單的利潤總和和平均利潤是多少?
SELECT `ordernum`,SUM(profit),AVG(`profit`)
from sale
where inventoryname like "%三星%"
and `tax` > 1000
GROUP BY `ordernum`;
09. 删除資料間的空格
需求:删除存貨名稱兩邊的空格。
SELECT trim(inventoryname) from sale;
10. 合并與排序列
需求:計算每個訂單号的成本并從高到低排序(成本 = 不含稅金額 - 利潤)
SELECT city,ordernum,
(Nontaxamount - profit) as cost
from sale
order by cost DESC;
總結:結構化查詢語言(Structured Query Language)簡稱SQL,果然和它名字一樣,查詢起來得心應手,但做想做資料處理方面,能明細感受到比Python和excel吃力(也可能是我還沒學好orz)。
SQL筆試題原題
貼一些我在面試時遇到過的SQL筆試題吧:
某資料服務公司:
Student表
Score表
(1)查詢Student表中的所有記錄的Sname、Ssex和Class列。
select sname,ssex,class from student;
(2)查詢Score表中成績在60到80之間的所有記錄。
select * from score between 60 and 80;
(3)查詢95033班和95031班的平均分。
select class,avg(degree) from Score a
join student b
on a.sno = b.sno
GROUP BY CLASS;
總之是比較簡單的SQL筆試題了,當時很快就寫完了。實際上這不是原題,不過我有印象就是考察這幾個知識點,并且蠻簡單的。
某手遊公司的SQL筆試題(原題)
(1)建立表Student的語句寫下來,表Student是由學号Sno,姓名Sname,性别Ssex,年齡Sage,所在系Sdept五個屬性組成,其中學号屬性不能為空,并且其值是唯一的。
create table Student_new
(sno varchar(20) PRIMARY KEY,
sname varchar(10),ssex char(2),
sage int,sdept varchar(25));
(2)在student 表中查詢Sdept是“計算機”的學生所有資訊并按SNO列排序。
select * from student
where sdept = "計算機"
order by sno ;
(3)在以上三個表中查詢Ccredit為5并且Grade大于60的學生的學号、姓名和性别。
select a.sno,a.sname,a.ssex from student a
join (Course b ,SC c)
on a.sno=c.sno and b.cno =c.cno
where Ccredit = 5 and Grade > 60;
某網際網路金融公司SQL筆試題(原題)
(1)表A和表B的交集:
SELECT a.cus_id from `表a` as a
INNER JOIN `表b` as b
on a.cus_id=b.cus_id;
(2)表A和表B的并集:
SELECT * from `表a`
UNION
SELECT * from `表b`;
(3)表A和表B的對稱差:
SELECT * from `表a`
where cus_id not in (SELECT * from `表b`)
UNION
SELECT * from `表b`
where cus_id not in (SELECT * from `表a`);
(4)表A中存在但表B中不存在:
SELECT * from `表a`
WHERE cus_id not in (SELECT cus_id from `表b`);
作者:雲朵君
背景回複資料分析入門,擷取資料分析入門資料
加入資料分析資料群,一起交流資料分析知識
『大話資料分析』
和作者一起學習資料分析!