How to describe SQL IN statement using Gurobi constraints

Answered

Yifeng Mao

• March 25, 2024 17:42

Hello, I am wondering how to use constraint to efficiently describe the IN statement of the SQL query.

For example, could you give me the gurobi constraint that describes the SQL query of SELECT COUNT(*) WHERE EDUCATION IN 10,11,12 AND GENDER=1 ?

Thank you very much for your help in advance!

0

• 

  Riley Clement

  Gurobi Staff

  • March 25, 2024 22:37

  Hi Yifeng,

  I'm not sure what this question means, there is no relation between Gurobi and SQL.  Is there more context you can provide?

  - Riley

  0
• 

  Yifeng Mao

  • March 26, 2024 12:18

  

  

  

  Hi Riley,
  
      Thanks for your prompt reply.
  
      I think evaluating the SQL queries in a database could be regarded as solving constraint satisfaction problem. The entry values in the database are set up as unknown variables, and the condition in the SQL query and the number of rows satisfying the given condition are considered as constraints. 
  
      To setup a toy example, the database consists of 5 rows and 3 columns (EDUCATION, GENDER, ID), thus, a total of 15 unknown variables. If the SQL query is SELECT COUNT(*) WHERE EDUCATION IN 10,11,12 AND GENDER=1, and if the answer to the SQL query is 3, then it means that 3 of 5 rows satisfying the constraints in WHERE conditions of the query.
  
      How could you describe the constraints in the Gurobi?
  
  Best
  
  Yifeng

  0
• 

  David Torres Sanchez

  Gurobi Staff

  • March 26, 2024 14:01
  • Edited

  Hi Yifeng,

  Do you mean something like this: In Python:

  import gurobipy as gp
  
  education = list(range(5))
  gender = list(range(5))
  idx = list(range(5))
  
  with gp.Model() as m:
      x = m.addVars(education, gender, idx, name="x")
      m.addConstr(gp.quicksum(x.sum(e, 1, "*") for e in [1, 2, 3]) >= 1)

  Cheers, 
  David

  0

Please sign in to leave a comment.