Querying tables with different aggregation levels

#31

On 24 Sep, 00:39, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
[...]

Quote:

Maybe you post a bit more specific information about your data and
especially time resolution(s) that you need and the report frequency etc.

In addition it may help to reveal the dbms used. Many times it is
possible to optimize the physical storage of the data (range
partitioning, materialized query tables, multidimensional clustering
etc).

/Lennart

#32

On Sep 23, 5:58*pm, Mark Wilden <m... (AT) mwilden (DOT) com> wrote:

Quote:

I appreciate each and every one of your responses, which have given me
some interesting food for thought.

However, I was still wondering about my original questions, which were

1) Has anyone heard of such a thing being done?

No. I see what you want to do, but I haven't seen it done in that way.

Quote:

2) What is it called? I can't even figure out how to Google it. Our
working name is "horizontal rollup."

I don't think it has a name. It's much more common to just provide
the rolled up data, or the daily data depending on what the person
wants, or roll up all the older data and provide daily for only the
most recent 30 days.

Quote:

In other words: This seems like a fairly obvious way to limit I/O
(query the base table for two small date ranges and query the
aggregate table for the middle date range). So I was really wondering
if anyone had heard of doing this before (and/or whether it might be a
good idea to explore).

The "where" part of the query can be exactly the same for both tables
and if the columns match up, you could get them in one query using
unions.

I don't see where getting the old partial month of data would be
helpful in analysis of the stats. If I were digging into the details,
I'd want daily, monthly rollups, day-of-week rollups, hourly roll-
ups. Daily would be most important for the most recent periods of
time unless a particular issue was being looked at, then the recent
daily info wouldn't be as important.

One I haven't seen that would be interesting is a graph of one
horizontal pixal for each ip range and colored dots and dashes
indicating periods of activity with more hotter colors indicating
higher levels of activity. (Similar to a histogram)

Quote:

///ark

#33

On Sep 23, 5:58*pm, Mark Wilden <m... (AT) mwilden (DOT) com> wrote:

Quote:

I appreciate each and every one of your responses, which have given me
some interesting food for thought.

However, I was still wondering about my original questions, which were

1) Has anyone heard of such a thing being done?

No. I see what you want to do, but I haven't seen it done in that way.

Quote:

2) What is it called? I can't even figure out how to Google it. Our
working name is "horizontal rollup."

I don't think it has a name. It's much more common to just provide
the rolled up data, or the daily data depending on what the person
wants, or roll up all the older data and provide daily for only the
most recent 30 days.

Quote:

In other words: This seems like a fairly obvious way to limit I/O
(query the base table for two small date ranges and query the
aggregate table for the middle date range). So I was really wondering
if anyone had heard of doing this before (and/or whether it might be a
good idea to explore).

The "where" part of the query can be exactly the same for both tables
and if the columns match up, you could get them in one query using
unions.

I don't see where getting the old partial month of data would be
helpful in analysis of the stats. If I were digging into the details,
I'd want daily, monthly rollups, day-of-week rollups, hourly roll-
ups. Daily would be most important for the most recent periods of
time unless a particular issue was being looked at, then the recent
daily info wouldn't be as important.

One I haven't seen that would be interesting is a graph of one
horizontal pixal for each ip range and colored dots and dashes
indicating periods of activity with more hotter colors indicating
higher levels of activity. (Similar to a histogram)

Quote:

///ark

#34

On Sep 23, 5:58*pm, Mark Wilden <m... (AT) mwilden (DOT) com> wrote:

Quote:

I appreciate each and every one of your responses, which have given me
some interesting food for thought.

However, I was still wondering about my original questions, which were

1) Has anyone heard of such a thing being done?

No. I see what you want to do, but I haven't seen it done in that way.

Quote:

2) What is it called? I can't even figure out how to Google it. Our
working name is "horizontal rollup."

I don't think it has a name. It's much more common to just provide
the rolled up data, or the daily data depending on what the person
wants, or roll up all the older data and provide daily for only the
most recent 30 days.

Quote:

In other words: This seems like a fairly obvious way to limit I/O
(query the base table for two small date ranges and query the
aggregate table for the middle date range). So I was really wondering
if anyone had heard of doing this before (and/or whether it might be a
good idea to explore).

The "where" part of the query can be exactly the same for both tables
and if the columns match up, you could get them in one query using
unions.

I don't see where getting the old partial month of data would be
helpful in analysis of the stats. If I were digging into the details,
I'd want daily, monthly rollups, day-of-week rollups, hourly roll-
ups. Daily would be most important for the most recent periods of
time unless a particular issue was being looked at, then the recent
daily info wouldn't be as important.

One I haven't seen that would be interesting is a graph of one
horizontal pixal for each ip range and colored dots and dashes
indicating periods of activity with more hotter colors indicating
higher levels of activity. (Similar to a histogram)

Quote:

///ark

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Querying tables with different aggregation levels

comp.databases comp.databases

Re: Querying tables with different aggregation levels - 09-24-2008 , 10:21 AM

Re: Querying tables with different aggregation levels - 09-24-2008 , 11:34 PM

Re: Querying tables with different aggregation levels - 09-24-2008 , 11:34 PM

Re: Querying tables with different aggregation levels - 09-24-2008 , 11:34 PM