iT邦幫忙

6

[演算法][SQL]演算法挑戰系列(11)-Trips and Users

嗨啊!大家好,又到一個禮拜一次的這個時候了,剛剛我在選題目的時候,突然發現一件事,那就是我們SQL的題目不知不覺快要被破台了XD,所以以後SQL的題目可能會變少,要好好珍惜最後幾題,哈哈哈,不過其實不是因為那個網站的題目少,而是因為大部分的題目都被鎖起來了,他會在每個禮拜持續增加,說不定之後還會有機會分享,那接著就開始這禮拜的題目吧!

題目:Trips and Users
難易度:高
題目內容:沒錯,這一次有個Trips表,內有計程車的出租資料,裡面包含唯一值的Id和客戶編號Client_Id、出租車的司機編號Driver_Id還有出租狀態Status和出租日期Request_at,而出租狀態Status有分完成completed和司機取消cancelled_by_driver及客戶取消cancelled_by_client三種。

另外的Users人員資料表記錄著包括客戶及司機,所有使用者的資料,而他有個Banned欄位記錄著是否為受限用戶,YES為受限,NO為正常使用。

好的,那說了那麼多,這一次我們要做的事情就是,要找出出租日期2013-10-01到2013-10-03未受限使用者中每一天的取消出租比例,並四捨五入到小數點第二位,一句話把題目簡單講完,但我有標記重點XD,可以看看以下例子:

例如:
Trips表內容:

Id Client_Id Driver_Id City_Id Status Request_at
1 1 10 1 completed 2013-10-01
2 2 11 1 cancelled_by_driver 2013-10-01
3 3 12 6 completed 2013-10-01
4 4 13 6 cancelled_by_client 2013-10-01
5 1 10 1 completed 2013-10-02
6 2 11 6 completed 2013-10-02
7 3 12 6 completed 2013-10-02
8 2 12 12 completed 2013-10-03
9 3 10 12 completed 2013-10-03
10 4 13 12 cancelled_by_driver 2013-10-03
Users表內容:
Users_Id Banned Role
------------- ------------- -------------
1 No client
2 Yes client
3 No client
4 No client
10 No driver
11 No driver
12 No driver
13 No driver
查詢結果:
 Day    | Cancellation Rate 

------------- | -------------
2013-10-01 | 0.33
2013-10-02 | 0.00
2013-10-03 | 0.50
Cancellation Rate欄位內的資料就是個日期的未受限用戶取消比例,例如2013-10-01這個日期中有四筆資料,但是Client_Id為2,他是受限用戶,不列入計算,所以2013-10-01剩下三筆出租資料,而三筆資料中又有一筆資料取消,所以取消的比例為1/3四捨五入到小數點第二位為0.33,其他兩天以此類推。

這一題解得還滿順的,以下解法:

/*(1)這邊查詢日期欄位資料*/
SELECT t.Request_at Day, 
/*(8)因為轉成FLOAT的關係,所以用ROUND把小數位數控制在兩位*/
ROUND(
    /*(6)這一層的SUM把內層的資料加總起來,得到取消資料的數量*/
    SUM(
        /*(5)內層先處理,用CASE WHEN判斷把取消的資料變為1,其餘為0*/
        CASE WHEN t.Status = 'cancelled_by_driver' OR t.Status = 'cancelled_by_client' THEN 1 ELSE 0 END
        )
    /*(7)這邊除上用COUNT取得該日期的所有資料數目,
    我把總資料數轉成FOLAT型態,這樣他除起來就會有小數點*/
    /CONVERT(FLOAT,COUNT(*)) 
,2) [Cancellation Rate]

FROM Trips t 
/*(3)只撈出不等於受限資料的使用者*/
WHERE t.Client_Id  NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes') 
/*(4)這邊指定日期,因為題目只要這三天的資料*/
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
/*(2)用GROUP BY讓日期可以把每天的資料作為一個群組,讓我們去計算*/
GROUP BY t.Request_at

然後這次的成績是...我就不貼了XD,哈哈哈,其實不是因為太爛啦,只是我覺得這個出現的區間太大了,這禮拜我1%~96%都跑到過,哈哈哈,個人是覺得和網路速度應該有關係,所以就當參考用就好!

啊啊,其實看了文章感覺這一題的解法大家都差不多,只有些是另外JOIN子查詢計算資料,而我是直接在SELECT寫完,而這次有沒有大大用一樣的方式寫呢?有!而且就在第一則!!不過是MySQL版本的,貼出來給大家看看XD,因為只是換個語言而已,所以我只註解標記的地方,哈哈哈!
原解答網址

