我想使用 SQL 來分析我擁有的這個呼叫資料。我想回答的主要問題是：如果呼叫者第一次撥打一個號碼并沒有得到回應，那么some_factor（布爾列）的值是否會影響呼叫者在稍后重撥相同號碼時獲得回應的概率？
以下是此表中的列：
| id | phone_number | callee_name | caller_id | call_duration | some_factor | created_at |
|----|--------------|-------------|-----------|---------------|-------------|---------------------|
| 1 | 123-456-7890 | John Smith | 42 | | t | 2022-03-07 09:40:40 |
這是我經過一番思考后得出的結論，但我對 SQL 查詢相對缺乏經驗。如果它們有效，我可以查看來自查詢 1 的計數與來自查??詢 2 的計數的比率，然后重做some_factor錯誤的查詢。
-- 1. successful retried calls: the callee picked up
SELECT COUNT(*)
FROM phone_calls
WHERE phone_number IN (
SELECT phone_number
FROM phone_calls
WHERE call_duration IS NULL
AND some_factor
)
AND call_duration IS NOT NULL
-- 2. unsuccessful retried calls: the callee did not pick up
SELECT COUNT(*)
FROM phone_calls
WHERE phone_number IN (
SELECT phone_number
FROM phone_calls
WHERE call_duration IS NULL
AND some_factor
)
AND call_duration IS NULL
-- then, do math with the results of these queries to get success rate when some_factor is true
-- then, redo but with AND NOT some_factor to get success rate when some_factor is false
But, I realized that 2 is counting entries from the subquery in the total count. I want the COUNT to not include the first time that someone called a number, if they did not pick up. Maybe if I can exclude entries with an id that corresponds to entries from the subquery, that will work. But I can't figure out how to do that. Am I missing something?
uj5u.com熱心網友回復：
正如我在第二個查詢中所了解的那樣，您對子查詢本身回傳的結果不感興趣。因此，計數結果會因子查詢回傳的不同電話號碼的數量而變大。你只需要減去那個數字：
SELECT COUNT(*) - COUNT(DISTINCT phone_number)
FROM phone_calls
WHERE phone_number IN (
SELECT phone_number
FROM phone_calls
WHERE call_duration IS NULL
AND some_factor
)
AND call_duration IS NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439763.html
標籤：sql postgresql aggregate-functions
上一篇：哪個視窗函式更快？