Gurobi LP file syntax - gurobi

I try to use the GurobiTM Optimizer. Please find below my QP model :
The problems most commonly solved by the Gurobi Parallel Mixed Integer Programming solver are of the form:
Objective: minimize cT x
Constraints: A x = b (linear constraints)
l ≤ x ≤ u (bound constraints)
some or all xj must take integer values (integrality constraints)
Maximize
- x1 + .5 x2 + .5 x3 + x4 - x5 + .5 x6 + .5 x7 - x8
- .17 x1 * x2 + .66 * x1 * x3 + .66 x1 * x4
+ .56 x2 * x3 + .49 x2 * x4
- .17 x5 * x6 + .82 x5 * x7 + .66 x5 * x8
+ .16 x6 * x7 + .49 x6 * x8
Subject To
c1: x1 + x5 <=1
c2: x2 + x6 <=1,
c3: x3 + x7 <=1
c4: x4 + x8 <=1,
c5: x1 + x2 + x3 + x4 <= 2
c6: x5 + x6 + x7 + x8 <= 2
Bounds
0 <= x1 <= 1
0 <= x2 <= 1
0 <= x3 <= 1
0 <= x4 <= 1
0 <= x5 <= 1
0 <= x6 <= 1
0 <= x7 <= 1
0 <= x8 <= 1
Integers
PS PD JS JD AS AD MS MD
End
It seems that according to Gurobi Interactive Shell the syntax is not correct :
Error reading LP format file /.../toyproblem.lp at line 2
Malformed term in expression
Neighboring tokens: " - .17 x1 * x2 + .66 * x1 * "
Any idea ?
Thanks in advance for your help,
MM.

That's not a linear program (LP) - it's a quadratic program (QP) that isn't written in standard form. Additionally, it's very bad form to write a product of binary variables; you should introduce new binary variables to represent the logical condition. For example, replace w1 * w2 by a new variable z with constraints z <= w1, z <= w2, z >= w1 + w2 - 1.

The error was on the third line:
- .17 x1 * x2 + .66 * x1 * x3 + .66 x1 * x4
it should be:
- .17 x1 * x2 + .66 x1 * x3 + .66 x1 * x4 instead
For the record. Gurobi does handle QP and it calls it simplex... although we know it's not really simplex in the real sense. They used something called "active-set methods"
you may want to post questions in their google group. They answer within a day over there :)

Related

Better way to create percentage ranges in oracle 11gr2

I have a table like
Value Average Difference%
1 2 50
5.5 13 43
3 10 30
And I want to turn that into something like
Difference Range #
0-30 1
41-50 2
To later plot that into a histogram-like graph.
After I've wrote the typical case when difference < 10 then 'under 10%' else when ... end I started wondering if there was a better way to do that. Found the PERCENT_RANK, which seemed to go that way but not quite what I was looking for.
Would be better if the step of the rages were fixed, like, each 5%:
case
when dif_perc_med = 0 then 'Ok'
when dif_perc_med < 0.05 then 'até 5%'
when dif_perc_med < 0.1 then 'até 10%'
when dif_perc_med < 0.15 then 'até 15%'
when dif_perc_med < 0.2 then 'até 20%'
when dif_perc_med < 0.25 then 'até 25%'
when dif_perc_med < 0.3 then 'até 30%'
when dif_perc_med < 0.35 then 'até 35%'
when dif_perc_med < 0.4 then 'até 40%'
when dif_perc_med < 0.45 then 'até 45%'
when dif_perc_med < 0.5 then 'até 50%'
when dif_perc_med < 0.55 then 'até 55%'
when dif_perc_med < 0.6 then 'até 60%'
when dif_perc_med < 0.65 then 'até 65%'
when dif_perc_med < 0.7 then 'até 70%'
when dif_perc_med < 0.75 then 'até 75%'
when dif_perc_med < 0.8 then 'até 80%'
when dif_perc_med < 0.85 then 'até 85%'
when dif_perc_med < 0.9 then 'até 90%'
when dif_perc_med < 0.95 then 'até 95%'
else 'mais de 95%'end as rng_perc_dif
Any ideas?
You can use simple arithmetics to do that:
SELECT TRUNC("Difference"/5) "Rank", COUNT("Difference")
FROM T
GROUP BY TRUNC("Difference"/5);
If you need all values (incl. "missing" ones), you could use a join on some generated table:
SELECT ("Rank")*5 "From", ("Rank"+1)*5 "To", NVL(V."Cnt", 0) "Cnt"
-- from N to N+1
FROM (
SELECT LEVEL-1 "Rank" FROM DUAL CONNECT BY LEVEL <= 100/5
-- ^^
-- start from zero
) C
LEFT JOIN (
SELECT TRUNC("Difference"/5) "Rank", COUNT("Difference") "Cnt"
FROM T
GROUP BY TRUNC("Difference"/5)
) V
USING("Rank")
ORDER BY "Rank"
In this example, I've hard-coded all the 5 -- for production code you should very probably turn them into parameters.
Starting with 10g (I think) Oracle does support the WIDTH_BUCKET function that perform mostly the same job as the above arithmetics. So you could rewrite that query as this:
SELECT ("Rank"-1)*5 "From", ("Rank")*5 "To", COUNT(V.ROWID) "Cnt"
-- from N-1 to N
FROM (
SELECT LEVEL "Rank" FROM DUAL CONNECT BY LEVEL <= 100/5
-- ^^
-- start from one
) C
LEFT JOIN (
SELECT WIDTH_BUCKET("Difference",0,100,20) "Rank"
FROM T
) V
USING("Rank")
GROUP BY "Rank"
ORDER BY "Rank";
See http://sqlfiddle.com/#!4/d7398/29 for a a live test of those various solutions

