LINUX.ORG.RU

Как составить оптимальный запрос в sqlite

 , , ,


0

2

Привет, ЛОР.

Есть таблица table1 в базе sqlite вида

| id | id_parent | date | id_status |

В колонке id_parent неуникальные значения. Я хочу получить все id_parent, для которых самая новая запись на основе date имеет статус 2. В колонке date даты не в хронологическом порядке.

Пример

| id | id_parent | date       | id_status |
| 1  | 1         | 2024-03-17 | 2         |
| 2  | 2         | 2020-01-01 | 2         |
| 3  | 1         | 2023-10-12 | 2         |
| 4  | 1         | 2023-12-31 | 1         |
| 5  | 3         | 2020-03-07 | 1         |
| 6  | 3         | 2018-01-01 | 2         |

Для такой таблицы в результате запроса должно быть

| id_parent |
| 1         |
| 2         |

потому что у id_parent 1 самая большая указанная дата 2024-03-17 и статус на эту дату - 2, а для id_parent 2 запись только одни и она со статусом 2. id_parent 3 не попадает в выборку, у него самая большая дата со статусом 1.

Использую такой запрос

SELECT id_parent FROM table1 AS t WHERE date = (SELECT MAX(date) FROM table1 WHERE id_parent = t.id_parent) AND id_status = 2;

Вопрос в том, правильный ли он и насколько он оптимальный для использования при большом кол-ве записей (десятки тысяч).



Последнее исправление: curbar (всего исправлений: 1)

Никогда не используй SELECT в WHERE. Вынеси его в JOIN.

Что-то вроде

SELECT id_parent FROM table1 AS t 
INNER JOIN SELECT MAX(date) date, id_parent FROM table1 t_date ON t_date.id_parent = id_parent
WHERE id_status = 2

Писал наощупь, синтаксис мог и налажать. Ну и за оптимальность тоже не скажу, но это точно лучше, чем подзапрос в WHERE.

Loki13 ★★★★★
()
Последнее исправление: Loki13 (всего исправлений: 1)

нет, не правильный. вообще это похоже на задачу с собеседования, так что гуляй.

asdpm
()
Ответ на: комментарий от Loki13

Что-то сомнительное утверждение.

JOIN же это гарантированный MATERIALZE, который не всегда полезен.

EXPLAIN QUERY PLAN вполне приличный с вложенным подзапросом, на мой взгляд. Точно проще, чем с JOIN, CTE, ROW_NUMBER() и прочими вариантами.

Индексы на date ещё навесить и совсем хорошо. Вроде.

id|parent|notused|detail                                   |
--+------+-------+-----------------------------------------+
 2|     0|      0|SCAN t                                   |
 8|     0|      0|CORRELATED SCALAR SUBQUERY 1             |
14|     8|      0|SEARCH table1 USING INDEX table1_date_IDX|

vs

id|parent|notused|detail                                       |
--+------+-------+---------------------------------------------+
 3|     0|      0|MATERIALIZE tx                               |
10|     3|      0|SCAN tm                                      |
12|     3|      0|USE TEMP B-TREE FOR GROUP BY                 |
54|     0|      0|SCAN tx                                      |
56|     0|      0|SEARCH t USING INDEX table1_date_IDX (date=?)|

Toxo2 ★★★★
()
Ответ на: комментарий от Loki13

В общем случае, не глядя на данные и окружение - спорить не готов. Автору проще самому нагенерировать предполагаемых данных, да проверить все варианты.

В PG я как-то экспериментировал похожий вопрос - к моему удивлению вот именно с подзапросом получалось быстрее всего.

Toxo2 ★★★★
()

Можно сделать с использованием оконных функций:

select
  id_parent
from
(
  select
    id_parent,
    first_value(id_status) over (partition by id_parent order by date desc) st
  from
    t1
)
where
  st = 2
group by
  id_parent
No ★★
()
Последнее исправление: No (всего исправлений: 1)
Ответ на: комментарий от No

Отрабатывает с тем же выводом, что и мой вариант. По скорости пока не понятно. Как посоветовали выше, заполню данными таблицу, чтобы сравнить производительность запроса.

curbar
() автор топика
Последнее исправление: curbar (всего исправлений: 1)
Ответ на: комментарий от Loki13

Почему select в where плох? Читал пару статей по плохим практикам при сырых запросах в sqlite, не видел ничего подобного.

curbar
() автор топика

А я бы зафигачил какой-нить подходящий индекс (лень думать, но на вскидку – (parent_id, date)), сделал бы select * order by parent_id, date – и сворачивал бы вручную, как в map-reduce.

А поскольку мы имеем дело с embedded-движком, хрен вы меня по скорости переплюнете со своими джойнами да подзапросами.

dimgel ★★★★★
()
Последнее исправление: dimgel (всего исправлений: 1)

Вот так ещё можно: https://dbfiddle.uk/ebKq93pY

select * from (
  select *, max(date)
  from tbl
  group by id_parent
)
where id_status = 2

SQLite при max гарантирует что значения в других колонках будут из той же строчки что и сам max: https://www.sqlite.org/lang_select.html#bareagg

Special processing occurs when the aggregate function is either min() or max().

Eshkin_kot ★★
()

десятки тысяч

это в современном соевом мире, где паинт больше сотни мегабайт весит, а у джавафриков в репозиториях вся база хранится

rtxtxtrx
()

select distinct(id_parent) from такая таблица

Я из описанного тобой такой бы запрос составил (ну влоб)

select id_parent from (select id_parent, id_status, max(date) from tbl group by id_parent) where id_status = 2;

Короче раз это первоеч то в голову пришло, то оно скорее всего неправильно…

Я еще посмотрел выше тож самое считай ряяя - значит точно неправильно

rtxtxtrx
()
Последнее исправление: rtxtxtrx (всего исправлений: 2)

в таких случаях надо смотреть или наличие индексов с последующим профилированием запроса. Или добавлять(если есть возможность) нужные индексы. И вообще то говоря «десятки тысяч записей» - это просто ни о чем. Даже если нету индексов(конечно если у тебя условное embedded на 32Кб оперативки - то тогда имеет значение).

Atlant ★★★★★
()
Ответ на: комментарий от Eshkin_kot

Под бессонницу поиграл в эту игру на 1 миллионе записей.

Похоже ваш вариант для SQLite бесспорно лучший при случае «нет вообще никаких индексов».

Подзапрос без индексов - просто катастрофа. Вот так точно нельзя делать.

Как только делаешь индекс по id_parent - все варианты практически одинаковые по времени (в т.ч. и с подзапросом). Ваш вариант становится чуть хуже самого себя, но остается лучшим всех остальных.

Toxo2 ★★★★
()
Для того чтобы оставить комментарий войдите или зарегистрируйтесь.