SQLite: How to create a combination of unrelated elements of two queries?

user_10863380 0
Viewed 29

I have one table that I need to get some metrics from.

For example I have the following table:

meas_count skippings links extra
10 8 4.2 some
10 9 5.8 some
10 9 5.8 some_2
11 8 4.2 some
11 8 5.8 some
11 9 5.9 some

I need to get a view of an existing table in the following form for further work:

meas_count skippings links_min links_max
10 8 0 4
10 8 4 5
10 8 5 6
10 9 0 4
10 9 4 5
10 9 5 6
11 8 0 4
11 8 4 5
11 8 5 6
11 9 0 4
11 9 4 5
11 9 5 6

At the moment I have 2 queries, the results of which I need to combine to get the result I need.

First request:

SELECT meas_count,skippings FROM current_stats GROUP BY meas_count,skippings

Creates the following:

meas_count skippings
10 8
10 9
11 8
11 9

Second request:

SELECT 
LAG(rounded) OVER (ORDER BY rounded) as links_min, 
rounded as links_max FROM 
(SELECT * FROM 
(SELECT ROUND(links, 1) as rounded FROM current_stats) 
GROUP BY rounded ORDER BY rounded)

Creates the following:

links_min links_max
NULL 4
4 5
5 6

I need something like result of sets multiplication...

What query should be executed to get the table of the view I need as a result?

I also have an additional question: is the execution of the second query slowed down due to several SELECTs inside?

combinations sqlite sql
1 Answers

You can do that by doing an INNER JOIN on the two tables without specifying a join condition. That will give you every combination of the two sets of rows.

SELECT * FROM 
(
    SELECT meas_count,skippings
    FROM current_stats
    GROUP BY meas_count,skippings)
AS one
INNER JOIN
(
    SELECT LAG(rounded) OVER (ORDER BY rounded) as links_min, 
 rounded as links_max FROM 
    (SELECT * FROM 
       (SELECT ROUND(links, 1) as rounded FROM current_stats) 
     GROUP BY rounded
     ORDER BY rounded  
    )
) AS two;

As for performance, that's really only an issue if there is a better way to do it. Of course nested SELECTs take time, but the query optimizers in today's SQL engine are pretty good at determining what you MEANT from what you SAID.

user_18833160
Related