Gerald Taylor [EMAIL PROTECTED] wrote on 10/14/2005 10:42:33 AM:
Thank you for your answer. What I'm trying to do is return the scores of
the diff values of datapoints between a particular user and all the
other users in the system. (hence the mypmd.uid != pmd.uid) Some
datapoints might be missing. I also want to know how many data point
diffs we got for each user. I no longer need the avg.
The tables are pretty straightforward
CREATE TABLE pmd (
id bigint unsigned not null auto_increment primary key,
uid bigint unsigned not null,
pmid bigint unsigned not null,
value int not null
) engine InnoDB DEFAULT CHARSET Latin1
and mnames is just the names of the datapoints being measured
CREATE TABLE mnames (
pmid bigint unsigned not null auto_increment primary key,
mname VARCHAR(20)
) engine InnoDB DEFAULT CHARSET Latin1
If an outer join would do it, I will like to learn something.
Thnka you again.
[EMAIL PROTECTED] wrote:
Gerald Taylor [EMAIL PROTECTED] wrote on 10/13/2005 12:20:53 PM:
SELECT avg(abs(pmd.value) - (mypmd.value))) as diff, pm.mname,
pmd.uid
FROM pmdata pmd, mnames pm, pmdata mypmd
WHERE mypmd.uid= ?
AND pmd.pmid=pm.pmid AND mypmd.pmid=pm.pmid
AND pmd.uid != mypmd.uid GROUP BY pmd.pmid, pmd.uid ORDER BY
pmd.uid
What I would like also to return in this query is a COUNT of the
number
of pmd.uid of each different value so I know how many values I got
from
uid#1, uid#2 etc.
I tried putting COUNT(pmd.uid) as numdelta but it just gave me a 1
in
every row. The problem is I am not doing a straight GROUP BY
pmd.uid
Can you explain, in simple language, the question you are trying to
answer with this query? I see an INNER JOIN and a != used together
which makes me wonder if you needed to use one of the OUTER JOINs
instead. Along with your explanation, please respond with the results
of
SHOW CREATE TABLE pmdata\G
SHOW CREATE TABLE mnames\G
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
If you know how to make an explicit INNER JOIN, making either one of the
OUTER JOINs becomes easy. I try to only use the explicit form of creating
JOINs and I avoid the implicit form (the comma separated list that you
used) like the plague. In your case, the INNER JOIN is appropriate because
if there is only one user, there can be no differences and you shouldn't
expect any results.
If you want just a table of differences, that does not require a GROUP BY:
SELECT me.uid as me
, them.uid as them
, n.mname as name
, (me.value - them.value) as diff
FROM pmd me
INNER JOIN pmd them
ON me.uid != them.uid
AND me.pmid = them.pmid
INNER JOIN mnames n
ON me.pmid = n.pmid;
If you want some statistics on your differences, that's another story.
Assuming that each pmid value can appear more than once per uid (which may
not be true for your data, but this assumption generalizes the solution)
or that some pmids do not apply to some uids, this query should provide
for each pmid of each uid, the name of the pmid, the sums of the
differences, the sums of the absolute differences, the sum of the squares
of the differences, the average difference, the average absolute
difference, the standard deviation of the differences, and the count of
how many other uids and pmids were compared to for each pmid that belongs
to the user. In this case I would like to list ALL of the pmids for each
uid, regardless if any other uid has the same one, so I need to use an
OUTER JOIN ( the LEFT JOIN, in particular).
SELECT me.uid
, n.mname as name
, sum(me.value - them.value) as sum_diff
, sum(abs(me.value - them.value)) as sum_abs_diff
, sum((me.value - them.value)*(me.value - them.value)) as
sum_sq_diff
, avg(me.value - them.value) as avg_diff
, avg(abs(me.value - them.value)) as avg_abs_diff
, std(me.value - them.value) as std_diff
, count(distinct them.uid) as otheruids
, count(them.pmid) as datapoints
FROM pmd me
INNER JOIN mnames n
ON me.pmid = n.pmid;
LEFT JOIN pmd them
ON me.uid != them.uid
AND me.pmid = them.pmid
GROUP BY me.uid, n.mname;
Let me know if that does what you want. And don't forget to CC: the list
on all responses :-o
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
