Geek asks and answers

Refresh the table in rows that are partial duplicates

My data set consists of daily (actually working days) deadlines for different companies from different industries, and I work with PostgreSQL. I have an indicator variable in my dataset that takes values ​​1, -1 and most of the time 0. For better readability of the question, I refer to the case when the indicator variable is not zero for this company as an indicator event.

If for a given industry there is more than one indicator event for a given day, the indicator variables of reputable companies should be updated to 0.

We can recall the following sample data set:

day              company     indicator     industry
2012-01-12       A           1             financial
2012-01-12       B           1             consumer
2012-01-12       C           0             consumer
2012-01-13       A           0             financial
2012-01-13       B           1             consumer
2012-01-13       C           0             consumer
2012-01-16       A           1             financial
2012-01-16       B           -1            consumer
2012-01-16       C           1             consumer

, , , B C 2012-01-16 , .

, exist:

    update mytable t1 set indicator = 0
    where exists (
              select 1
              from mytable t2
              where t2.day = t1.day
              and t2.industry = t1.industry
              and t2.indicator <> 0
              and t1.indicator <> 0)

, 0, , .

, , ?

+3
1

, , ( ), .

update mytable t1 set indicator = 0
where exists (
          select 1
          from mytable t2
          where t2.day = t1.day
          and t1.company <> t2.company
          and t2.industry = t1.industry
          and t2.indicator <> 0
          and t1.indicator <> 0)
+1

More articles:


All Articles