我想知道你能不能幫幫我。我不確定這是否可行，但鑒于下面的資料表，我想知道是否可以撰寫一個查詢來輕松顯示每輛車在 carViewed 和 carBought 階段之間所花費的時間。理想情況下，我希望看到 carID 和時間。例如結果應該是這樣的：
| 車牌 | 時間差異 |
|---|---|
| 1 | 00:17:83 |
| 2 | 00:04:21 |
| 3 | 01:57:83 |
資料
| 車牌 | 階段 | 時間戳 |
|---|---|---|
| 1 | 車到了 | 2022-01-20 13:00:00 |
| 1 | 汽車查看 | 2022-01-20 14:00:00 |
| 1 | 買車 | 2022-01-20 14:17:83 |
| 1 | 車左 | 2022-01-20 15:17:83 |
| 2 | 車到了 | 2022-01-21 15:00:00 |
| 2 | 汽車查看 | 2022-01-21 16:00:00 |
| 2 | 買車 | 2022-01-21 16:04:21 |
| 2 | 車左 | 2022-01-21 16:27:83 |
| 3 | 車到了 | 2022-01-22 13:00:00 |
| 3 | 汽車查看 | 2022-01-22 14:00:00 |
| 3 | 買車 | 2022-01-22 15:57:83 |
| 3 | 車左 | 2022-01-22 16:17:83 |
對此的任何幫助將不勝感激。謝謝你。
uj5u.com熱心網友回復：
使用條件聚合：
SELECT carid,
MAX(CASE stage WHEN 'carBought' THEN timestamp END)
- MIN(CASE stage WHEN 'carViewed' THEN timestamp END) AS timeDifference
FROM table_name
GROUP BY carid
其中，對于樣本資料：
CREATE TABLE table_name (CarID, Stage, Timestamp) AS
SELECT 1, 'carArrived', TIMESTAMP '2022-01-20 13:00:00' FROM DUAL UNION ALL
SELECT 1, 'carViewed', TIMESTAMP '2022-01-20 14:00:00' FROM DUAL UNION ALL
SELECT 1, 'carBought', TIMESTAMP '2022-01-20 14:17:53' FROM DUAL UNION ALL
SELECT 1, 'carLeft', TIMESTAMP '2022-01-20 15:17:53' FROM DUAL UNION ALL
SELECT 2, 'carArrived', TIMESTAMP '2022-01-21 15:00:00' FROM DUAL UNION ALL
SELECT 2, 'carViewed', TIMESTAMP '2022-01-21 16:00:00' FROM DUAL UNION ALL
SELECT 2, 'carBought', TIMESTAMP '2022-01-21 16:04:21' FROM DUAL UNION ALL
SELECT 2, 'carLeft', TIMESTAMP '2022-01-21 16:27:53' FROM DUAL UNION ALL
SELECT 3, 'carArrived', TIMESTAMP '2022-01-22 13:00:00' FROM DUAL UNION ALL
SELECT 3, 'carViewed', TIMESTAMP '2022-01-22 14:00:00' FROM DUAL UNION ALL
SELECT 3, 'carBought', TIMESTAMP '2022-01-22 15:57:53' FROM DUAL UNION ALL
SELECT 3, 'carLeft', TIMESTAMP '2022-01-22 16:17:53' FROM DUAL;
輸出：
CARID 時間差異 1 000000000 00:17:53.000000000 2 000000000 00:04:21.000000000 3 000000000 01:57:53.000000000
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/484721.html
上一篇：如何計算每行不同起點之前的事件？
下一篇：從Oracle中的時間戳截斷毫秒