SQLite3 how to compare indices to lists

I’m using Pandas and SQLite3 and have a SQL table called Environment with two saved Environments. Each of those Environments contain of 4 meas_dev_ids and 4 subset_id, see picture. The important difference between the two is that the order of the subset_ids is different in the second one.

SQL Table called Environment

Question: With what query would I get the id of the first Environment if these two lists are given:

  • meas_dev_ids = (1,2,3,4)
  • subset_id = (a,b,c,d)

With the lists

  • meas_dev_ids = (1,2,3,4)
  • subset_id = (b,a,c,d)

I would like it to output the id of the second Environment.

The query I’m using at the moment is:

'SELECT DISTINCT id FROM environment a 
WHERE meas_dev_ids IN (1, 2, 3, 4) 
Group by id 
having count(*) = 4 and not exists 
(SELECT * FROM environment b WHERE meas_dev_ids NOT IN (1, 2, 3, 4) and a.id = b.id)'

With this query I can get the Environment which has exactly the meas_dev_ids (1,2,3,4). It is however not able to discern between the two Environments in the table above.

Important notes:

  • The Environment table can contain Environments with an arbitrary number of subset_id (they don’t have to be of length 4).
  • With the query I would like to get the Environment id with the exact same composition as mentioned in the lists provided. For example if I’m looking for the Environment with meas_dev_ids = (3,4) and subset_id = (c,d), I want the result to be None (in this case), because both saved Environments have more entries.

Answer

You can do it with GROUP_CONCAT() window function:

SELECT id
FROM (
  SELECT id, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY meas_dev_ids DESC) rn, 
         GROUP_CONCAT(meas_dev_ids) OVER (PARTITION BY id ORDER BY meas_dev_ids) meas_dev_ids,
         GROUP_CONCAT(subset_id) OVER (PARTITION BY id ORDER BY meas_dev_ids) subset_ids
  FROM Environment
)
WHERE rn = 1 AND meas_dev_ids = ? AND subset_ids = ?

Replace the ? placeholders with the meas_dev_ids and subset_ids that you search for, as comma separated strings, like '1,2,3,4' and 'a,b,c,d'.

See the demo.

Note: If it returns a syntax error near "(", update sqlite3 by downloading the sqlite3.dll from sqlite.org and replacing it in the PythonDLLs folder.

Leave a Reply

Your email address will not be published. Required fields are marked *