Two columns condition in different rows in PostgreSQL

Question

I have a table with about a 100 columns and 30000 of rows. Look something like this:

site_id   cell_id   sector_id    value1     value2
  1          1           1          70        23
  1          2           1          40        20
  1          3           1          67        35
  1          5           2          42        60
  1          6           2          65        30
  1          7           2          62        62
  2          11          1          67        11
  2          12          1          45        22
  2          13          1          65        15

For the same sector_id of one site_id, if value1>=65 than any cell_id in that same sector with value2<25 would be classified as "LOW_LOAD_CELL". Desired output would be:

site_id   cell_id   sector_id    value1     value2   cell_status
  1          1           1          70        23     LOW_LOAD_CELL
  1          2           1          40        20     LOW_LOAD_CELL
  1          3           1          67        35
  1          5           2          42        60
  1          6           2          65        30
  1          7           2          62        62
  2          11          1          67        11     LOW_LOAD_CELL
  2          12          1          45        22     LOW_LOAD_CELL
  2          13          1          65        15     LOW_LOAD_CELL
 ...

I don't know how to approach in SQL, honestly. I have tried with WHEN CASE, but I stacked when I needed to write condition for value2.

If it was a last row with sector_id = 2, you was right, I've edited. Thank you. — jovicbg
– jovicbg, Commented Aug 3, 2017 at 15:00
This won't help and I'm sorry about it, but I think your problem is your trying to get unrelationnal data from a relationnal database with a language that fit relationnal databases (SQL)... I would be very surprised if a model that includes relations with 100 columns met the third normal form... — Arkhena
– Arkhena, Commented Aug 3, 2017 at 15:10
I think it's possible but with a few queries, it's hard to with one. — jovicbg
– jovicbg, Commented Aug 3, 2017 at 15:17

Vao Tsun · Accepted Answer · 2017-08-03 14:54:40Z

8

try:

select *
, case when value1>=65 and min(value2) over (partition by site_id, sector_id)<25 then 'LOW_LOAD_CELL' end cell_status 
from your_table

answered Aug 3, 2017 at 14:54

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-08-03 14:59:45Z

7

I think the logic you actually want is:

select t.*,
       (case when max(value1) over (partition by site_it, sector_id) >= 65 and
                  value2 < 25
             then 'LOW_LOAD_CELL'
        end) as cell_status 
from t ;

This conforms to your data -- if any row for a sector/site combination has value1 of 65 or over, then that cell is a low load cell when its value2 is less than 25.

answered Aug 3, 2017 at 14:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

jovicbg Over a year ago

If in same one site_id and same one sector_id any cell_id have value_1 >= 65 than any cell_id which have value_2<25 in the same site_id and same sector_id is classified as "LOW_LOAD_CELL"

Gordon Linoff Over a year ago

@jovicbg . . . Cells 2 and 12 suggest that the rule is the other way around.

jovicbg Over a year ago

No, if in that same sector of the same site_id any cell has value1 over 65, then any cell in that site_id and sector_id which have value2 under 25 is LOW_LOAD_CELL. Cells 1 and 11 has value1 over 65, and cells 2 and 12 value2 under 25, so they are LOW_LOAD_CELL.

xycf7 · Accepted Answer · 2017-08-15 08:37:34Z

0

You can try this:

select t.*, case when t2.cnt > 0 and value2 < 25 then 'LOW_LOAD_CELL' end cell_status
from mytable as t left join
( select site_id, sector_id, count(*) cnt from mytable where 
  value1 >= 65 group by site_id, sector_id ) as  t2
on t.site_id = t2.site_id and t.sector_id = t2.sector_id

answered Aug 15, 2017 at 8:37

xycf7

9136 silver badges11 bronze badges

Collectives™ on Stack Overflow

Two columns condition in different rows in PostgreSQL

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related