StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>how much time does it take that vehicle to go about 5000 miles</p> </blockquote> <p>Well I guess that's the main problem. </p> <p>Where does the "5000 miles" come from ? Is it your recommended interval between oil changes ? Perhaps you'd like to schedule other maintenance operations which occur at different intervals (tires, whatever). So you'd need a way to encode those intervals. </p> <p>I'll make a little example :</p> <pre><code>CREATE TABLE service_types ( st_id SERIAL PRIMARY KEY, st_name TEXT, st_interval_miles INTEGER, st_interval_time INTERVAL ); CREATE TABLE vehicles ( veh_id SERIAL PRIMARY KEY, birth_date DATE ); CREATE TABLE service_sales ( ss_id SERIAL PRIMARY KEY, ss_mileage INTEGER NOT NULL, ss_date DATE NOT NULL, veh_id INTEGER NOT NULL REFERENCES vehicles( veh_id ) ); CREATE TABLE service_details( ss_id INTEGER NOT NULL REFERENCES service_sales( ss_id ), st_id INTEGER NOT NULL REFERENCES service_types( st_id ) ); </code></pre> <p>Let's fill this :</p> <pre><code>INSERT INTO service_types (st_name,st_interval_miles,st_interval_time) VALUES ('oil change', 5000, '2 YEAR'), ('timing belt', 60000, '5 YEAR'); INSERT INTO vehicles (veh_id,birth_date) VALUES (1,'2009-09-01'),(2,'2009-08-01'); INSERT INTO service_sales (ss_id, veh_id, ss_mileage, ss_date) VALUES (1, 1, 5000, '2010-01-01'), (2, 1, 12000, '2010-04-01'), (3, 1, 18000, '2010-09-01'), (4, 2, 5000, '2010-01-01'), (5, 2, 10000, '2010-08-01'), (6, 2, 16000, '2010-12-01'); INSERT INTO service_details (ss_id,st_id) VALUES (1,1), (2,1), (3,1), (4,1), (5,1), (6,1); SELECT * FROM vehicles v JOIN service_sales USING (veh_id) JOIN service_details USING (ss_id) JOIN service_types USING (st_id); st_id | ss_id | veh_id | birth_date | ss_mileage | ss_date | st_name | st_interval_miles | st_interval_time -------+-------+--------+------------+------------+------------+------------+-------------------+------------------ 1 | 1 | 1 | 2009-09-01 | 5000 | 2010-01-01 | oil change | 5000 | 2 years 1 | 2 | 1 | 2009-09-01 | 12000 | 2010-04-01 | oil change | 5000 | 2 years 1 | 3 | 1 | 2009-09-01 | 18000 | 2010-09-01 | oil change | 5000 | 2 years 1 | 4 | 2 | 2009-08-01 | 5000 | 2010-01-01 | oil change | 5000 | 2 years 1 | 5 | 2 | 2009-08-01 | 10000 | 2010-08-01 | oil change | 5000 | 2 years 1 | 6 | 2 | 2009-08-01 | 16000 | 2010-12-01 | oil change | 5000 | 2 years </code></pre> <p>Now for your estimation : You need to compute the "average speed" of the vehicle, that is the distance it did, divided by time, but you need to choose the interval. You could use the interval between the last 2 services, or an average over all the times your shop saw this vehicle. That's up to you. </p> <p>Once you have this, you can compute how much time it will take that vehicle to do the 5000 miles since the last recorded oil change.</p> <p>Let's say you gonna estimate the average speed over all the times your shop saw this vehicle. </p> <p>This means for each vehicle you take the last and first service records and compute the mileage and time differences :</p> <pre><code>SELECT foo.veh_id, (last.ss_mileage-first.ss_mileage) AS miles, (last.ss_date-first.ss_date) AS days FROM ( SELECT veh_id, min( ss_id ) AS first_ss_id, max( ss_id ) AS last_ss_id FROM service_sales GROUP BY veh_id ) foo JOIN service_sales first ON (first.ss_id=first_ss_id) JOIN service_sales last ON (last.ss_id=last_ss_id) WHERE first_ss_id != last_ss_id AND last.ss_date <= first.ss_date + '1 WEEK'::INTERVAL; veh_id | miles | days --------+-------+------ 1 | 13000 | 243 2 | 11000 | 334 </code></pre> <p>So we know how many miles each vehicle did in how many days. The WHERE condition eliminates vehicles which have only one service record or that your shop only knows from less than 1 week which would not give a very good estimation. You could use a LEFT JOIN and some COALESCE work to substitute this with a fudged default value.</p> <p>Now from the last oil change and average speed we can get a value for the next oil change :</p> <pre><code>WITH avgspeeds AS (SELECT foo.veh_id, (last.ss_mileage-first.ss_mileage) AS miles, (last.ss_date-first.ss_date) AS days FROM ( SELECT veh_id, min( ss_id ) AS first_ss_id, max( ss_id ) AS last_ss_id FROM service_sales GROUP BY veh_id ) foo JOIN service_sales first ON (first.ss_id=first_ss_id) JOIN service_sales last ON (last.ss_id=last_ss_id) WHERE first_ss_id != last_ss_id AND last.ss_date >= first.ss_date + '1 WEEK'::INTERVAL ), last_services AS (SELECT veh_id, st_id, max(ss_date) AS last_service_date FROM service_sales JOIN service_details USING (ss_id) GROUP BY veh_id, st_id) SELECT *, COALESCE( last_service_date, birth_date ) + least( (st.st_interval_miles * a.days / a.miles) * '1 DAY'::INTERVAL, st.st_interval_time) AS next_service_date FROM service_types st CROSS JOIN vehicles v JOIN avgspeeds a USING (veh_id) LEFT JOIN last_services ls USING (veh_id,st_id); </code></pre> <p>Monster query eh ? Seems to give the right results.</p> <p>Good luck ;)</p> <blockquote> <p>Why do you put unnecessary prefixes before each column name? I really hate it when people do this, it makes every query so damn ugly, </p> </blockquote> <p>Because : - It makes queries readable - I hate to have several "id" fields in the result sets coming from whatever tables - Foreign keys should have the same names in both tables, allowing the use of NATURAL JOIN and the very useful JOIN .. USING - I don't care that you don't like it</p> <blockquote> <p>and it's even worse if you use an orm. Column aliasing is already in the sql standard, use it</p> </blockquote> <p>If your ORM doesn't allow you to define table column names that are different from object member names, and write large (yet readabe) SQL queries, you need a real ORM.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload