Postgresql
如何遞歸查詢 2 列?
抱歉,我的 DBA 技能有點有限,但這裡有一個範例模式(從我的真實表中簡化)
create table test ( id serial primary key, person TEXT, start_point TEXT, end_point TEXT );
這是一些範例數據
insert into test(person, start_point, end_point) values ('Bob', 'a', 'b'), ('Bob', 'b', 'c'), ('Bob', 'c', 'd'), ('Alice', 'a', 'b'), ('Alice', 'b', 'c'), ('Fred', 'a', 'b'), ('Fred', 'c', 'd') ;
注意一個特定的人是如何從 a->b 開始的,然後是 b->c 等等。
問題
我怎樣才能
SELECT
遍歷開始/結束並給我這樣的輸出?| person | place | group_id | |--------|-------|----------| | Bob | a | 1 | | Bob | b | 1 | | Bob | c | 1 | | Bob | d | 1 | | Alice | a | 2 | | Alice | b | 2 | | Alice | c | 2 | | Fred | a | 3 | | Fred | b | 3 | | Fred | c | 4 | | Fred | d | 4 |
請注意 Bob 是如何通過 abcd 的,Alice 是如何通過 abc 的,但 Fred 並沒有完全通過,所以他被分成一個 ab 然後一個 cd,因為他錯過了 bc。
group_id
只能來自一個序列。到目前為止我已經嘗試過 我目前正在做的事情是將整個數據集選擇到 a
CURSOR
中,然後LOOP
對其進行 a ,檢查是否start_point
與end_point
前一行的匹配以及person
匹配,而這種情況是真實的,將它們分組並更新一個表。當我們沒有找到匹配項時,假設它是給一個新人的,然後重新開始計數。
當表增長時,這是非常低效的,而且我在一個多執行緒應用程序中,所以我寧願能夠做一個原子
UPDATE...SELECT
而不是打開一個游標並循環它。謝謝
尚不清楚 group_id 代表什麼,但對於點的遍歷,可以使用遞歸查詢來處理,如下所示:
WITH RECURSIVE movement(id, person, place, end_point, level) as ( select id,person, start_point, end_point, 1 from test union select p.id, c.person, c.end_point, c.end_point , case when p.id=c.id then p.level else p.level + 1 end from test c inner join movement p on (c.start_point=p.end_point or c.start_point=p.place) and c.person=p.person ), movementSum as ( select id,person,place,level from movement m where m.id = (select min(id) from movement t where t.person=m.person and m.place = t.place) ) select * from movementSum ms order by id, place, level
一旦您分享了有關 group_id 的更多資訊,也許可以生成確切的 sql。使用這個 sql fiddle 來決定 group_id 應該代表什麼:http ://sqlfiddle.com/#!17/cc042/2