Filling up a column with a value that satisfy conditions from other columns-CodePudding

I have table like below:

con_no     pr_no     flag_1  flag_2
con_002   1000234      1       0
con_002   1000345     -1       0
con_002   1100200      1       1
con_002   1005890      0       0
con_003   1100367      0       0
con_003   1000445      1       1
con_003   1200334     -1       0
con_003   1140567      1       0

Now I want another column filled with pr_no whose flag_2 = 1 and where flag_1 = 1 within a specific con_no. So the resultant table should look like

con_no     pr_no     flag_1  flag_2  Parent_pr
con_002   1000234      1       0      1100200 <---This belongs to con_002
con_002   1000345     -1       0      
con_002   1100200      1       1      1100200 <---This belongs to con_002
con_002   1005890      0       0
con_003   1100367      0       0
con_003   1000445      1       1      1000445 <---This belongs to con_003
con_003   1200334     -1       0
con_003   1140567      1       0      1000445 <---This belongs to con_003

How to achieve the above using python (pandas) or SQL. Actually this table in a database (postgreSQL). So a SQL Query string will also do.

There is a catch though. Every times a program runs, the con_no gets reshuffled. Some pr_no may be attached to either a new or an existing (but different con_no).

CodePudding user response：

You should join the table with itself. If your data are in table named my_table, a query like this should work:

with cte as (
    select con_no, pr_no
       from my_table
      where flag_2 = 1)
select t.con_no, t.pr_no, t.flog_1, t.flog_2, cte.pr_no as parent_pf
  from my_table as t
  left outer join cte on (cte.con_no = t.con_no and t.flag_1 = 1)

CodePudding user response：

You can use a window function and conditional logic:

select t.*,
       (case when flag_1 = 1
             then max(case when flag_1 = 1 and flag_2 = 1 then pr_no end) over (partition by con_no)
        end) as parent_pr
from t;

Actually, Postgres supports the filter syntax, so I would write this as:

select t.*,
       (case when flag_1 = 1
             then max(pr_no) filter (where flag_1 = 1 and flag_2 = 1) over (partition by con_no)
        end) as parent_pr
from t;