SELECT Request_at as Day,
    ROUND(
        COUNT(
            /*因為MySQL沒有CASE WHEN取而代之用IF判斷,如果不等於就是TRUE(1),不然就NULL(0)*/
            IF(Status != 'completed', TRUE, NULL)) 
        / COUNT(*)
    , 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
      AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;

對耶!在幫忙下註解的時候赫然發現,我就直接判斷completed就好了,我還去判斷兩種狀態,真的是一心只想著取消,都忘記換個方向思考了XD,不過我就不改我的版本了,看版上大大有沒有更棒的解法,哈哈哈!

這次我也提供了現在風靡板上的MSSQL連結讓各位大大去玩XD。

話說文章的最後大家有沒有發現這禮拜的難度居然是高級的,哈哈哈,我一直都沒提想說這樣解起來會不會比較簡單,才不會有慘了,這次一定很難,的心理作用XD,啊選擇困難的原因只是單純想快點全破而已,哈哈哈,如果有一起做的大大,現在的進度應該也和我一樣,快解完SQL的題目了,像下面這張圖一樣:
https://ithelp.ithome.com.tw/upload/images/20180713/20106935Ebu62FGnOe.jpg
搞定這題後,大家一起破台SQL的困難級題目/images/emoticon/emoticon07.gif
如果以上文章中有任何不懂的,或是解釋錯誤,都麻煩各位大大留言告訴我,我會盡快改進,謝謝大家/images/emoticon/emoticon41.gif


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 則留言

4
暐翰
iT邦大師 1 級 ‧ 2018-07-14 12:30:54

又到大大每禮拜一挑戰 /images/emoticon/emoticon31.gif

我使用sql-server來做題目
一開始解法跟大大都差不多

後面就想說玩點特別的使用distinct + sum over
相對效能一定會比較差XDD

--把Banned的使用者篩選掉
select distinct [Request_at] [Day]
	--只需要取到小數點二位
	,round(
		--算取消率,特別使用sum少一次轉型
		sum(case when [Status]<>'completed' then 1.00 else 0.00 end)
			over (partition by [Request_at])
		/count(1) over (partition by [Request_at])
	,2) [Cancellation_Rate]
from Trips
where 1=1
	--篩選 只要10/01 - 03的資料 
	and [Request_at] between '2013-10-01' and '2013-10-03'
	--把Banned的使用者篩選掉
	and Client_Id not in (
	  select Users_Id from Users
	  where Banned = 'Yes'
	) 
;

結果:

Day Cancellation_Rate
2013-10-01 0.330000
2013-10-02 0.000000
2013-10-03 0.500000

線上測試連結

神Q超人 iT邦研究生 5 級 ‧ 2018-07-14 14:00:03 檢舉

哈哈,這樣子就不用GROUP BY了!
而且大大直接在CASE WHEN中指定小數點1.00,這我也沒想到XD
這樣就像註解說的,可以少一次轉型!

我想這些SQL的問題已經難不倒大大們了,
以後可能要比誰寫出最有趣的寫法/images/emoticon/emoticon37.gif

沒想到判斷 completed 和直接轉型 1.00 +1

2
小碼農米爾
iT邦高手 1 級 ‧ 2018-07-14 16:08:16

MS SQL 連結

SELECT
	CONVERT(NVARCHAR(10), Request_at, 120)AS [Day],
	ROUND(
		SUM(CASE WHEN [Status]='cancelled_by_client' OR 
                      [Status]='cancelled_by_driver' THEN 1 ELSE 0 END) / 
		CAST(COUNT(Id) AS float), 2
	) AS [Cancellation Rate]
FROM @Trips AS A
LEFT JOIN @Users AS B ON B.Users_Id=A.Client_Id
WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' AND 
      Banned = 'No' 
GROUP BY Request_at
看更多先前的回應...收起先前的回應...
神Q超人 iT邦研究生 5 級 ‧ 2018-07-14 16:16:46 檢舉

這次輕輕鬆鬆的感覺嗎/images/emoticon/emoticon37.gif

哈哈哈,這次蠻輕鬆的,這樣也不錯,我可以留點腦力去想 Code Jam 的題目。

神Q超人 iT邦研究生 5 級 ‧ 2018-07-14 16:26:17 檢舉

我有看到你要再寫一次XD,
這次的時間應該充裕很多,記得上次你是花一個晚上搞定的XD
看大大要不要整理後再發文一次,
換我在文章下面分享解法/images/emoticon/emoticon25.gif

好啊,如果我真的有找到更好解法的話。
/images/emoticon/emoticon16.gif

神Q超人 大大,我後來重做的部分直接更新在原文章上了,
這樣比較可以看出心路歷程,哈哈哈。

新的程式碼我也會回在 MNGhost 大大文章的下方,您也一起阿。
/images/emoticon/emoticon37.gif

神Q超人 iT邦研究生 5 級 ‧ 2018-07-16 08:49:10 檢舉

哈哈哈,好哦!
我現在在想第三題要怎麼解,
我今天晚上先留言另外兩題/images/emoticon/emoticon13.gif

我要留言

立即登入留言