說，我有一個我在 mySQL 中派生的表：
| 醫生 | 醫生的病人 | patient_bought_item_from_doctor_x_days_ago |
|---|---|---|
| 亞倫 | 杰夫 | 10 |
| 亞倫 | 杰夫 | 20 |
| 杰斯 | 杰森 | 50 |
| 杰斯 | 杰森 | 20 |
| 杰斯 | 杰森 | 30 |
| 亞倫 | 斯圖 | 90 |
| 亞倫 | 斯圖 | 70 |
| 亞倫 | 斯圖 | 110 |
| 亞倫 | 斯圖 | 105 |
現在，我想從這張表中創建一個新表，以便為每位醫生顯示患者的平均購買間隔。
對于這張表，
杰夫 10 天和 20 天前從亞倫那里買的。因此，杰夫的平均買入間隔為 (20-10)/1 = 10。
Jason 在 20、50 和 30 天前從 Jess 購買。Jason 的平均買入間隔為 ((50-30) (30-20))/2 = 15。
Stu 在 90、70、110 和 105 天前從 Aaron 購買。Stu的平均買入區間為((110-105) (105-90) (90-70))/3 = (5 15 20)/3 =40/3 = 13.33
我想輸出一個如下所示的表：
| 醫生 | 醫生的病人 | avg_buy_interval |
|---|---|---|
| 亞倫 | 杰夫 | 10 |
| 杰斯 | 杰森 | 15 |
| 亞倫 | 斯圖 | 13.33 |
我正在認真考慮使用 python 來做到這一點，但我不能放棄向你們學習一些 mySQL 的機會。
謝謝！烏梅什
uj5u.com熱心網友回復：
如果您使用的是 MySQL v8（您可以運行SELECT version();檢查），您可以嘗試使用LEAD()或LAG()函式來獲取 previous 或 next x_days_ago。然后，您可以使用AVG()當前行資料與其 previous 或 next 之間的減法結果x_days_ago。
與LEAD()：
SELECT doctor,
patient,
AVG(bxd-prev_data)
FROM
(SELECT doctor,
patient,
bought_x_days_ago AS bxd,
LEAD(bought_x_days_ago) /*using LEAD*/
OVER (PARTITION BY doctor, patient
ORDER BY bought_x_days_ago DESC) AS prev_data
FROM /*with ORDER BY in descending*/
mytable) v
WHERE prev_data IS NOT NULL
GROUP BY doctor,
patient;
與LAG()：
SELECT doctor,
patient,
AVG(bxd-prev_data)
FROM
(SELECT doctor,
patient,
bought_x_days_ago AS bxd,
LAG(bought_x_days_ago) /*using LAG*/
OVER (PARTITION BY doctor, patient
ORDER BY bought_x_days_ago) AS prev_data
FROM /*with ORDER BY in ascending (default)*/
mytable) v
WHERE prev_data IS NOT NULL
GROUP BY doctor,
patient
演示小提琴
在較舊的 MySQL 版本上，您可以嘗試以下操作：
SELECT doctor,
patient,
AVG(prev_bxd-bxd)
FROM
(SELECT m1.doctor, m1.patient, m1.bought_x_days_ago AS bxd,
SUBSTRING_INDEX(
GROUP_CONCAT(m2.bought_x_days_ago ORDER BY m2.bought_x_days_ago),',',1) AS prev_bxd
FROM mytable m1
LEFT JOIN mytable m2
ON m1.doctor=m2.doctor
AND m1.patient=m2.patient
AND m1.bought_x_days_ago < m2.bought_x_days_ago
GROUP BY
m1.doctor,
m1.patient,
m1.bought_x_days_ago) v
GROUP BY doctor,
patient;
我會盡力解釋它。這里的第一個組件是表的 self LEFT JOIN。連接的條件是精確匹配doctor和patient資料，并匹配第二個bought_x_days_ago比參考表值更大的表。在本SELECT節中，我們使用默認升序的第二個表中的GROUP_CONCAT()over并用于從結果中獲取第一個值。如果沒有，結果如下所示：bought_x_days_agoSUBSTRING_INDEX()GROUP_CONCAT()SUBSTRING_INDEX()
| 醫生 | 病人 | bxd | prev_bxd |
|---|---|---|---|
| 亞倫 | 杰夫 | 10 | 20 |
| 亞倫 | 杰夫 | 20 | 無效的 |
| 亞倫 | 斯圖 | 70 | 90,105,110 |
| 亞倫 | 斯圖 | 90 | 105,110 |
| 亞倫 | 斯圖 | 105 | 110 |
| 亞倫 | 斯圖 | 110 | 無效的 |
| 杰斯 | 杰森 | 20 | 30,50 |
| 杰斯 | 杰森 | 30 | 50 |
| 杰斯 | 杰森 | 50 | 無效的 |
然后有了SUBSTRING_INDEX()，就變成了這樣：
| 醫生 | 病人 | bxd | prev_bxd |
|---|---|---|---|
| 亞倫 | 杰夫 | 10 | 20 |
| 亞倫 | 杰夫 | 20 | 無效的 |
| 亞倫 | 斯圖 | 70 | 90 |
| 亞倫 | 斯圖 | 90 | 105 |
| 亞倫 | 斯圖 | 105 | 110 |
| 亞倫 | 斯圖 | 110 | 無效的 |
| 杰斯 | 杰森 | 20 | 30 |
| 杰斯 | 杰森 | 30 | 50 |
| 杰斯 | 杰森 | 50 | 無效的 |
下一步很簡單，只需將其作為子查詢然后進行平均計算。
這是一個小提琴
uj5u.com熱心網友回復：
要做到這一點，您應該使用 group by 子句，sql其中將是這樣的：
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
在您的情況下，這將是這個 EXACT SQL 代碼：
SELECT doctor,
patient_of_doctor,
AVG(bought_item) as 'avg_buy_interval'
FROM Patients
GROUP BY doctor, patient_of_doctor
你會得到這個結果：
上面查詢的結果
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/492264.html
標籤：mysql