師兄大廠面試遇到這條 SQL 資料分析題，差點含淚而歸！

雲祁發表於2020-07-08
面試SQL

寫在前面：我是「雲祁」，一枚熱愛技術、會寫詩的大資料開發猿。暱稱來源於王安石詩中一句 [ 雲之祁祁，或雨於淵 ] ，甚是喜歡。


寫部落格一方面是對自己學習的一點點總結及記錄，另一方面則是希望能夠幫助更多對大資料感興趣的朋友。如果你也對 資料中臺、資料建模、資料分析以及Flink/Spark/Hadoop/數倉開發 感興趣，可以關注我的動態 ，讓我們一起挖掘大資料的價值~


每天都要進步一點點，生命不是要超越別人，而是要超越自己！ (ง •_•)ง

一、背景

師兄在面試時遇到了這條SQL題，回來我幫他參謀了下，覺得非常有意思，讓我們一起來看看這道差點吊打師兄的筆試題吧！?

對方給了兩張表（分別是：派工記錄表和打卡記錄表），以及一張需求表（需要我們寫查詢語句得出），內容如下：

二、派工記錄表

1、表示某人從某日開始到某日結束，按要求工作，派工期間每日打卡時間必須在“要求到崗時間”前（含要求時間，精確到分鐘），否則遲到。

例如：

要求7：00，則6:59或者7:00:59 都不算遲到；7:01則視為遲到1分鐘

2、行1中“派工結束日期”為null，表示此人的工作結束時間尚未確定，還在搬磚中；
行2中派工結束日期為2020-02-15，表示派工於02-15日結束。

3、假設員工名字不重複，每人只有一條派工資訊

建表語句如下：

create table work_plan
(
worker_name varchar(10),     --人員
start_date date,             --派工起始日
end_date date,               --派工截止日
sign_time varchar(10)        --派工期間的需要打卡時間
)

insert into work_plan values
('張三','2020-01-01',null,'06:30'),
('李四','2020-02-01','2020-02-15','07:00'),
('王五','2019-12-29','2020-03-30','06:00'),
('趙六','2019-12-29','2020-03-30','06:00')

在這裡插入圖片描述

三、打卡記錄表

在員工每次按指紋考勤時
都會生成一條記錄

建表語句如下：

create table sign_log
(
worker_name varchar(10),
sign_time datetime
)

insert into sign_log values  
('張三','2020-02-16 04:01'),
('張三','2020-02-16 05:02'),
('張三','2020-02-16 06:03'),
('王五','2020-02-16 07:03'),
('王五','2020-02-16 08:03'),
('王五','2020-02-16 09:03')

在這裡插入圖片描述

四、需求

寫一個查詢語句，輸入引數：日期（date），輸出表格如下：

在這裡插入圖片描述
注：
1、2020-02-16李四派工期已結束，不在派工期間不需要計算考勤，故不用顯示
2、趙六在當日沒有打卡，按照遲到算，遲到時間1440分鐘

五、思路

我們要最終得到遲到時間，需要用表二中最早的打卡時間減去表一中要求的截至打卡時間，但很明顯兩個表日期的資料格式不一致，對錶一的時間資料 sing_time 我們需要和年份進行拼接，然後再與表二的打卡時間進行比較，即可得出最終我們需要的遲到時間。?

但在實際寫查詢語句時，我發現那樣會過於繁瑣，變考慮將上述步驟通過 SQL 中自定義函式來實現。先來複習下MySQL中的自定義函式。

自定義函式例項：

先來一個簡單的，建立一個函式將'2009-06-23 00:00:00'這樣格式的datetime時間轉化為'2009年6月23日0時0分0秒'這樣的格式：

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`getdate`$$
CREATE FUNCTION `sp_test`.`getdate`(gdate datetime) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x= date_format(gdate,'%Y年%m月%d日%h時%i分%s秒');
RETURN x;
END $$
DELIMITER ;

解析：

第一句是定義一個結束識別符號，因為MySQL預設是以分號作為SQL語句的結束符的，而函式體內部要用到分號，所以會跟預設的SQL結束符發生衝突，所以需要先定義一個其他的符號作為SQL的結束符；

第二句是如果這個函式已經存在了，就刪除掉，sp_test是資料庫的名字，函式是跟資料庫相關聯的，getdate是函式的名字；

第三句是建立一個函式，（）裡是引數的名字和型別，RETURNS 定義這個函式返回值的型別；

函式體必須放在BEGIN END之間；

DECLARE 是定義函式體的變數，這裡定義一個變數x,預設是空，然後SET給x變數賦值；

RETURN 是返回值，這裡把變數x返回，x的型別必須與第三句中定義的返回型別一致。

呼叫：

SELECT getdate('2009-06-23 00：00：00');

返回 '2009年06月23日00時00分00秒'

六、最終答案

我們先來完成時間處理的自定義函式，程式碼如下：

DELIMITER $$
DROP FUNCTION IF EXISTS func_date_sub $$
-- d1 6:00 d2 '2020-2-16 4:5:0'
CREATE FUNCTION func_date_sub(d1 VARCHAR(20),d2 DATETIME) RETURNS INT
BEGIN
  IF d2 IS NULL THEN
	RETURN -1440;
  ELSE
	RETURN CEIL((UNIX_TIMESTAMP(CONCAT('2020-2-16 ',d1))-UNIX_TIMESTAMP(d2))/60);
  END IF;
  END $$
DELIMITER ;

SQL 查詢語句如下：

select res.worker_name,res.attend,if(res.latetime<0,'是','否') as isLate,if(
res.latetime<0,abs(res.latetime),0) as latetime from
(select ck.worker_name,ck.attend,func_date_sub(ck.sign_time,ck.st) latetime
from (select e.worker_name,e.sign_time,'2020-02-16' as attend,k.st from 
(select * from work_plan 
where datediff(end_date,'2020-2-16')>0 
or end_date is null) e 
left join
(select worker_name,min(sign_time) st 
from sign_log group by worker_name) k 
on e.worker_name = k.worker_name) ck)res;

在這裡插入圖片描述

最終結果還是非常完美的，如果小夥伴有更好的意見，歡迎留言討論~

相關文章