Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.mysql > #5513

Re: Calculating Price Increases

Show all headers | View raw

1911que@gmail.com:

>Hello

Hi 1911,

>
>Here is the scenario, I am trying to build a gas price spike alert
>application. I gather gas prices and store them in a database. I
>created a table and store information as follows: (although this is
>an SQLite snapshot version, I have a MySQL version)
>
>CREATE TABLE [PRICES] (
>  [url] VARCHAR,
>  [state] VARCHAR,
>  [city] VARCHAR,
>  [location] VARCHAR,
>  [price] VARCHAR,
>  [company] VARCHAR,
>  [address] VARCHAR,
>  [time] VARCHAR,
>  [timerecorded] VARCHAR,
>  [increasedby] VARCHAR);

>Is this a suggested way to store the information? What I want to do
>is be able to calculate when a price at a store increases by more
>than 10 cents. That is all.

Usually, a data model is normalized, before defining tables. In this
case, you would have GasStations (for which you keep track of their
company name, address and location (etc.)) and RecordedPrices (where
date and price are stored). These tables are related 1:n, so each
GasStation has multiple RecordPrice entries.


>
>CREATE TRIGGER insert_date AFTER  INSERT ON PRICES
>BEGIN
>
>UPDATE PRICES SET timerecorded = DATETIME('NOW', '-240 minutes')
>         WHERE rowid = new.rowid;
>END;

It's unclear why you want to do that. If a trigger is needed at all, I
would expect one to calculate the 'increasedby' field, which you don't
use in your next queries, though.

However, there seems no need for an increasedby field (unless you need
to de-normalize in order to get better performance, but that's probably
not an issue here). The increasedby value can be calculated upon
retrieval of the data.



> Below is what I have tried, but does not
>print the correct stores with increases.
>
>(BELOW IS WHAT I WANT TO CREATE A VIEW WITH)
>SELECT * FROM prices
>WHERE rowid in (
>    SELECT p.rowid from prices p
>    LEFT JOIN prices p1
>    ON (p1.rowid = (select max(rowid) from prices pp  where pp.rowid
>< p.rowid))      WHERE p.price > p1.price + .10 and p.address =
>p1.address ) 

As Doug mentioned, calculations and comparisons will fail due to the
fact that wrong field types are used.
Also, I think the "max(rowid) from prices pp" clause will fail, because
you don't limit the search range to the entries belonging to the same
address. You might get a wrong value for max(rowid).


-- 
Erick

Back to comp.databases.mysql | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Calculating Price Increases "1911que@gmail.com" <1911que@gmail.com> - 2014-06-28 16:51 -0700
  Re: Calculating Price Increases Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-29 00:03 +0000
  Re: Calculating Price Increases "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-29 12:49 +0000

csiph-web