我需要帮助来过滤表中的数据:tbl_entso_cdbf
. 该表包含从一个区域到另一个区域的贸易信息:
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
值的第二行。这意味着area_in275
和area_out40
之间的交易在同一日期和时间2015-12-05 03:00:00
应该具有独特的正值。第 3、4、7 和 8 行也是如此。 但是在第 5 行和第 6 行中,两者都有0
值,所以我只想有一条记录(其中任何一个(。
所以,最后我希望我的桌子看起来像:
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查询吗?
使用此查询查找要删除的行:
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
较小。
查询将返回以下行:
| 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 |
现在在 delete 语句的子查询中使用它:
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
表示所有列值必须相等。如果您有主键(或任何唯一键(,则只需在子查询中选择主键列(唯一键(,而不是*
。
现在表中只剩下以下行:
| 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 |
我假设带有主键的表看起来像这样:
pk | Utc | date |area_in|area_out| value |
-----------------------------------------------------------------------
1 | 2015-12-05T03:00Z |2015-12-05 03:00:00| 275 | 40 | 320 |
2 | 2015-12-05T03:00Z |2015-12-05 03:00:00| 40 | 275 | 0 |
3 | 2015-12-06T03:00Z |2015-12-06 03:00:00| 100 | 175 | 550 |
4 | 2015-12-06T03:00Z |2015-12-06 03:00:00| 175 | 100 | 0 |
5 | 2015-11-04T03:00Z |2015-11-04 03:00:00| 280 | 310 | 0 |
6 | 2015-11-04T03:00Z |2015-11-04 03:00:00| 310 | 280 | 0 |
7 | 2016-09-19T00:00Z |2016-09-19 00:00:00| 187 | 292 | 45 |
8 | 2016-09-19T00:00Z |2016-09-19 00:00:00| 292 | 187 | 0 |
通过执行此查询,您应该能够删除要删除的行:
DELETE FROM tbl_entso_cdbf
WHERE pk IN (SELECT PK_DEL
FROM (Select pk AS PK_DEL
, utc
, value
, ROW_NUMBER() OVER(PARTITION BY utc ORDER BY value desc) AS DUB_IND
FROM tbl_entso_cdbf
WHERE DUB_IND <> 1
)
);
该查询应该删除子查询返回中 pk 所在的所有行。子查询应返回记录的所有主键,其中值与重复项无关。但是,在尝试此操作之前,请先让此查询返回应删除的记录的所需结果:
Select pk AS PK_DEL
, utc
, value
, ROW_NUMBER() OVER(PARTITION BY utc ORDER BY value desc) AS DUB_IND
FROM tbl_entso_cdbf
WHERE DUB_IND <> 1
如果是这样,您应该能够删除重复项!