mdewstowe99
asked on
How to create an aggregage table based on month/year
I have 20 million records, each has a date. About 50,000 exist on each day.
I want to be able to create an aggregate table based on the date. It needs to be aggregated into month/year.
As I am not hugely famialiar with SQL yet, it would be great to understand if this can be done in the query designer. If not, suggestions on the SQL are most welcome.
I want to be able to create an aggregate table based on the date. It needs to be aggregated into month/year.
As I am not hugely famialiar with SQL yet, it would be great to understand if this can be done in the query designer. If not, suggestions on the SQL are most welcome.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check it out: http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/vista/vista20.htm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
20 million rows isn't necessarily that large...
what sort of groupings do you want to apply?
(apart from year month)
e.g product, agent, source, geographic location...
by all means add year and month columns to the data if that is going to be your prime level of reporting
but a standard index on the "date" column will probably suffice..
you may find it's as cheap to just create a view on the table to display and query the data, rather than
actually creating a physical summary table, particularly if you then have to worry about creating routines to maintain it....
what sort of groupings do you want to apply?
(apart from year month)
e.g product, agent, source, geographic location...
by all means add year and month columns to the data if that is going to be your prime level of reporting
but a standard index on the "date" column will probably suffice..
you may find it's as cheap to just create a view on the table to display and query the data, rather than
actually creating a physical summary table, particularly if you then have to worry about creating routines to maintain it....
additionally
creating a "calendar" dimension table which associates dates with all your financial/reporting requirements can be a good investment... that way you have a standard definition for first quarter, financial year, tax year, you can also indicate public holidays etc to assess the impact of those on sales etc....
from a warehouse/analysis/reporti
fact table so that when reporting unnecessary joining of data is minimised....
hth
creating a "calendar" dimension table which associates dates with all your financial/reporting requirements can be a good investment... that way you have a standard definition for first quarter, financial year, tax year, you can also indicate public holidays etc to assess the impact of those on sales etc....
from a warehouse/analysis/reporti
fact table so that when reporting unnecessary joining of data is minimised....
hth
ASKER
Great. Can this be done in the query designer or do I need to write the SQL. As you might sense I am new to SQL.
ASKER
Great. Can this be done in the query designer or do I need to write the SQL. As you might sense I am new to SQL.
ASKER
I have a database of jobs advertised on the internet.
Each has a date found, category, source, region and some other fields. I definitely want to roll up by year and then month. Does it matter then in what order I group the other fields? Does it matter if I group by source and then category or category and source?
Or is it more important to ensure the indices are right.
Each has a date found, category, source, region and some other fields. I definitely want to roll up by year and then month. Does it matter then in what order I group the other fields? Does it matter if I group by source and then category or category and source?
Or is it more important to ensure the indices are right.
ASKER
How can I aggergate by the first day of the month, so counts are based on 1st Jan 2010, 1st Feb 2010 etc.
I need to have one continuous aggregation on date rather than seperating into years and months.
I need to have one continuous aggregation on date rather than seperating into years and months.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lovely.
ASKER
If, once a table is created, and then truncated and insert is used, how can one update the structure of the table without recreating it? Say the table is formed from a view. And I add a new field to the view. Do I need to create a new table or can I also insert this new field? This way existing indices are maintained.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again my thanks!!
Easy when you know how n
Easy when you know how n
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.