英文:
Is there a way to find rows of a certain value based on the previous row?
问题
我想要选择在表中具有特定值的行，但前一行也必须包括特定值。例如，
| ID | column1 | column2 |
|---|---|---|
| 1 | S | 日期 |
| 1 | T | 日期 |
| 1 | J | 日期 |
| 1 | C | 日期 |
| 2 | D | 日期 |
| 2 | Q | 日期 |
| 2 | L | 日期 |
| 2 | J | 日期 |
| 2 | C | 日期 |
| 3 | L | 日期 |
| 3 | T | 日期 |
| 3 | T | 日期 |
| 3 | C | 日期 |
我只想选择在column2中具有'C'状态的ID，当前一个状态是'T'时。
是否可以做到这一点？
英文:
I'm trying to search in a table rows that have a certain value but the previous row has to include specific values as well. Ex.
| ID | column1 | column2 |
|---|---|---|
| 1 | S | Date |
| 1 | T | Date |
| 1 | J | Date |
| 1 | C | Date |
| 2 | D | Date |
| 2 | Q | Date |
| 2 | L | Date |
| 2 | J | Date |
| 2 | C | Date |
| 3 | L | Date |
| 3 | T | Date |
| 3 | T | Date |
| 3 | C | Date |
I would just want to select on IDs that have 'C' status in column 2 when the status immediately before is 'T'.
Is it possible to do this?
答案1
得分: 0
从Oracle 12版本开始，您可以使用MATCH_RECOGNIZE：
SELECT id, column1, column2FROM table_nameMATCH_RECOGNIZE (PARTITION BY idORDER BY something_that_orders_the_rowsALL ROWS PER MATCHPATTERN ( {- status_t -} status_c)DEFINEstatus_t AS column1 = 'T',status_c AS column1 = 'C');
或者，在早期版本中，您可以使用LAG分析函数：
SELECT id, column1, column2FROM (SELECT id, column1, column2,LAG(column1) OVER (PARTITION BY id ORDER BY something_that_orders_the_rows) AS prev_c1FROM table_name t)WHERE column1 = 'C'AND prev_c1 = 'T';
对于示例数据：
CREATE TABLE table_name (ID, column1, column2, something_that_orders_the_rows) ASSELECT 1, 'S', 'Date', 1 FROM DUAL UNION ALLSELECT 1, 'T', 'Date', 2 FROM DUAL UNION ALLSELECT 1, 'J', 'Date', 3 FROM DUAL UNION ALLSELECT 1, 'C', 'Date', 4 FROM DUAL UNION ALLSELECT 2, 'D', 'Date', 1 FROM DUAL UNION ALLSELECT 2, 'Q', 'Date', 2 FROM DUAL UNION ALLSELECT 2, 'L', 'Date', 3 FROM DUAL UNION ALLSELECT 2, 'J', 'Date', 4 FROM DUAL UNION ALLSELECT 2, 'C', 'Date', 5 FROM DUAL UNION ALLSELECT 3, 'L', 'Date', 1 FROM DUAL UNION ALLSELECT 3, 'T', 'Date', 2 FROM DUAL UNION ALLSELECT 3, 'T', 'Date', 3 FROM DUAL UNION ALLSELECT 3, 'C', 'Date', 4 FROM DUAL;
两者的输出都是：
| ID | COLUMN1 | COLUMN2 |
|---|---|---|
| 3 | C | Date |
英文:
From Oracle 12, you can use MATCH_RECOGNIZE:
SELECT id, column1, column2FROM table_nameMATCH_RECOGNIZE (PARTITION BY idORDER BY something_that_orders_the_rowsALL ROWS PER MATCHPATTERN ( {- status_t -} status_c)DEFINEstatus_t AS column1 = 'T',status_c AS column1 = 'C');
or, in earlier versions, you can use the LAG analytic function:
SELECT id, column1, column2FROM (SELECT id, column1, column2,LAG(column1) OVER (PARTITION BY id ORDER BY something_that_orders_the_rows) AS prev_c1FROM table_name t)WHERE column1 = 'C'AND prev_c1 = 'T';
Which, for the sample data:
CREATE TABLE table_name (ID, column1, column2, something_that_orders_the_rows) ASSELECT 1, 'S', 'Date', 1 FROM DUAL UNION ALLSELECT 1, 'T', 'Date', 2 FROM DUAL UNION ALLSELECT 1, 'J', 'Date', 3 FROM DUAL UNION ALLSELECT 1, 'C', 'Date', 4 FROM DUAL UNION ALLSELECT 2, 'D', 'Date', 1 FROM DUAL UNION ALLSELECT 2, 'Q', 'Date', 2 FROM DUAL UNION ALLSELECT 2, 'L', 'Date', 3 FROM DUAL UNION ALLSELECT 2, 'J', 'Date', 4 FROM DUAL UNION ALLSELECT 2, 'C', 'Date', 5 FROM DUAL UNION ALLSELECT 3, 'L', 'Date', 1 FROM DUAL UNION ALLSELECT 3, 'T', 'Date', 2 FROM DUAL UNION ALLSELECT 3, 'T', 'Date', 3 FROM DUAL UNION ALLSELECT 3, 'C', 'Date', 4 FROM DUAL;
Both output:
| ID | COLUMN1 | COLUMN2 |
|---|---|---|
| 3 | C | Date |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论