Сравнение sqlite кортежей

Question

Попытка сделать то же самое, что и этот вопрос, но на этот раз в sqlite. В моем текущем приложении мне нужно выполнить этот тип запроса:

SELECT First, Last, Score
FROM mytable
WHERE
    ('John',  'Jordan',  5) <= (First, Last, Score )
    AND (First, Last, Score) <= ('Mike',  'Taylor',  50) 
ORDER BY First, Last, Score
LIMIT 1

и получить ответ ('Liz', 'Jordan', 2), учитывая эти данные:

+-------+---------+-------+
| First | Last    | Score |
+-------+---------+-------+
| Liz   | Jordan  |     2 |
| John  | Jordan  |     2 |
| Liz   | Lemon   |    10 |
| Mike  | Taylor  |   100 |
| John  | Jackson |  1000 |
| Mike  | Wayne   |     1 |
| Liz   | Lemon   |    20 |
| Liz   | Meyers  |     5 |
| Bruce | Jackson |     1 |
+-------+---------+-------+

Каков наиболее эффективный способ сделать это в sqlite? Пожалуйста, имейте в виду, что это игрушечный пример, и что мое настоящее приложение имеет таблицы с большим количеством столбцов и типов данных и сотнями миллионов строк.

Если решение легко расширяется на большее / меньшее количество столбцов, это даже лучше.

---

Сравнение кортежей:

Кортежи упорядочены лексикографически, что означает, что последовательности упорядочены так же, как их первые отличающиеся элементы. Например, (1,2,x) <(1,2, y) возвращает то же самое, что и x

Стоит отметить, что SQL-92 (и mysql, oracle, postresql) реализуют это правильно. Стандарт использует "конструктор значений строк" ​​для обозначения того, что я называю кортежем. Поведение определено в мучительных деталях в части 8.2.7, стр. 209.

---

Вот необходимый SQL для создания примера:

create table mytable ( First char(20), Last char(20), Score int );
insert into mytable values ('Liz', 'Jordan', 2);
insert into mytable values ('John', 'Jordan', 2);
insert into mytable values ('Liz', 'Lemon', 10);
insert into mytable values ('Mike', 'Taylor', 100);
insert into mytable values ('John', 'Jackson', 1000);
insert into mytable values ('Mike', 'Wayne', 1);
insert into mytable values ('Liz', 'Lemon', 20);
insert into mytable values ('Liz', 'Meyers', 5);
insert into mytable values ('Bruce', 'Jackson', 1);
create unique index 'UNIQ' on mytable (First, Last, Score);

Answer 1

SQLite не поддерживает сравнение кортежей. Но конструктор строк - это своего рода сокращение. Вы можете получить тот же результат с более сложным предложением WHERE. Я опустил LIMIT 1 чтобы легче было видеть, что оба запроса возвращают один и тот же набор. (На платформах, которые поддерживают конструкторы строк, то есть.)

Это сравнение

ROW(a,b) <= ROW(c,d) 

эквивалентно

a < c OR (a = c AND b <= d)

И вы можете расширить это на столько столбцов, сколько вам нужно.

SELECT First, Last, Score
FROM mytable
WHERE
      (('John' < First) OR 
       ('John' = First AND 'Jordan' < Last) OR 
       ('John' = First AND 'Jordan' = Last AND 5 <= Score))
  AND ((First < 'Mike') OR 
       (First = 'Mike' AND Last < 'Taylor') OR 
       (First = 'Mike' AND Last = 'Taylor' AND Score <= 50))
ORDER BY First, Last, Score

Liz  Jordan  2
Liz  Lemon  10
Liz  Lemon  20
Liz  Meyers  5

Я не проверял это с NULL в данных.

---

Начиная с 2018 года, SQLite поддерживает сравнение кортежей. Запрос OP производит ожидаемый результат, используя предоставленные операторы SQL. Этот способ написания запроса также работает. (Я нахожу between ... and ... более читабельным.)

SELECT First, Last, Score
FROM mytable
WHERE (First, Last, Score ) between ('John',  'Jordan',  5) and ('Mike',  'Taylor',  50) 
ORDER BY First, Last, Score
Limit 1

Я не знаю, как давно это было введено.

Answer 2

Я обошел отсутствие сравнения кортежей с помощью конкатенации строк (||) и последовательность символов, чтобы поля не "сливались" и вызывали неправильные совпадения (-).

(First, Last, Score) <= ('Mike',  'Taylor',  50)

становится

First||' - '||Last||' - '||Score <= 'Mike'||' - '||'Taylor'||' - '||'50'

или же

First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50'

Так что ваши SELECT было бы

SELECT First, Last, Score
FROM mytable
WHERE
    'John - Jordan - 5' <= First||' - '||Last||' - '||Score
    AND First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50'
ORDER BY First, Last, Score
LIMIT 1

Конкатенация строк довольно дорогая и менее краткая, но она работает так же и выглядит очень похоже.

Answer 3

СклайтRaw valueдобавлено в3.15.0: https://sqlite.org/rowvalue.html