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

For example I have the following table:

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

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:

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:

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?

CodePudding user response：

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.