Geek asks and answers

Are inline queries bad ideas?

I have a table containing runtimes for generators on different sites, and I want to select the most recent entry for each site. Each generator starts once or twice a week.

I have a request that will do this, but I wonder if it is the best. I cannot help but think that using WHERE x IN (SELECT ...) is lazy and not the best way to formulate a query - any query.

The table is as follows:

CREATE TABLE generator_logs (
    id integer NOT NULL,
    site_id character varying(4) NOT NULL,
    start timestamp without time zone NOT NULL,
    "end" timestamp without time zone NOT NULL,
    duration integer NOT NULL
);

And request:

SELECT id, site_id, start, "end", duration 
FROM generator_logs
WHERE start IN (SELECT MAX(start) AS start 
                FROM generator_logs 
                GROUP BY site_id) 
ORDER BY start DESC

There is not a lot of data, so I'm not worried about query optimization. However, I have to do similar things on tables with 10 million rows (large tables, as far as I know!), And optimization is more important.

So, is there a better query for this, and inline queries are a bad idea?

+3
5

, , "IN":

select gl.id, gl.site_id, gl.start, gl."end", gl.duration 
from 
    generator_logs gl
    inner join (
        select max(start) as start, site_id
        from generator_logs 
        group by site_id
    ) gl2
        on gl.site_id = gl2.site_id
        and gl.start = gl2.start

,

+1

? :.

SELECT id, site_id, start, "end", duration 
FROM generator_logs g1
WHERE start = (SELECT MAX(g2.start) AS start 
               FROM generator_logs  g2
               WHERE g2.site_id = g1.site_id) 
ORDER BY start DESC

, .

, :

SELECT id, site_id, start, "end", duration 
FROM generator_logs g1
WHERE (site_id, start) IN (SELECT site_id, MAX(g2.start) AS start 
                           FROM generator_logs  g2
                           GROUP BY site_id)
ORDER BY start DESC
+4

MYSQL , Last Checked (..: )

Genetic Query, , .

MYSQL , - , , , , .

, , , .

$maxids = 'SELECT MAX(start) AS start FROM generator_logs GROUP BY site_id';
$q ="     
    SELECT id, site_id, start, \"end\", duration 
       FROM generator_logs
       WHERE start IN ($maxids) 
       ORDER BY start DESC
";
0

- MAX, - . , Postgres , DISTINCT ON:

SELECT DISTINCT ON (site_id)
  id, site_id, start, "end", duration
FROM generator_logs
ORDER BY site_id, start DESC;

DISTINCT ON (site_id) " site_id". order by , . , , - start, , .

0

The way to search for records that have a MAX value for each group is to select those records for which there is no record in the same group with a higher value:

SELECT id, site_id, "start", "end", duration 
FROM generator_logs g1
WHERE NOT EXISTS (
    SELECT 1
    FROM generator_logs g2
    WHERE g2.site_id = g1.site_id
    AND g2."start" > g1."start"
    );
0
source

More articles:


All Articles