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

雲祁發表於2020-07-08

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


寫部落格一方面是對自己學習的一點點總結及記錄,另一方面則是希望能夠幫助更多對大資料感興趣的朋友。如果你也對 資料中臺、資料建模、資料分析以及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;

在這裡插入圖片描述

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

相關文章