On 2019/02/12 1:13 PM, Ivan Krylov wrote:
Hi!
I have a table of some values obtained from different sources:
create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);
//...//
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;
which results in the following query plan:
QUERY PLAN
|--CO-ROUTINE 1
| |--CO-ROUTINE 3
| | |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
| | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
| `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1
Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?
The Window function solution is good, but it can be done easily without
it, resulting in a bit more efficient query.
Here is the query set recreated showing first your previous stated query
and then an alternate. The semantics are made more legible using a CTE
here, but can be done without it. The essential difference is that it
uses a sub-query to obtain the minimum priority as opposed to
introducing a sub-table sort.
-- SQLite version 3.25.1 [ Release: 2018-09-18 ] on SQLitespeed
version 2.1.1.37.
--
================================================================================================
create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);
select * from test;
-- id | source_id | value
-- --- | --------- | ------------
-- 1 | 1 | 11
-- 1 | 2 | 12
-- 1 | 3 | 13
-- 2 | 1 | 21
-- 3 | 2 | 32
-- Original proposed Query:
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;
-- id | value
-- ------------ | ------------
-- 1 | 13
-- 2 | 21
EXPLAIN QUERY PLAN
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;
-- id | parent | notused | detail
-- ------------ | ------ | ------- |
------------------------------------------------------------
-- 2 | 0 | 0 | CO-ROUTINE 1
-- 6 | 2 | 0 | CO-ROUTINE 3
-- 10 | 6 | 0 | SEARCH TABLE test USING INDEX
sqlite_autoindex_test_1 (id=?)
-- 42 | 6 | 0 | USE TEMP B-TREE FOR RIGHT PART
OF ORDER BY
-- 58 | 2 | 0 | SCAN SUBQUERY 3
-- 95 | 0 | 0 | SCAN SUBQUERY 1
-- New proposed Query
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
FROM test
)
SELECT id, value
FROM AV AS AV1
WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV
AS AV2 WHERE AV2.id = AV1.id)
;
-- id | value
-- ------------ | ------------
-- 1 | 13
-- 2 | 21
EXPLAIN QUERY PLAN
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
FROM test
)
SELECT id, value
FROM AV AS AV1
WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV
AS AV2 WHERE AV2.id = AV1.id)
;
-- id | parent | notused | detail
-- ------------ | ------ | ------- |
------------------------------------------------------------
-- 3 | 0 | 0 | SEARCH TABLE test USING INDEX
sqlite_autoindex_test_1 (id=?)
-- 21 | 0 | 0 | CORRELATED SCALAR SUBQUERY
-- 27 | 21 | 0 | SEARCH TABLE test USING INDEX
sqlite_autoindex_test_1 (id=?)
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.078s
-- Total Script Query Time: 0d 00h 00m and
00.016s
-- Total Database Rows Changed: 5
-- Total Virtual-Machine Steps: 600
-- Last executed Item Index: 7
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2019-02-12 13:48:56.260 | [Success] Script Success.
-- 2019-02-12 13:48:56.260 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2019-02-12 13:48:56.166] APPLICATION : Script
E:\Documents\SQLiteAutoScript.sql started at 13:48:56.166 on 12 February.
--
================================================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
