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

Groups > comp.databases.mysql > #5510 > unrolled thread

Calculating Price Increases

Back to article view | Back to comp.databases.mysql

Contents

  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

#5510 — Calculating Price Increases

Hello

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);

CREATE TRIGGER insert_date AFTER  INSERT ON PRICES
BEGIN

UPDATE PRICES SET timerecorded = DATETIME('NOW', '-240 minutes')
         WHERE rowid = new.rowid;
END;


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. 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 
) 

[toc] | [next] | [standalone]

#5511

"1911que@gmail.com" <1911que@gmail.com> wrote in
news:7a9f4841-b760-4adc-b3b0-114b901c2200@googlegroups.com: 

> Hello
> 
> 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, <<<<<<<<<<<<<<< this isn't going to work

Price is inherently numeric data, and if you want to do any
calculations with it, you need to store it as a numeric data type. 

>   [company] VARCHAR,
>   [address] VARCHAR,

The next three columns also should not be VARCHAR data. Time and
timerecorded are inherently times, and so should use a time data
type, probably DATETIME. 

IncreasedBy should probably be a numeric data type also.

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

What is the purpose of this trigger?
 
> 
> Is this a suggested way to store the information?

No; see above.

> 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. Below is what I have
> tried, but does not print the correct stores with increases. 

I can imagine that it won't. You can't add 0.10 to a VARCHAR field. You need to define price 
as a numeric data type.
 
> (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 
> ) 
> 

[toc] | [prev] | [next] | [standalone]

#5513

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.mysql

csiph-web