SQL Row wise total value

I have a table named calcu
id date name s1 s2 s3 s4 min_value
1 02/10/2017 dicky 7 4 8 9 4
2 02/10/2017 acton 12 15 17 19 15
3 02/10/2017 adney 28 13 19 10 13
This is my table in SQL Fiddle
I need row wise total value. I means in a new column total, it will be (s1 + s2 + s3 + s4) i.e. (7+4+8+9) = 28 where id=1, (12+15+17+19)=63 where id=2, (28+13+19+10)=70 where id=3 respectively.
Result will be like below:
id date name s1 s2 s3 s4 min_value Total
1 02/10/2017 dicky 7 4 8 9 4 28
2 02/10/2017 acton 12 15 17 19 15 63
3 02/10/2017 adney 28 13 19 10 13 70
see my problem here
It results all total 161 and 3 rows become 1 row.
How to write SQL query?
The SUM() function is an aggregate function. As with other aggregates, use it only to compute values across multiple rows.
You want to add up values in one row, so just use the + operator (brackets are optional).
As for finding the minimum value in the row, use CASE WHEN with 3 tests, comparing S1, S2, S3 and S4.
This should work:
select
c.id, c.date, c.name, c.s1, c.s2, c.s3, c.s4,
(c.s1 + c.s2 + c.s3 + c.s4) as total,
case
when c.s1 <= c.s2 and c.s1 <= c.s3 and c.s1 <= c.s4 then c.s1
when c.s2 <= c.s1 and c.s2 <= c.s3 and c.s2 <= c.s4 then c.s2
when c.s3 <= c.s2 and c.s3 <= c.s1 and c.s3 <= c.s4 then c.s3
when c.s4 <= c.s2 and c.s4 <= c.s3 and c.s4 <= c.s1 then c.s4
end as min_value
from calcu c
;
See SQLFiddle
select c.id,
c.date, c.name, c.s1, c.s2, c.s3, c.s4,
least(s1,s2,s3,s4) Minvalue,
(s1+s2+s3+s4) Total
from calcu c
I tried simplifying the query. So you are looking for the minimum value among s1,s2,s3 and s4. You can achieve with least function. And you need a total of all four 's' columns. Just add them
SELECT *,s1+s2+s3+s4 as Total FROM calcu

SELECT A, B, C, GMTDATE FROM TABLE_NAME: with constant GMT

SELECT A, B, C, GMTDATE
FROM TABLE_NAME
Here when Data is large.
A B C GMTDATE
X1 Y1 Z1 2017.06.05.17:17:59
X2 Y2 Z2 2017.06.05.17:17:59
X3 Y3 Z3 2017.06.05.17:17:59
X4 Y4 Z4 2017.06.05.17:18:00
Exmplanation: I want same GMT date for all data.
Then GMT Date will be different at every second in the table: it display's time of at the time of execution. Where I want same GMTDATE for ALL Data. Either it should be GMT DATE of starting of execution the query or Execution end of the query.
SELECT A, B, C, DT.gmtvalue
FROM TABLE_NAME, (select GMTDATE gmtvalue) DT

