SQL Server 使用SQL删除冲销对

bihw5rsg  于 2022-12-10  发布在  其他
关注(0)|答案(2)|浏览(223)

I have a sql question that is closely related to this question - SQL - Need to find duplicate records but EXCLUDE reversed transactions
I need to remove all reversal "pairs" for a recordset using (if possible) non procedural SQL. The specific rdbms is Oracle 11g, but I would like the SQL to be as generic as possible so the same strategy can be used in SQL Server 2008. The example recordset looks like this:

ROW     |    DATE    |    QTY    |    FUEL_TYPE    |    REVERSAL    |
1        | 01-MAY-12  |    23.3   |    DSL          |    N           |
2        | 01-MAY-12  |   -23.3   |    DSL          |    Y           |
3        | 01-MAY-12  |    23.3   |    DSL          |    N           |
4        | 01-MAY-12  |    23.3   |    DSL          |    N           |
5        | 01-MAY-12  |    23.3   |    DSL          |    N           |
6        | 01-MAY-12  |    18.6   |    DSL          |    N           |
7        | 01-MAY-12  |   -18.6   |    DSL          |    Y           |
8        | 01-MAY-12  |    14.9   |    GAS          |    N           |

The desired outcome of the query would reduce this recordset to:

ROW     |    DATE    |    QTY    |    FUEL_TYPE    |    REVERSAL    |
3        | 01-MAY-12  |    23.3   |    DSL          |    N           |
4        | 01-MAY-12  |    23.3   |    DSL          |    N           |
5        | 01-MAY-12  |    23.3   |    DSL          |    N           |
8        | 01-MAY-12  |    14.9   |    GAS          |    N           |

Notice that duplicates are possible, but the reversal "pairs" always need to be removed.

  • edit* The rows and row numbers are irrelevant and are just used to illustrate. It doesn't really matter which records are removed, just that there is always a "pair" - a positive amount and negative amount. So, for example, row 2 could be paired with 1,3,4 or 5 and removed.

Also, the logic that populates the table and the table structure itself is controlled by vendor software, and DOES NOT include the original id of the record that is being reversed in a reversal record. I don't really have any control over this. /edit
Incidentally, I would love it if the MINUS keyword were changed such that it functioned similar to UNION and UNION ALL - in that MINUS would remove only single rowsets that match from a second recordset, but MINUS ALL removed every row that matches values from a second recordset. If that were the case, this problem would be trivial (at least for the way that my brain thinks).

zd287kbt

zd287kbt1#

It turns out I was looking at the problem in a pretty terrible way. Instead of finding the exact reversal pair, I just did a SUM with a GROUP BY, so only the values that I cared about keeping remained.
The end result is that transactions will end up distinct - especially if, like in my case, the real transaction table is actually a datetime value instead of date.

SELECT SUM(QTY) AS newQTY, DATE, FUEL_TYPE
FROM fuel_transactions
GROUP BY DATE, FUEL_TYPE

The only time this won't produce the values that you really want are if you have a need to maintain the id's of the transactions, or if you have a situation in which multiple transactions occur at the exact same time.

j8yoct9x

j8yoct9x2#

The problem that you face is that the reversals are not assigned directly to the transaction they are reversing.
Assuming that the reversal is an exact reversal, you can take the following approach. For a given set of transaction information, enumerate the reversals and the non-reversals. Then, take all non-reversals that do not match.
Here is an example of SQL to do this:

with t as 
(
  select row, date, qty, fuel_type, reversal,
    ROW_NUMBER() over (partition by row, date, qty, fuel_type, reversal) as rownum
  from table
)
select *
from 
(
   select *
   from t
   where t.reversal = 'N'
 ) n 
 left outer join
 (
   select *
   from t
   where t.reversal = 'Y'
 ) y
    on n.date = y.date 
    and n.qty = y.qty 
    and n.fuel_type = y.fuel_type 
    and n.rownum = y.rownum 
where n.row is null

This should work in both SQL and Oracle, because both support the row_number window/analytic function.
Note that this solution does not guarantee that a reversal is correct. It also assumes that a reversal happened on the same day as the original transaction.

相关问题