On 26-Mar-2003 Black, Kelly W [PCS] wrote:
> Thanks again Serge and Don,
>
> Don not to sound like an idiot but what would
> you recommend on adding a unique key. I understand
> the idea, but didn't want to corrupt tables trying to
> get it right.
>
> Serge, I knew DISTINCT operated on whole lines, I was
> counting on that but as you can see, I didn't know about
> the sum *before* the distinction...
>
> Thanks guys, I will try these and see if I can fix it.
>
It's tough to say without the context.
So I'll make some assumptions and guesses.
Kelly, you get to figure out if they are valid for you.
Let's look at the query:
SELECT DISTINCT measurement, sum(val), pcfver, hour, release
FROM pcf WHERE release = curdate()-1 group by measurement, hour;
---
I'll assume 'release' is a DATE and 'hour' is a TINYINT UNSIGNED,
meaning the hour of 'release' day when the entry was recorded.
I'll further assume 'measurement' is a discrete measurement sensor.
Example: a thermocouple at the bottom of an exhaust stack is a
different measurement # than the TC at the top of the stack.
And 'pcfvar' is some nominal or ideal value (but it could be some
max/min acceptable).
The 'val' field is, of course, your independent y-variable.
---
We've already discussed/fixed the 'curdate()-1' error.
And since yure going to constrain the table to UNIQUE entries, let's
get rid of the DISTINCT function.
So, you're left with the X-axis (non-independent) variables:
'measurement', 'release', 'hour', & 'pcfvar'.
The 'measurement' is a no-brainer: the temp at the bottom of the chimney
has nothing to do with the top (in the database sense).
'release' ? another no-brainer. You're reporting by date.
So far the index is :
UNIQUE KEY mr (measurement, release);
'pcfvar' is an iffy. It might change by the date or hour.
Example: aircraft taking off before 7am must be quieter than 90dB,
but can hit 105 dB afterwards (yeah, I made up the numbers).
UNIQUE KEY mrp (measurement, release, pcfvar);
'hour' gets a bit more tricky.
If "pinkware" (humans) are recording the data, you've got to consider
the case of entries at :01 and then :59 in the same hour ...
And which is more "valid" ?
"INSERT IGNORE INTO ... or REPLACE INTO ..." ?
Or you could allow both readings and AVG() indstead of SUM() ...
Depending on what you'll allow, a full constraint would be:
UNIQUE KEY mrph (measurement, release, pcfvar, hour);
---
Kelly. You owe me two virtual beers.
Shiner Bock or Miller High-Life tall-boys will be fine.
Invoice to follow.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
