partitioning a hierarchical query into subtrees

I have a problem doing analysis of logging data in a webbased collaboration environment.

I want to compute values for most active users, most active readers, average response time etc. based on an event log stored in a sql table ("eventaudit").

the system i am working is designed in an hierachical way. what means that all items are organized in one hierarchy. the hierachy is represented in a second table ("itemtree"). childs are linked to parents by a "parentid" row.

analysing the eventlog of one subtree of the hierarchy was easy. i joined the "eventaudit"-table with a subset of the "itemtree"-table that countaind all items under a certain root element. The subtree was created using the ORACLE connect-by statement.

computing different values of most-active users, average response time etc. for the subtrees under the root element in one single query, however, is beyond my expertise.

how can i create partitions that reflect substrees of a hierachical query result is my question.

--
Posted via http://dbforums.com