python sqlite string comparison

user_14015920 0
Viewed 153

I have got an sqlite database with multiple rows in a table. One of the colums holds strings with an arbitrary amount of positive integers, so:

1: '### ## # # # ## # ##'
2: '# ## # ## # ##'
3: '# # # ## ## ### #'
...

I have also gotten a comparison string in my python code that also looks like '## # ### #' The comparison string also holds an arbitrary amount of positive integers.

Now if we take a database string as being 'a b c d e f' and our comparison string as 'g h i j k' for example, I need to test if any 3 consecutive numbers in my comparison string(so 'g h i' or 'h i j' or 'i j k') are also consecutive numbers in the database. So basically I need to test for each row in the database if

'g h i' == 'a b c' or 'g h i' == 'b c d' or ...

Is that doable with the way my data is currently saved in the database or do I need to save my data any other way. Either way, how can I do this with SQL?

sliding-window window-functions sqlite sql python
1 Answers

I would propose a different design for your table.

Instead of storing the numbers like this:

grp value
1 '10 100 20 5 70'
2 '100 20 5 35 3 15'

you could normalize the table so that each row contains only 1 value:

id grp value
1 1 10
2 1 100
3 1 20
4 1 5
5 1 70
6 2 100
7 2 20
8 2 5
9 2 35
10 2 3
11 2 15

This way it is easier to concatenate consecutive numbers (in triplets or anything else) so that you can check if the concatenated numbers are contained inside your string.

Create the table:

CREATE TABLE tablename(id INTEGER PRIMARY KEY AUTOINCREMENT, grp INTEGER, value INTEGER);

and for each row use window function LEAD() to get the next number and the one after that so that you create a concatenated triplet.
Then you can use the operator LIKE to check if the triplet is there in your string:

WITH cte AS (
  SELECT *, 
          value || ' ' ||
          LEAD(value, 1) OVER (PARTITION BY grp ORDER BY id) || ' ' || 
          LEAD(value, 2) OVER (PARTITION BY grp ORDER BY id) str
  FROM tablename
)
SELECT grp, MAX(' ' || ? || ' ' LIKE '% ' || str || ' %') flag
FROM cte 
GROUP BY grp;

Or, a better scalable solution:

WITH cte AS (
  SELECT *, 
         CASE WHEN ROW_NUMBER() OVER (PARTITION BY grp ORDER BY id) > 2
           THEN GROUP_CONCAT(value, ' ') OVER (
                    PARTITION BY grp ORDER BY id 
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
                )
         END str
  FROM tablename
)
SELECT grp, MAX(' ' || '6 100 20 5 12 19' || ' ' LIKE '% ' || str || ' %') flag
FROM cte 
GROUP BY grp;

Replace ? with your string.

See a simplified demo.

user_104988280
Related