SQL練習65：例外的郵件概率-有解無憂

SQL練習65：例外的郵件概率

題目鏈接：牛客網

題目描述
現在有一個需求，讓你統計正常用戶發送給正常用戶郵件失敗的概率:
有一個郵件(email)表，id為主鍵， type是列舉型別，列舉成員為(completed，no_completed)，completed代表郵件發送是成功的，no_completed代表郵件是發送失敗的，簡況如下:
在這里插入圖片描述
第1行表示為id為2的用戶在2020-01-11成功發送了一封郵件給了id為3的用戶;
…
第3行表示為id為1的用戶在2020-01-11沒有成功發送一封郵件給了id為4的用戶;
…
第6行表示為id為4的用戶在2020-01-12成功發送了一封郵件給了id為1的用戶;

下面是一個用戶(user)表，id為主鍵，is_blacklist為0代表為正常用戶，is_blacklist為1代表為黑名單用戶，簡況如下:
在這里插入圖片描述
第1行表示id為1的是正常用戶;
第2行表示id為2的不是正常用戶，是黑名單用戶，如果發送大量郵件或者出現各種情況就會容易發送郵件失敗的用戶
，，，
第4行表示id為4的是正常用戶

現在讓你寫一個sql查詢，每一個日期里面，正常用戶發送給正常用戶郵件失敗的概率是多少，結果保留到小數點后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查詢結果如下:
在這里插入圖片描述
結果表示:
2020-01-11失敗的概率為0.500，因為email的第1條資料，發送的用戶id為2是黑名單用戶，所以不計入統計，正常用戶發正常用戶總共2次，但是失敗了1次，所以概率是0.500;
2020-01-12沒有失敗的情況，所以概率為0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才會得到0.5，sqlite四舍五入的函式為round)

解法
先通過子查詢的方式獲取正常人給正常人發郵件的email表，再通ROUND函式保留三位小數，AVG(type = 'no_completed')的到失敗的概率，最后對date進行分組即可，

SELECT date, round(AVG(type = 'no_completed'), 3) p
FROM (SELECT *
      FROM email
      WHERE send_id NOT IN (SELECT id
                            FROM user
                            WHERE is_blacklist = 1)
      AND
      receive_id NOT IN (SELECT id
                         FROM user
                         WHERE is_blacklist = 1))as r
GROUP BY date

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/259173.html

標籤：其他

上一篇：UBports安裝應用（Libertine）

下一篇：局域網安全之ARP攻擊