删除分钟比较sql中表中的两个值后记录中的值 [英] delete min. values from records after comparing two values from table in sql

问题描述

我需要帮助从表中过滤数据:tbl_entso_cdbf.该表包含从一个区域到另一区域的贸易信息:

I need help to filter data from table: tbl_entso_cdbf. The table has trade information from one area to other area:

  Utc             |      date         |area_in|area_out|  value  | 
------------------------------------------------------------------
2015-12-05T03:00Z |2015-12-05 03:00:00| 275   |  40    |   320   |
2015-12-05T03:00Z |2015-12-05 03:00:00| 40    |  275   |   0     |
2015-12-06T03:00Z |2015-12-06 03:00:00| 100   |  175   |   550   |
2015-12-06T03:00Z |2015-12-06 03:00:00| 175   |  100   |   0     |
2015-11-04T03:00Z |2015-11-04 03:00:00| 280   |  310   |   0     |
2015-11-04T03:00Z |2015-11-04 03:00:00| 310   |  280   |   0     |
2016-09-19T00:00Z |2016-09-19 00:00:00| 187   |  292   |   45    |
2016-09-19T00:00Z |2016-09-19 00:00:00| 292   |  187   |   0     |

表包含area_in 和area_out 之间的导出和导入值.此表在值列中包含双重条目，请参阅 date 列.例如，前两行具有相同的日期和时间 2015-12-05 03:00:00 但两个不同的值 320 和 0.我只想有一个值 320 并删除具有 0 值的第二行.这意味着在同一日期和时间 2015-12-05 03:00:00 和 area_in 275 和 area_out 40 之间的交易应该具有唯一的正值.同样暗示行号.3、4、7 和 8.但是在第 5 行和第 6 行中都有 0 值，所以我只想有一个记录(其中任何一个).

Table contains export and import values between area_in and area_out. This table contains double entries in value column refere to date column.For ex., first two rows have same date and time 2015-12-05 03:00:00 but two different values 320 and 0. I want to have only one value 320 and delete second row which has 0 value. It means trade between area_in 275 and area_out 40 at same date and time 2015-12-05 03:00:00 should have unique positive value. Same implies for row no. 3, 4, 7 and 8. But in rows 5 and 6 both have 0 values so I want to have only one record (any one of them).

所以，最后我希望我的桌子看起来像:

So, in the end I want my table to be looked like:

Utc               |      date         |area_in|area_out|  value  | 
------------------------------------------------------------------
2015-12-05T03:00Z |2015-12-05 03:00:00| 275   |  40    |   320   |
2015-12-06T03:00Z |2015-12-06 03:00:00| 100   |  175   |   550   |
2015-11-04T03:00Z |2015-11-04 03:00:00| 310   |  280   |   0     |
2016-09-19T00:00Z |2016-09-19 00:00:00| 187   |  292   |   45    |

这个表有数百万这样的行要过滤.谁能帮我写一个sql查询?

This table has millions of such kind of rows to filter. can any one help me to write a sql query ?

推荐答案

使用此查询查找要删除的行:

Find the rows you want to delete with this query:

select t0.* 
from tbl_entso_cdbf t0
join tbl_entso_cdbf t1
  on  t1.Utc  = t0.Utc
  and t1.date = t0.date
  and t1.area_in  = t0.area_out
  and t1.area_out = t0.area_in
where t0.value = 0
  and (t1.value <> 0 or t1.area_in < t0.area_in);

条件是:

• value = 0
• 还有一行具有相同的Utc和相同的date，但是area_in和area_out被切换了.立>
• 另一行的value不是0 或者 area_in更小.

• value = 0
• There is another row with same Utc and same date but area_in and area_out are switched.
• The value of the other row is not 0 or area_in is smaller.

查询将返回以下行:

|               Utc |                date | area_in | area_out | value |
|-------------------|---------------------|---------|----------|-------|
| 2015-12-05T03:00Z | 2015-12-05 03:00:00 |      40 |      275 |     0 |
| 2015-12-06T03:00Z | 2015-12-06 03:00:00 |     175 |      100 |     0 |
| 2015-11-04T03:00Z | 2015-11-04 03:00:00 |     310 |      280 |     0 |
| 2016-09-19T00:00Z | 2016-09-19 00:00:00 |     292 |      187 |     0 |

现在在删除语句的子查询中使用它:

Now use it in a subquery of the delete statement:

delete t1
from tbl_entso_cdbf t1
natural join (
    select t0.*
    from tbl_entso_cdbf t0
    join tbl_entso_cdbf t1
      on  t1.Utc  = t0.Utc
      and t1.date = t0.date
      and t1.area_in  = t0.area_out
      and t1.area_out = t0.area_in
    where t0.value = 0
      and (t1.value <> 0 or t1.area_in < t0.area_in)    
) t0;

NATURAL JOIN 表示所有列值必须相等.如果您有一个主(或任何唯一)键，您只需要在子查询中选择主(唯一)键列而不是 *.

NATURAL JOIN means that all column values must be equal. If you had a primary (or any unique) key, you would only need to select the primary (unique) key columns in the subquery instead of *.

现在表格中只剩下以下几行:

Now only the following rows are left in the table:

|               Utc |                date | area_in | area_out | value |
|-------------------|---------------------|---------|----------|-------|
| 2015-12-05T03:00Z | 2015-12-05 03:00:00 |     275 |       40 |   320 |
| 2015-12-06T03:00Z | 2015-12-06 03:00:00 |     100 |      175 |   550 |
| 2015-11-04T03:00Z | 2015-11-04 03:00:00 |     280 |      310 |     0 |
| 2016-09-19T00:00Z | 2016-09-19 00:00:00 |     187 |      292 |    45 |

这篇关于删除分钟比较sql中表中的两个值后记录中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！