MySQL divide two series by eachother

I have several data series in a MySQL table.

The fields are Series, Date, Value. 开发者_StackOverflow中文版 Is there a way for me to select, for two distinct Series, all the dates for which the series overlap and then for each date, the quotient of the value from series one and series two.

Example:

Series Date Value

Dogs 2004 10

Dogs 2005 12

Dogs 2006 14

Dogs 2007 16

Cats 2004 6

Cats 2005 6

Cats 2006 7

Cats 2007 4

I would like something like SELECT Cats/Dogs and get the following result

Date Value

2004 .6

2005 .5

2006 .5

2007 .25

You can achieve this with a self-join on your table.

SELECT cats.Series, cats.value / dogs.value 
FROM animals cats
INNER JOIN animals dogs 
        ON dogs.Date = cats.Date
WHERE cats.Series = "Cats"
AND dogs.Series = "Dogs"

It would be preferable for performance to have an index on Date, and a key on (Series,Date) would be ideal.