SQL query to Transpose columns into rows

can somebody help with a query for below problem
Return Table
Id Factor monthlyReturns price date
A1 0.2 0.001 2010-01-29
A1 0.2 0.003 2010-02-26
A1 0.2 0.004 2010-03-31
A1 0.2 0.004 2010-04-30
A2 0.1 0.001 2010-01-29
A2 0.1 0.001 2010-02-26
A2 0.1 0.001 2010-03-31
A2 0.1 0.001 2010-04-30
A3 0.3 0.03 2010-01-29
A3 0.3 0.04 2010-02-26
A3 0.3 0.05 2010-03-31
A3 0.3 0.05 2010-04-30
A4 0.4 0.12 2010-01-29
A4 0.4 0.12 2010-02-26
A4 0.4 0.14 2010-03-31
A4 0.4 0.15 2010-04-30
I want to convert this data into following format .Can somebody please suggest a query
A1 A2 A3 A4 Total
31-Jan-10 0.001 0.001 0.03 0.12 0.001*0.2(Factor for A1)+0.001*0.1(Factor forA2)+
0.03*0.3(Factor forA3)+ 0.12*0.4(Factor forA4)
28-Feb-10 0.003 0.001 0.04 0.12 Same as above
31-Mar-10 0.004 0.001 0.05 0.14 Same as above
30-Apr-10 0.004 0.001 0.05 0.15 Same as above
Thanks
below is the query you asked for, notice that you need to change the table name to yours.
a little explanation:
I assumed the Ids are limited to A1,A2,A3,A4. otherwise this will not work and you will need a more complexed solution.
first i created a common table expression of a pivoted table with all the factors grouped by date (assuming that there can not be two factors to the same id on the same date)
then, i join that with a pivoted table that holds all the monthly returns.
note: i used the sum function on the fields A1,A2,.. since i needed to group it by date and the select can only use aggregate function if the column is not in the group by clause.
this does not effect the values since you only have one row for each id on a particular date, but correct me if i'm wrong.
with FactorsByDate(PriceDate,FactorA1,FactorA2,FactorA3,FactorA4)
AS
(
SELECT [price date] AS Date,
SUM([A1]) AS A1, SUM([A2]) AS A2, SUM([A3]) AS A3, SUM([A4]) AS A4
FROM
(SELECT Id, Factor , [price date]
FROM TempTbl) AS SourceTable
PIVOT
(
SUM(Factor)
FOR Id IN ([A1], [A2], [A3], [A4])
) AS PivotTable
group by [price date]
)
SELECT Date,A1,A2,A3,A4, A1*FactorA1 + A2*FactorA2 + A3*FactorA3 + A4*FactorA4 AS Total
FROM
(
SELECT [price date] AS Date,
SUM([A1]) AS A1, SUM([A2]) AS A2, SUM([A3]) AS A3, SUM([A4]) AS A4
FROM
(SELECT Id, monthlyReturns , [price date]
FROM TempTbl) AS SourceTable
PIVOT
(
SUM(monthlyReturns)
FOR Id IN ([A1], [A2], [A3], [A4])
) AS PivotTable
group by [price date]
) AS monthlyReturns
INNER JOIN FactorsByDate ON FactorsByDate.PriceDate = monthlyReturns.Date

Closest two values from certain value

I have a table with the points X and Y. I need to find the X and Y point closest to the origin (0, 0).
I am trying this way:
SELECT *
FROM `line`
WHERE xi < yi and 0 < xi and 0 < yi and yi < xi
ORDER BY yi and xi ASC
Limit 100
But I am not getting the desired values.
The distance to the origin is given by sqrt(xi^2 +yi^2). Since a square root is strictly ascending, you can omit it for the purpose of ordering. That gives:
SELECT *
FROM `line`
ORDER BY xi*xi + yi*yi
Limit 100
SELECT xi, yi, xi * xi + yi * yi As r2
FROM line
ORDER BY r2
You need to calculate the distance d = sqrt(x²+y²) to get the nearest point from the origin
select x, y, sqrt(x*x + y*y) as distance
from `line`
order by distance asc
limit 1

Resources