Re: [PHP-DB] Re: Date operations.
Frank,
As yet no response from Garry, but I wanted to ask:
You already have DateTable set up (presumably), so extending its application into a
relationship with the
breeding table is no big deal. Given that one suggestion was that Garry could consider
storing monthly
statistics in another table (from breeding), and thus saves processing each time/only
needs to update once per
month and presumably only for the month just passed; wouldn't it make sense FOR HIM to
combine the two
functions? Initially create the MonthlyStats table to contain only year and month data
(which can be related to
'drive' date-oriented SELECTing), and then update/populate each month's row as and
when the stats become
available. It might also offer opportunities to simplify the processing outlined below.
Your thoughts?
=dn
- Original Message -
From: Frank Flynn [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 30 January 2002 17:05
Subject: [PHP-DB] Re: Date operations.
Gary,
You have a right to be confused - this problem is short but it is about as
complex as they get. Still I have a solution for you, also short and you'll
(hopefully) find it elegant. I found it interesting...
The thing is we will create a Cartesian product on purpose - this is
something they tell people learning SQL to avoid (and usually they are not
helpful) but here it is the only to get a daily average since you don't have
daily records of your pairs just a start and stop date.
I was looking at the problem from the child point of view in my first post
(easier since children only have a single birthday). I'll use your problem
now.
We will keep the DateTable and use your 'breeding' table but I will change
the 'paired' and 'split' columns to int (you don't need to do this but it
will make thing easier - some DBMS's will always put a time stamp in a Date
field and this does screw thing up - it makes it harder to join on them).
So we have:
breeding
---
rec SMALLINT PRIMARY KEY
sire SMALLINT
dam SMALLINT
pairedINT
split INT
num_offspring INT
And
DateTable
dayID INT PRIMARY KEY
myDate DATE
month INT
yearINT
quarter INT
/* and as discussed any other date related columns */
I need to count how many pairs were breeding
on a given day, and then average over the month.
OK - fasten you seat belt :-0
SELECT count(*), myDate
FROM breeding, DateTable
WHERE paired = dayID
AND split = dayID
AND month = 2
AND year = 2001
GROUP BY myDay
ORDER BY myDay
/* you could have just as easily used a start and stop date */
/* myDay = 2/1/2001 and myDate 3/1/2001 */
This will give you output something like this (the total per day):
5 Feb 1, 2001
5 Feb 2, 2001
5 Feb 3, 2001
5 Feb 4, 2001
5 Feb 5, 2001
6 Feb 6, 2001
6 Feb 7, 2001
6 Feb 8, 2001
6 Feb 9, 2001
6 Feb 10, 2001
6 Feb 11, 2001
... And so on ...
To get the average per month I'd use a temporary table (a table you create
just for this report or that is always there but empty except during this
report)
/* start with an empty table */
CREATE TABLE TotalByDay
( total INT,
dayID INT)
/* --- or --- */
TRUNCATE TABLE TotalByDay
/* now fill it... */
/* notice it the same SQL but I've gone back to */
/* dayID (the INT) not the full date and I've done */
/* the whole year so we can average per month */
INSERT INTO TotalByDay
SELECT count(*), dayID
FROM breeding, DateTable
WHERE paired = dayID
AND split = dayID
AND year = 2001
GROUP BY dayID
ORDER BY dayID
/* OK almost there ... Now to average by month */
SELECT ave(total), month, year
FROM TotalByDay, DateTable
WHERE TotalByDay.dayID = DateTable.dayID
GROUP BY month
ORDER BY month
Now you've got output like
5 1 2001
6 2 2001
6 3 2001
7 4 2001
12 5 2001
11 6 2001
11 7 2001
7 8 2001
7 9 2001
9 10 2001
9 11 2001
9 12 2001
From here you convert the month INT back into a name.
Feel free to writ back with any questions (it's headdy stuff) also I don't
have a mySQL db handy to check the syntax against - hopefully I've
remembered everything correctly. The concept is sound, we do this all the
time.
The got ya's are the DateTable must be full (don't fill it to the end of
this year and forget about it - next year your reports will be wrong)
Frank
Ouch, I'm getting a bit lost. I need to count how many pairs were breeding
on a given day, and then average over the month. Playing with mysql I get
for say the 1st day of the year:
select count(*) from breeding
where TO_DAYS(paired) = TO_DAYS('2002-1-1')
AND TO_DAYS('2002-1-1') = TO_DAYS(split);
That will give me the number of pairs breeding that day. Then I would have
to loop through the year and keep track of number of pairs, days of months
etc to get the monthly
[PHP-DB] Re: Date operations.
The traditional Data Warehouse way to solve this is to have a second table of dates. The table might look like: DateTable --- dayID int, myDate datetime And you add all the particular columns that you might ever want to search on, like: month int, year int, dayOfYear, int quarter int And then your table(s) only include the dayID (the integer - perhaps the date too but it's not necessary, all your searching and grouping is done by the ID). PHP offers several data functions which may or may not meet your needs but the important thing to remember is that next year the dayID must not go back to 1 - rather tomorrow will always be dayID++. So if your table looked like: Dog - name varchar(20), mom varchar(20), dad varchar(20), birthDayID int, sex char You could do a query like: Select mom, count(*) from Dog where birthDayID in (select dayID from DateTable where year = 2000) Group by mom Which would show you all the mothers who had given birth in 2000 and the number of children for each. Now notice how easy it is to change the where year = 2000 to where quarter = 2 and year = 2001 and so on... I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. Select month, count(*) from Dog, DateTable where birthDayID = myDate and myDate = 1/1/2000 /*start date */ and myDate 6/1/2000 /* end date */ Group by month -- this is the total per month for the first 5 months of 2000, you can figure the average from here. Change the month to quarter, dayOfYear or year or even myDate for daily totals and you're off. This set up works best where the number of records per day is high - you only need one int field in your data table (sometimes called 'fact table') and you can be as anal as you like with your DateTable - even go back and add more attributes later without messing with your fact table. The advantage is you do the work on the DateTable and then it's a simple join to your fact table. Good Luck, Frank [EMAIL PROTECTED] On 1/29/02 9:21 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From: Garry Optland [EMAIL PROTECTED] Date: Tue, 29 Jan 2002 19:10:25 +1100 (EST) To: [EMAIL PROTECTED] Subject: Date operations. Hi All, I have a problem working out a suitable algorithm either in PHP or MySQL. Basically I have a DB that keeps track of breeding records. Each record has a paired data, and a split-up date. I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. All the algorithms I can think of are messy, where I have to loop through all the breeding records for every day of the year, and count how many pairs are breeding by seeing if the date is between the start and stop dates, and then average that on a monthly basis. I can't see that scaling very well, as there might be several hundred breeding records for a given year, multiplied by 365 days. Has anyone any hints/pointers for an efficient way to do this? Regards, Garry. -- Frank Flynn Poet, Artist Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
