我有一個下面的資料集，我試圖在單個列中獲取相同值的最小時間戳。
這是我的資料集。
我正在嘗試創建一個列來查找與經銷商的每次互動的 user_first_comment。
像下面
有什么技術可以用來為列中的相同值添加增量值。例如列 RANK_Incremental，這樣我就可以使用 Min wpartition 來獲得最終輸出。
uj5u.com熱心網友回復：
使用公用表運算式（或派生表）和視窗函式支持，請嘗試以下操作：
WITH step1 AS (
SELECT *, CASE WHEN LAG(note_type) OVER (PARTITION BY incident ORDER BY note_date) = note_type THEN 0 ELSE 1 END AS edge FROM incidents
)
, step2 AS (
SELECT *, SUM(edge) OVER (PARTITION BY incident ORDER BY note_date) AS grp FROM step1
)
SELECT *, FIRST_VALUE(note_date) OVER (PARTITION BY incident, grp ORDER BY note_date) AS first_date FROM step2
;
結果：
---------- --------------------- ---------------- ------ ------ ---------------------
| incident | note_date | note_type | edge | grp | first_date |
---------- --------------------- ---------------- ------ ------ ---------------------
| 5498091 | 2021-12-15 17:20:00 | USER_COMMENT | 1 | 1 | 2021-12-15 17:20:00 |
| 5498091 | 2021-12-15 17:21:00 | USER_COMMENT | 0 | 1 | 2021-12-15 17:20:00 |
| 5498091 | 2021-12-15 17:55:00 | DEALER_COMMENT | 1 | 2 | 2021-12-15 17:55:00 |
| 5498091 | 2021-12-15 17:59:00 | USER_COMMENT | 1 | 3 | 2021-12-15 17:59:00 |
| 5498091 | 2021-12-16 11:02:00 | USER_COMMENT | 0 | 3 | 2021-12-15 17:59:00 |
| 5498091 | 2021-12-16 16:46:00 | DEALER_COMMENT | 1 | 4 | 2021-12-16 16:46:00 |
---------- --------------------- ---------------- ------ ------ ---------------------
設定：
CREATE TABLE incidents (
incident int
, note_date datetime
, note_type varchar(20)
)
;
INSERT INTO incidents VALUES
(5498091, '2021-12-15 17:20', 'USER_COMMENT')
, (5498091, '2021-12-15 17:21', 'USER_COMMENT')
, (5498091, '2021-12-15 17:55', 'DEALER_COMMENT')
, (5498091, '2021-12-15 17:59', 'USER_COMMENT')
, (5498091, '2021-12-16 11:02', 'USER_COMMENT')
, (5498091, '2021-12-16 16:46', 'DEALER_COMMENT')
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/405766.html
標籤：