Select zeit wo (werte1 + Werte 2 - werte3) am größten ist?

S

SolarGuido

Neuer Benutzer
Beiträge
2
Guten Tag,

ich habe eine tabelle die so ausieht:

nr time wert1 wert2 wert3
20217 1 1503825706 221 34 408
20215 1 1503825686 121 4 307
20213 1 1503825665 220 67 506
20211 1 1503825645 220 89 206

nun möchte ich die zeit wo (wert1+wert2-wert3) am gößten ist.

ich finde einfach nichts passendes, dachte es ist einfach :

select time where MAX(werte1+wert2-wert3)

aber das geht nicht...
Kann mir jemand helfen?
 
Werbung:
schon raus gefunden durch probieren einfacher als ich dachte...


SELECT time, (`wert1`+`wert2`-`wert3`) FROM `table` WHERE id=1
ORDER BY (`wert1`+`wert2`-`wert3`) DESC LIMIT 1
 
Deine Zahlen haben 6 Spalten, die Tabellendefinition nur 5.

Code: 
test=*# select * from solarguide ;
  nr  | wert1 | wert2 | wert3
-------+-------+-------+-------
 20217 |  221 |  34 |  408
 20215 |  121 |  4 |  307
 20213 |  220 |  67 |  506
 20211 |  220 |  89 |  206
(4 Zeilen)

test=*# select * from solarguide order by (wert1+wert2+wert3) desc limit 1;
  nr  | wert1 | wert2 | wert3
-------+-------+-------+-------
 20213 |  220 |  67 |  506
(1 Zeile)
 
HAVING wäre schöner als ORDER BY und LIMIT
Code: 
SELECT [time] FROM tabelle WHERE id=1 HAVING (wert1 + wert2 + wert3) = max(wert1 + wert2 + wert3)
 
@ukulele : warum?

Testtabelle:

Code: 
test=*# create table solarguide(id int primary key, val1 numeric, val2 numeric, val3 numeric);
CREATE TABLE
test=*# insert into solarguide select s, random(), random(), random() from generate_series(1, 1000000) s;
INSERT 0 1000000

Suche ohne Index:

Code: 
test=*# explain analyse select * from solarguide order by (val1+val2+val3) desc limit 1;
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=28334.00..28334.00 rows=1 width=37) (actual time=831.121..831.121 rows=1 loops=1)
  ->  Sort  (cost=28334.00..30834.00 rows=1000000 width=37) (actual time=831.121..831.121 rows=1 loops=1)
  Sort Key: (((val1 + val2) + val3)) DESC
  Sort Method: top-N heapsort  Memory: 25kB
  ->  Seq Scan on solarguide  (cost=0.00..23334.00 rows=1000000 width=37) (actual time=0.012..525.751 rows=1000000 loops=1)
 Planning time: 0.150 ms
 Execution time: 831.152 ms
(7 Zeilen)

test=*# explain analyse select id from solarguide group by id having(val1+val2+val3) = max(val1+val2+val3);
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..61817.43 rows=1000000 width=37) (actual time=0.049..1685.694 rows=1000000 loops=1)
  Group Key: id
  Filter: (((val1 + val2) + val3) = max(((val1 + val2) + val3)))
  ->  Index Scan using solarguide_pkey on solarguide  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.026..347.161 rows=1000000 loops=1)
 Planning time: 0.114 ms
 Execution time: 1762.401 ms
(6 Zeilen)

Index:

Code: 
test=*# create index ind_solarguide on solarguide ((val1+val2+val3)) ;
CREATE INDEX

Suche mit Index:

Code: 
test=*# explain analyse select * from solarguide order by (val1+val2+val3) desc limit 1;
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.49 rows=1 width=37) (actual time=0.041..0.041 rows=1 loops=1)
  ->  Index Scan Backward using ind_solarguide on solarguide  (cost=0.42..68748.43 rows=1000000 width=37) (actual time=0.040..0.040 rows=1 loops=1)
 Planning time: 0.170 ms
 Execution time: 0.062 ms
(4 Zeilen)

test=*# explain analyse select id from solarguide group by id having(val1+val2+val3) = max(val1+val2+val3);
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..61817.43 rows=1000000 width=37) (actual time=0.046..1667.399 rows=1000000 loops=1)
  Group Key: id
  Filter: (((val1 + val2) + val3) = max(((val1 + val2) + val3)))
  ->  Index Scan using solarguide_pkey on solarguide  (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.021..348.088 rows=1000000 loops=1)
 Planning time: 0.125 ms
 Execution time: 1744.888 ms
(6 Zeilen)
 
Werbung:
Zurück
Oben