PostgreSQL中的两列不同的行条件

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

我有一个大约有100列和30000行的表。看起来像这样:

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的相同sector_id,如果value1> = 65,那么value2 <25的相同扇区中的任何cell_id将被归类为“LOW_LOAD_CELL”。期望的输出将是:

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.

老实说,我不知道如何在SQL中使用。我曾尝试使用WHEN CASE,但是当我需要为value2写条件时,我会堆积。

3 个解决方案

#1

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

#2

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.

这符合您的数据 - 如果扇区/站点组合的任何行的value1为65或更高,则当该值小于25时,该单元格是低负载单元格。

#3

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

#1