0

i have a table like this

+--------+------------+-----------+
| id     | volume     | price     |
+--------+------------+-----------+
|      1 | 1.0000     |  14250.00 |
|      2 | 0.2500     |  14251.10 |
|      3 | 0.2500     |  14250.00 |
|      4 | 0.0050     |  14200.00 |
|      5 | 0.5000     |  14255.01 |
+--------+------------+-----------+

Simply if volume 1.7500, how to select like bellow by ordering(highest price)

+--------+------------+-----------+
| id     | volume     | price     |
+--------+------------+-----------+
|      5 | 0.5000     |  14255.01 |
|      2 | 0.2500     |  14251.10 |
|      1 | 1.0000     |  14250.00 |
+--------+------------+-----------+

And if volume 1.5000 it also select the least amounts of records needed to calculate to the input volume

+--------+------------+-----------+
| id     | volume     | price     |
+--------+------------+-----------+
|      5 | 0.5000     |  14255.01 |
|      1 | 1.0000     |  14250.00 |
+--------+------------+-----------+

some guys say i cant do this with sql

  • @memo please merge your accounts: dba.stackexchange.com/help/merging-accounts – ypercubeᵀᴹ Oct 8 '17 at 12:09
  • It's unclear to me how you are deciding to choose records 1, 2 and 5! – Vérace Oct 8 '17 at 13:35
  • Does it need to total exactly 1.7500? Or at least 1.7500? You have posed a mathematical problem; please provide the algorithm, then we can help you turn it into SQL. – Rick James Oct 8 '17 at 16:31
  • you can play with my script using various sample and let me know your problem. – KumarHarsh Oct 24 '17 at 10:35
  • as a individual question ,it is very fantastics. I see only 2 problem in your explanation.First,what is your real situation like ? Often asking real question or original requirement give you perfect answer.Secondly,there are various combination of arriving at your input,so what is the criteria of shortlisting one of the combination. – KumarHarsh Oct 24 '17 at 10:58
2

You would need to use a reduction method to slowly calculate the parts that make up the target volume. For instance 1.75, you would as you say be 1.00, then 0.5 then 0.25. You can use a loop to calculate the remainder using modulus, then use that remainder to find the next largest volume less than or equal to your value.

This is how I would do it (there might be better ways but this works)

CREATE TABLE VolumePrice (
    id int not null,
    volume decimal(9,4) not null,
    price decimal(9,2) not null
);

insert VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),
       (2,0.2500,14251.10),
       (3,0.2500,14250.00),
       (4,0.0050,14200.00),
       (5,0.5000,14255.01);

-- create a temp table to hold the output
create table output (
    id int auto_increment not null primary key, 
    remainder decimal(9,2), 
    volume decimal(9,2)
);

DELIMITER //

create procedure do_insert()
begin
set @v = 1.75;
set @remainder = @v;

while @remainder > 0
do
    insert output(remainder, volume)
    select @remainder % max(volume)
         , max(volume) as volume
      from volumeprice
     where volume <= @remainder;

  set @remainder = (select remainder 
                      from output 
                  order by id desc
                     limit 1);
end while;
end;
//

DELIMITER ;    

CALL do_insert();

select v.*
  from output as o
  join (select volume, max(price) as max_price
          from volumeprice
      group by volume) as vp on vp.volume = o.volume
  -- I only link back to the volumeprice table here to obtain the id, 
  -- if you do't need the id you can remove this part and just select vp.* instead...
  join volumeprice as v 
    on v.volume = vp.volume 
   and v.price = vp.max_price;

And here is a SQL Fiddle that seems to do the trick.

1

It looks like you have two questions.

The first:

Simply if volume 1.7500, how to select like bellow by ordering(highest price)

Here's a solution that should provide that result set:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE (id <> t.id AND price > t.price) OR (id < t.id AND price = t.price) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.7500'
ORDER BY price DESC, id ASC;

Here's the SQLFiddle to validate the output.

As for the second question, it looks like an entirely different problem:

And if volume 1.5000 it also select the least amounts of records needed to calculate to the input volume

I basically order by volume instead of price which should then get what you want:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE volume > t.volume OR (id < t.id AND volume = t.volume) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

Here's the SQLFiddle to validate that output.

1

Same problem can be written solve in PURE SQL without RBAR or REcursive

This script is in Sql server 2005+,i think it can be very easily converted to mySQL.

Let me know the LIMITATION

First create number table.

SELECT TOP (1048576)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.Numbers
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2
CROSS JOIN sys.columns AS c3;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Numbers (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

Then final script,

declare @input decimal(9,4)=1.7500 
if object_id('tempdb..#VolumePrice') is not null 
drop table #VolumePrice

create  table #VolumePrice (
    id tinyint  PRIMARY KEY NONCLUSTERED,
    volume decimal(9,4) not null,
    price decimal(9,2) not null,
     bit_value   AS 
                CONVERT
                (
                    integer, 
                    POWER(2, id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);

insert #VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),(2,0.2500,14251.10),
      (3,0.2500,14250.00),(4,0.0050,14200.00),(5,0.5000,14255.01);


DECLARE 
    @max integer = 
    POWER(2,
        (
            SELECT COUNT(*) 
            FROM #VolumePrice AS s
        )
    ) - 1;

;

WITH CTE
AS (
    SELECT n.n
        ,c.id
        ,c.volume
    FROM dbo.Numbers AS N
    CROSS APPLY (
        SELECT s.id
            ,s.volume
        FROM #VolumePrice AS s
        WHERE n.n & s.bit_value = s.bit_value
        ) c
    WHERE N.n BETWEEN 1
            AND @max
    )
    ,cte1
AS (
    SELECT n
        ,sum(volume) vol
        ,count(n) HavingLeastcount
    FROM cte
    GROUP BY n
    HAVING sum(volume) = @input
    )
SELECT *
FROM cte c
WHERE EXISTS (
        SELECT n
        FROM cte1 c1
        WHERE n = c.n
        )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.