Re: repeatet summary querys per month over 5 years
От | |
---|---|
Тема | Re: repeatet summary querys per month over 5 years |
Дата | |
Msg-id | 983001A7B5598D4D8E8D759F33B630FE0B6FF8C9@aai-exch-mbx2.campus.unibe.ch обсуждение исходный текст |
Ответ на | Re: repeatet summary querys per month over 5 years (Rory Campbell-Lange <rory@campbell-lange.net>) |
Список | pgsql-novice |
Thanks a lot for your answer, it looks promising. But I still have a problem. I'll try to give you more details in amore comprehensive way.
The sourcetable contains informations about every stay of each animal on a specific farm:
animalID animalbirthday animalsex farmID farmproductiontype beginn(Date) end(date)
to get the number of animals in different ageclasses per month, I have to calculate the age of the animals for every month (e.g. 2001-01-01 - birthday) and categorize the ageclasses and select the animals that are alive (i.e. stay on a farm) on the first for every month (begin <= 2001-01-01 and end >= 2001-01-01).
To do so the query I got looks like
SELECT
prductiontype,
animalsex,
CASE WHEN (2007-01-01)-animalbirhtday < 365 THEN '1' WHEN (2007-01-01)-animalbirhtday > 730 THEN '3' ELSE '2' END AS ageclass,
COUNT(animalID),
FROM sourcetable
WHERE begin <= 2007-01-01 and end >= 2007-01-01
GROUP BY farmproductiontype, animalsex, ageclass
ORDER BY farmproductiontype, animalsex, ageclass
wich produces me a table like
farmrpoductiontype animalsex ageclasse count
1 female 1 10000
1 male 1 10000
etc.... now I have to do this 60 times with reference date (here 2007-01-01) as the only thing changing. I'd like to automatize that and just ad each time a column to the table above
farmrpoductiontype animalsex ageclasse jan07 feb07 ....
1 female 1 10000 10003
1 male 1 10000 9000
In R I'd use a loop with for i=1:60 with begindat = 2011-07-01 and steps=month etc but I don't know if one can generate loops in postgresql with a variable in the query changing (I think not, right?)
I see the way I could use your solution assigning categories per month (e.g. CASE WHEN begin <= 2007-01-01 and end >= 2007-01-01 THEN Jan07 WHEN begin <= 2007-02-01 and end >= 2007-02-01 THEN feb07 etc) in the inner query but this would multiplie the table as the animals stay usualy longer than one month on a farm. As also the ageclasses change for every month I think it gets to complicatet...
In a book about SQL (Alan Beaulieu, Introduction to SQL in German) I found, that its possible to use subqueries to generate expressions like
SELECT
x
(SELECT y FROM tabley) y
FROM table x;
but in postgress I get the error statement that the subquery produces more than 1 row... (this would be the other obtion, create 60 subqueries to produce the final table in one step).
In the end I guess I loose less time doing a lot by hand than trying to understand the possibilities of SQL by learning by doing... but oviously if there is a solution I'm very happy to learn, especially as I have to generate other summaries over the 60 months, this is just the first one...
I hope I could explain better and again thanks a lot!
Sara
________________________________________
Von: Rory Campbell-Lange [rory@campbell-lange.net]
Gesendet: Donnerstag, 15. März 2012 19:17
An: Schaerrer, Sara (VETSUISSE)
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] repeatet summary querys per month over 5 years
On 15/03/12, sara.schaerrer@vetsuisse.unibe.ch (sara.schaerrer@vetsuisse.unibe.ch) wrote:
> Background: I have to summarize (count) the number of animals for the
> categories production type, ageclass and sex per month (1. of every
> month) over 5 years.
...
> Ideally I'd generate a table with a column for every month:
>
> nuar ak sex jan07 feb07 ... dez11
You may need a query with an outer and inner part. The inner part is a
query that provides you with the base data which is summarised in the
outer part.
I couldn't read your query but if something like this worked:
SELECT
animal
,sex
,tochar(dater,'YYDD') as month
FROM
sourcetable;
you can nest the query to get the sort of result you seem to need, as
follows:
SELECT
x.animal
,x.sex
,sum(case when x.month='012001' then 1 else 0 end) as '012001'
,sum(case when x.month='022001' then 1 else 0 end) as '022001'
,sum(case when x.month='032001' then 1 else 0 end) as '032001'
...
FROM (
SELECT
animal
,sex
,tochar(dater,'MMYY') as month
FROM
sourcetable;
) x
GROUP BY
x.animal
,x.sex;
--
Rory Campbell-Lange
rory@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
The sourcetable contains informations about every stay of each animal on a specific farm:
animalID animalbirthday animalsex farmID farmproductiontype beginn(Date) end(date)
to get the number of animals in different ageclasses per month, I have to calculate the age of the animals for every month (e.g. 2001-01-01 - birthday) and categorize the ageclasses and select the animals that are alive (i.e. stay on a farm) on the first for every month (begin <= 2001-01-01 and end >= 2001-01-01).
To do so the query I got looks like
SELECT
prductiontype,
animalsex,
CASE WHEN (2007-01-01)-animalbirhtday < 365 THEN '1' WHEN (2007-01-01)-animalbirhtday > 730 THEN '3' ELSE '2' END AS ageclass,
COUNT(animalID),
FROM sourcetable
WHERE begin <= 2007-01-01 and end >= 2007-01-01
GROUP BY farmproductiontype, animalsex, ageclass
ORDER BY farmproductiontype, animalsex, ageclass
wich produces me a table like
farmrpoductiontype animalsex ageclasse count
1 female 1 10000
1 male 1 10000
etc.... now I have to do this 60 times with reference date (here 2007-01-01) as the only thing changing. I'd like to automatize that and just ad each time a column to the table above
farmrpoductiontype animalsex ageclasse jan07 feb07 ....
1 female 1 10000 10003
1 male 1 10000 9000
In R I'd use a loop with for i=1:60 with begindat = 2011-07-01 and steps=month etc but I don't know if one can generate loops in postgresql with a variable in the query changing (I think not, right?)
I see the way I could use your solution assigning categories per month (e.g. CASE WHEN begin <= 2007-01-01 and end >= 2007-01-01 THEN Jan07 WHEN begin <= 2007-02-01 and end >= 2007-02-01 THEN feb07 etc) in the inner query but this would multiplie the table as the animals stay usualy longer than one month on a farm. As also the ageclasses change for every month I think it gets to complicatet...
In a book about SQL (Alan Beaulieu, Introduction to SQL in German) I found, that its possible to use subqueries to generate expressions like
SELECT
x
(SELECT y FROM tabley) y
FROM table x;
but in postgress I get the error statement that the subquery produces more than 1 row... (this would be the other obtion, create 60 subqueries to produce the final table in one step).
In the end I guess I loose less time doing a lot by hand than trying to understand the possibilities of SQL by learning by doing... but oviously if there is a solution I'm very happy to learn, especially as I have to generate other summaries over the 60 months, this is just the first one...
I hope I could explain better and again thanks a lot!
Sara
________________________________________
Von: Rory Campbell-Lange [rory@campbell-lange.net]
Gesendet: Donnerstag, 15. März 2012 19:17
An: Schaerrer, Sara (VETSUISSE)
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] repeatet summary querys per month over 5 years
On 15/03/12, sara.schaerrer@vetsuisse.unibe.ch (sara.schaerrer@vetsuisse.unibe.ch) wrote:
> Background: I have to summarize (count) the number of animals for the
> categories production type, ageclass and sex per month (1. of every
> month) over 5 years.
...
> Ideally I'd generate a table with a column for every month:
>
> nuar ak sex jan07 feb07 ... dez11
You may need a query with an outer and inner part. The inner part is a
query that provides you with the base data which is summarised in the
outer part.
I couldn't read your query but if something like this worked:
SELECT
animal
,sex
,tochar(dater,'YYDD') as month
FROM
sourcetable;
you can nest the query to get the sort of result you seem to need, as
follows:
SELECT
x.animal
,x.sex
,sum(case when x.month='012001' then 1 else 0 end) as '012001'
,sum(case when x.month='022001' then 1 else 0 end) as '022001'
,sum(case when x.month='032001' then 1 else 0 end) as '032001'
...
FROM (
SELECT
animal
,sex
,tochar(dater,'MMYY') as month
FROM
sourcetable;
) x
GROUP BY
x.animal
,x.sex;
--
Rory Campbell-Lange
rory@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
В списке pgsql-novice по дате отправления: