Subject: Re: Sum one attribute while grouping by another
From: Wendell Piez <wapiez@xxxxxxxxxxxxxxxx>
Date: Thu, 24 Jul 2008 15:44:18 -0400
|
DB,
If you can't use XSLT 2.0, you would be best off in 1.0 using the
so-called Muenchian grouping technique to collect your groups. It
would allow the collection of nodes that you need. It is also more
efficient and will perform better over a large data set than the
method you are using. (Actually, at present you aren't grouping at
all, as is indicated by your dependency on the items being sorted by
type -- you are only using comparison logic to recognize the start
and end of groups that are already implicit in the order. A grouping
algorithm would be able to work with groups that were not sorted in the input.)
The essence of Muenchian grouping is in the use of key-based
retrieval to collect all the elements belonging to a particular
group. For example, if you had
<xsl:key name="items-by-type" match="item" use="@type"/>
you would then be able to collect all the 'fruit' items using
"key('items-by-type','fruit')" and all the items of type $x using
"key('items-by-type',$x)".
Not only is this useful for grouping, but it's helpful for
aggregating, as you can then say
"sum(key('items-by-type','fruit')/@rotten)" to add together the
@rotten attributes on the 'fruit' items.
Muenchian grouping is well documented on line. Besides key-based
retrieval, the one subtlety it depends on is an idiom to recognize a
single representative member of each group (commonly the first one).
Usually, for this you'll see something like
generate-id(.)=generate-id(key('items-by-type',@type)[1]
which will be true only for every first item of its type. You need
such a de-duplication technique to avoid repeating groups.
If this plus your further research leaves you confused, feel free to
ask for more detail or clarification.
Cheers,
Wendell
At 02:06 PM 7/24/2008, you wrote:
How do I sum one attribute while grouping by another?
From examples online (including posts in this list) I'm pretty sure
a recursive template is necessary but I can't figure out how.
Unfortunately, all the examples are too simple (e.g. increment
counter with position()) or assume you can divide the data by
elements. All my elements are the same.
For instance, I have the following XML data:
<food>
<item type="fruit" name="apple" rotten="6" total="10"/>
<item type="fruit" name="orange" rotten="2" total="10"/>
<item type="fruit" name="pear" rotten="1" total="4"/>
<item type="vegetable" name="carrot" rotten="0" total="5"/>
<item type="vegetable" name="potato" rotten="1" total="2"/>
</food>
To which I apply a stylesheet to get a nice table output. I know how
to get to overall sums, but I don't know how to get the group/inner
sums (denoted by '?'):
type name rotten total percent
fruit
apple 6 10 60
orange 2 10 20
pear 1 4 25
total ? ? ?
vegetable
carrot 0 5 0
potato 1 2 50
total ? ? ?
total 10 31 32.3
Here are some assumptions that can be made about the data:
* The items are guaranteed to be sorted by the "type" attribute.
* I don't know the possible values of "type" beforehand.
* Its format cannot be changed.
Also:
* I am using XSLT 1.0, but upgrading to 2.0 might be possible (I'm not sure).
* I am using python 2.4 with libxml2 and libxslt.
Here's the stylesheet I have now (that generated the above table):
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html><body><table>
<tr>
<td>type</td>
<td>name</td>
<td>rotten</td>
<td>total</td>
<td>percent</td>
</tr>
<xsl:for-each select="//item">
<xsl:if test="not(preceding-sibling::*[1]/@type=@type)">
<tr><td><xsl:value-of select="@type"/></td></tr>
</xsl:if>
<tr>
<td></td>
<td><xsl:value-of select="@name"/></td>
<td><xsl:value-of select="@rotten"/></td>
<td><xsl:value-of select="@total"/></td>
<td><xsl:value-of select="format-number(@rotten div @total *
100,'##.#')"/></td>
</tr>
<xsl:if test="not(following-sibling::*[1]/@type=@type)">
<tr>
<td></td>
<td>total</td>
<td>?</td>
<td>?</td>
<td>?</td>
</tr>
</xsl:if>
</xsl:for-each>
<tr>
<td>total</td>
<td></td>
<td><xsl:value-of select="sum(//item/@rotten)"/></td>
<td><xsl:value-of select="sum(//item/@total)"/></td>
<td><xsl:value-of select="format-number(sum(//item/@rotten)
div sum(//item/@total) * 100,'##.#')"/></td>
</tr>
</table></body></html>
</xsl:template>
</xsl:stylesheet>
======================================================================
Wendell Piez mailto:wapiez@xxxxxxxxxxxxxxxx
Mulberry Technologies, Inc. http://www.mulberrytech.com
17 West Jefferson Street Direct Phone: 301/315-9635
Suite 207 Phone: 301/315-9631
Rockville, MD 20850 Fax: 301/315-8285
----------------------------------------------------------------------
Mulberry Technologies: A Consultancy Specializing in SGML and XML
======================================================================
|
Cart
