everyone!
I have a table, before it I appended rang for each purchase. Like this:
| id | date | time | title | rang |
|---|---|---|---|---|
| 1 | 2023-03-03 | 2023-03-03 10:00 | A | 1 |
| 1 | 2023-03-03 | 2023-03-03 10:10 | B | 2 |
| 1 | 2023-03-03 | 2023-03-03 10:20 | BUY C | 3 |
| 1 | 2023-03-03 | 2023-03-03 10:20 | D | 4 |
| 1 | 2023-03-03 | 2023-03-03 10:30 | E | 5 |
| 1 | 2023-03-03 | 2023-03-03 10:40 | BUY F | 6 |
| 1 | 2023-03-03 | 2023-03-03 10:50 | G | 7 |
| 2 | 2023-03-03 | 2023-03-03 12:00 | A_1 | 1 |
| 2 | 2023-03-03 | 2023-03-03 12:20 | BUY B_1 | 2 |
| 2 | 2023-03-03 | 2023-03-03 12:20 | C_1 | 3 |
But I would end window function after a certain row, which has "BUY in title. I tried to use:
RANK() OVER (PARTITION BY id ORDER BY time ASC RESET WHEN title LIKE '%BUY%')
But it doesn't work. The result is as followed:
| id | date | time | title | rang |
|---|---|---|---|---|
| 1 | 2023-03-03 | 2023-03-03 10:00 | A | 1 |
| 1 | 2023-03-03 | 2023-03-03 10:10 | B | 2 |
| 1 | 2023-03-03 | 2023-03-03 10:20 | BUY C | 3 |
| 2 | 2023-03-03 | 2023-03-03 12:00 | A_1 | 1 |
| 2 | 2023-03-03 | 2023-03-03 12:20 | BUY B_1 | 2 |
All end on the row with word BUY on a first time
We can use the running total provided by the window function SUM() to identify the initial occurrence of a row containing "BUY" in its title. Subsequently, we can join this dataset with your table based on the condition that the 'rang' value should be lower than that of the first row featuring 'BUY'.
SELECT t.*
FROM mytable t
INNER JOIN (
SELECT *, SUM(CASE WHEN title LIKE '%BUY%' THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY time) AS sm
FROM mytable
) AS s ON s.id = t.id AND t.rang <= s.rang
WHERE s.sm = 1 AND s.title LIKE '%BUY%';
Results :
id date time title rang
1 2023-03-03 2023-03-03 10:00:00 A 1
1 2023-03-03 2023-03-03 10:10:00 B 2
1 2023-03-03 2023-03-03 10:20:00 BUY C 3
2 2023-03-03 2023-03-03 12:00:00 A_1 1
2 2023-03-03 2023-03-03 12:20:00 BUY B_1 2