SQL Query of 2 temporary tables and computed mathematical equations to return predicted rows

[pkedpker]

Okay this is pretty complicated to even explain but I will try.

 

I am attempting to match up 2 tables which I have no problem doing at the moment.

But it doesn't take into account that when the 2 tables have rows that match up.

 

The rows which match up after the first row (not always the rows will match up but it's possible).

The newly matched rows will not take into account the previous row changes.

 

Here is the query

 

    SELECT
    S.itemId, S.amount, S.price, S.sold, S.playerHash,
    B.itemId, B.amount, B.price, B.bought, B.playerHash
    FROM Buying AS B JOIN Selling AS S
    ON B.itemId = S.itemId
    AND B.price >= S.price
    AND S.sold < S.amount
    AND B.bought < B.amount
    ORDER BY B.price DESC LIMIT 100

 

When the first Seller finds a Buyer it should compute instantly that the Buyer already bought the Seller's item.

 

`S.amount` is total amount of items Seller has to sell.

`S.sold` is total amount of items Seller has sold (if this S.sold = S.amount then no items left to sell)

`B.amount` is total amount of items Buyer has to buy.

`B.bought` is total amount of items Buyer has bought (if this B.amount = B.bought then no items left to buy)

 

So the first row should simulate both tables change the S.sold and B.bought and Don't Update database for both tables when the row is outputted those will be the predicted changes..

 

(I still wan't to handle changes myself in case the program I do it on crashes then It should be able to re-do the ones the program missed)

 

Calculate amount left to sell.

Calculate amount left to buy.

Calculate proper amount sold and proper amount bought.

 

My math could be wrong here but here is a shot what I am trying to do.

From research you cannot use IF in Sqlite have to resort to using CASE WHEN .. THEN .. ELSE .. END

Then again I don't even know how to use these variables or updating both tables temporarily and joining both updated temporarily tables

 

    amountBuyerNeeds = (B.amount - B.bought)
    amountSellerStock = (S.amount - B.sold)
    B.bought = IF(amountBuyerNeeds > amountSellerStock, (B.bought + amountSellerStock), B.amount)
    S.sold = IF(amountSellerStock > amountBuyerNeeds, (S.sold + amountBuyerNeeds), S.amount)

 

[Barand]

Here's a query that will process the buyers for each seller's items, tracking the amount still available for successive transactions. Price and quantity are maximised.

The output gives the seller code and item code, buyer and the quantity by which the respective sold and bought columns should be updated. Hope it helps.

SELECT seller, itemid, avail, required, qty, buyer 
FROM (
SELECT 
  @req := Bamount - bought as `required`
, @cumsold := CASE
    WHEN @prevseller=seller AND @previtem=itemid
        THEN @cumsold + @qtysold
        ELSE sold
    END as cumsold
, @avail := Samount - @cumsold as avail   
, @qtysold := LEAST(@avail, @req) as qty
, buyer
, @prevseller := seller as seller
, @previtem := itemid as itemid
FROM
    (
    SELECT
        S.itemid
        , S.amount as Samount
        , S.price
        , S.sold
        , B.amount as Bamount
        , B.price as Bprice
        , B.bought
        , B.playerHash as buyer
        , S.playerhash as seller
    FROM Buying AS B 
        JOIN Selling AS S ON B.itemId = S.itemId
    WHERE (S.amount-S.sold)>=(B.amount-B.bought)
        AND B.price >= S.price
        AND S.sold < S.amount
        AND B.bought < B.amount
    ) as matched_transactions
        JOIN (SELECT @qtysold:=0, @cumsold:=0, @prevseller:=0, @previtem:=0, @req:=0, @avail:=0) as init
ORDER BY seller, itemid, Bprice DESC, required DESC
)  as main
WHERE qty > 0
 

Sample output

+--------+--------+-------+----------+------+-------+
| seller | itemid | avail | required | qty  | buyer |
+--------+--------+-------+----------+------+-------+
|      3 |      5 |     7 |        7 | 7    |    12 |
|      5 |      1 |     4 |        3 | 3    |   100 |
|      5 |      1 |     1 |        1 | 1    |    78 |
|      7 |      5 |     1 |        1 | 1    |     4 |
|     17 |      1 |     2 |        1 | 1    |    78 |
|     17 |      1 |     1 |        2 | 1    |    13 |
|     17 |      5 |     9 |        8 | 8    |    35 |
|     17 |      5 |     1 |        7 | 1    |    12 |
|     19 |      5 |     2 |        2 | 2    |    73 |
|     20 |      5 |     2 |        2 | 2    |    73 |
|     21 |      1 |     4 |        3 | 3    |   100 |
|     21 |      1 |     1 |        1 | 1    |    78 |
|     21 |      5 |     3 |        3 | 3    |    18 |
|     23 |      1 |     5 |        3 | 3    |   100 |
|     23 |      1 |     2 |        1 | 1    |    78 |
|     23 |      1 |     1 |        2 | 1    |    13 |
|     23 |      5 |     1 |        1 | 1    |     4 |
+--------+--------+-------+----------+------+-------+