SQLize Online / PHPize Online  /  SQLtest Online

Ïðè ïëàíèðîâàíèè íîâûõ ìàðøðóòîâ è îöåíêå ýêîíîìè÷åñêîé ýôôåêòèâíîñòè óæå ñóùåñòâóþùèõ ìîæåò ïîòðåáîâàòüñÿ èíôîðìàöèÿ î òîì, êàêîâà óñðåäíåí- íàÿ ñòåïåíü çàïîëíåíèÿ ñàìîëåòîâ íà âñåõ íàïðàâëåíèÿõ. Áóäåì ó÷èòûâàòü òîëüêî óæå ïðèáûâøèå ðåéñû. WITH tickets_seats AS ( SELECT f.flight_id, f.flight_no, f.departure_city, f.arrival_city, f.aircraft_code, count( tf.ticket_no ) AS fact_passengers, ( SELECT count( s.seat_no ) FROM seats s WHERE s.aircraft_code = f.aircraft_code ) AS total_seats FROM flights_v f JOIN ticket_flights tf ON f.flight_id = tf.flight_id WHERE f.status = 'Arrived' GROUP BY 1, 2, 3, 4, 5 ) SELECT ts.departure_city, ts.arrival_city, sum( ts.fact_passengers ) AS sum_pass, sum( ts.total_seats ) AS sum_seats, round( sum( ts.fact_passengers )::numeric / sum( ts.total_seats )::numeric, 2 ) AS frac FROM tickets_seats ts GROUP BY ts.departure_city, ts.arrival_city ORDER BY ts.departure_city; departure_city | arrival_city | sum_pass | sum_seats | frac ----------------+-----------------+----------+-----------+------ Àáàêàí | Tomsk | 258 | 360 | 0.72 Àáàêàí | Novosibirsk | 217 | 348 | 0.62 Àáàêàí | Moscow | 466 | 1044 | 0.45 ... ßêóòñê | Ñàíêò-Ïåòåðáóðã | 352 | 3596 | 0.10 (361 ñòðîêà) Äëÿ òîãî ÷òîáû ëó÷øå óÿñíèòü, êàê ðàáîòàåò çàïðîñ â öåëîì, âû÷ëåíèòå èç íåãî îòäåëüíûå ïîäçàïðîñû è âûïîëíèòå èõ, ïîñìîòðèòå, ÷òî îíè âûâîäÿò. 14.  ðàçäåëå äîêóìåíòàöèè 9.17 «Óñëîâíûå âûðàæåíèÿ» ïðåäñòàâëåíû óñëîâ- íûå âûðàæåíèÿ, êîòîðûå ïîääåðæèâàþòñÿ â PostgreSQL.  òåêñòå ãëàâû áû- ëà ðàññìîòðåíà êîíñòðóêöèÿ CASE. Ñàìîñòîÿòåëüíî îçíàêîìüòåñü ñ ôóíêöèÿìè COALESCE, NULLIF, GREATEST è LEAST. Êàê âû ñ÷èòàåòå, ðàâíîñèëüíî ëè â äàííîì çàïðîñå SELECT count( s.seat_no ) è SELECT count( s.* ) Ïî÷åìó? Çàäàíèå. Ìîäèôèöèðóéòå ýòîò çàïðîñ, ÷òîáû îí âûâîäèë òå æå îò÷åòíûå äàí- íûå, íî ñ ó÷åòîì êëàññîâ îáñëóæèâàíèÿ, ò. å. Business, Comfort è Economy.