Перекурсивная функция в PostgreSQL

У меня есть эта таблица:

CREATE TABLE players
(
    winner            CHARACTER VARYING(50) NOT NULL ,
    successor              CHARACTER VARYING(50) NOT NULL ,
    data           NUMERIC(6,2) NOT NULL ,
    CONSTRAINT pk_win_succ PRIMARY KEY (winner, successor)
);

С этими данными:

INSERT INTO players VALUES
  ('Helen','Sharon',12),
  ('Helen','Martina',34),
  ('Martina','Sharon',2),
  ('Claudia','Steffi',35),
  ('Sharon','Penny',5),
  ('Meg','Claudia',21),
  ('Penny','Meg',3),
  ('Steffi','Helen',230);

То, в чем я нуждаюсь, так это функция, которая обращалась бы победительницы, когда дали начальный игрок и другой любой финал.

Например, если я вмещаюсь в Sharon как инициал и Meg как конец, он был бы должен делать следующее:

Initial | Final | List of winners | Total Data | Number of Winners |
Sharon | Meg | Sharon - Penny - Meg | 8 | 3

Если он, он вмещался в Claudia как инициал и в Sharon как этот конец был бы результатом:

Initial | Final | List of winners | Total Data | Number of Winners |
Claudia | Sharon | Claudia - Steffi - Helen - Sharon | 277 | 4
Claudia | Sharon | Claudia - Steffi - Helen - Martina - Sharon | 301 | 5

Я это изменил для того, чтобы он показал различные альтернативы, в которых возможно находить и показывать каждую альтернативу в линии. Он в этой точке, в которой я застрял, спасибо!!! ^^

2
задан 11.02.2017, 15:29
0 ответов

Интересное упражнение. Это консультация, которая функционировала для меня. Просто наладь 2 места, где я оставил тебе комментарий, чтобы меняться человек начала и конца:

with recursive cte as (
  select 1 as level,
         winner,
         successor,
         data
    from players
   where winner = 'Claudia' -- modifica aquí para cambiar el inicio
   union all
  select c.level + 1 as level,
         p.winner,
         p.successor,
         p.data
    from cte c
    join players p
      on p.winner = c.successor
   where c.winner <> 'Sharon' -- modifica aquí para cambiar el final
)
select max(case when level = 1 then winner end) as "Initial",
       max(case when reverse_level = 1 then winner end) as "Final",
       string_agg(winner, ' - ' order by level) as "List of winners",
       sum(case when reverse_level <> 1 then data end) as "Total Data",
       count(*) as "Number of Winners"
  from (select *,
               row_number() over (order by level desc) as reverse_level
          from cte) t;

Demo

Чтобы этого добиваться, я использовал следующие техники:

  • Выражение общей перекурсивной таблицы (WITH RECURSIVE) чтобы переходить с одного сайта на другой реестры перекурсивной формы.
  • Функция окно ROW_NUMBER() чтобы мочь идентифицировать последний реестр.
  • Условные функции добавления (MAX(CASE ... END)).
  • Функция STRING_AGG() чтобы сочетать список победителей в единственном поле.

Редактирование

С настройками в вопрос, я был должен изменять общую технику. Я продолжаю использовать WITH RECURSIVE чтобы переходить с одного сайта на другой реестры перекурсивной формы. Но в отличие от моей предыдущей консультации, вместо того, чтобы использовать функции добавления в конце концов чтобы получать желанные результаты, скорее я иду в осуществляя добавление прогрессивной формы прямо в WITH RECURSIVE. Потом я выдаю реестры для того, чтобы только у меня остались те, которые заканчивают с "конечным" человеком.

Ты ощутишься, что с этой консультацией, ты должен определять человек начала в 1 месте, и конечном человеке в 2 местах:

with recursive cte as (
  select winner,
         successor,
         winner as inicial,
         successor as final,
         winner || ' - ' || successor as list_winners,
         data::numeric(10,2) as total_data,
         2 as num_winners
    from players
   where winner = 'Claudia' -- modifica aquí para cambiar el inicio
   union all
  select p.winner,
         p.successor,
         c.inicial,
         p.successor as final,
         c.list_winners || ' - ' || p.successor as list_winners,
         (c.total_data + p.data)::numeric(10,2) as total_data,
         c.num_winners + 1 as num_winners
    from cte c
    join players p
      on p.winner = c.successor
     and p.winner <> 'Sharon'-- modifica aquí para cambiar el final
)
select inicial as "Initial",
       final as "Final",
       list_winners as "List of winners",
       total_data as "Total Data",
       num_winners as "Number of Winners"
  from cte
 where final = 'Sharon'; -- modifica aquí para cambiar el final

Demo

4
ответ дан 03.12.2019, 17:51
  • 1
    Спасибо за твой explicació n, я изучаю má s aquí что со многими книгами. Я реализовал variació n на упражнении, я существую, так как смогло быть несколько ví схвати до того, чтобы прибывать в судьбу, и что показал эти альтернативы в линиях. Я провожу часы засоренным ahí. Спасибо! ^^ –  07.01.2017, 11:12
  • 2
    То есть cambiastes вопрос :( Tí picamente, это не делается. Скорее, открывается новый вопрос, чтобы избегать объявлять существующие ответы недействительными. Но хороший, для pró xima раз. –  07.01.2017, 16:11
  • 3
    Ok, agregué edició n в ответ, чтобы манипулировать более чем одной альтернативой. –  07.01.2017, 17:14
  • 4
    Для pró xima несомненно. Спасибо ^^ –  08.01.2017, 11:45
  • 5
    Превосходный explicació n учитель @sstan –  11.02.2017, 16:08