XSLT Aggregation using sort with Muenchian Method

2019-05-07 05:59发布

站内文章 / 前端开发
7 0 0

问题:

I understand the Muenchian Method indexes the entire document at first read. But how can one sort prior or within grouping but not on its key? Specifically, how can I order a sibling of the key in order to calculate min and max of a series of element values?

As XML data shows below, I am attempting to aggregate Companies by Industry. Across the seven metrics (revenue, assets, equity, netincome, stockprice, and employees) I can achieve various aggregates: sum with sum() and average with sum() div count(). But in order to obtain min and max particularly for netincome, I need to sort companies within industry by netincome and select the value at a position(). Do note, there are exactly five companies per industry. So for decreasing netincome sort, position()=1 is the maximum and position()=5 would be minimum.

Now, I can achieve my desired results running two XSLT scripts. The first sorts and second aggregates. But how can I do so with one XSLT? Within for each loop of Muenchian key I tried the below all to no avail.

<xsl:sort select="../netincome" data-type="number" order="descending"/>

<xsl:sort select="key('indkey', .)/../netincome" data-type="number" order="descending"/>

<xsl:sort select="key('indkey', .)[../netincome]" data-type="number" order="descending"/>

Possibly, the min and max must be done in a template outside the others or run two passes/call templates of the XSLT scripts or using <xsl:with-params>.

XML data

<?xml version="1.0" encoding="UTF-8"?>
<data>
    <bigcompany>
        <company>Company OA</company>
        <industry>Oil &amp; Gas</industry>
        <revenue>394105000000</revenue>
        <assets>349493000000</assets>
        <equity>174399000000</equity>
        <netincome>32520000000</netincome>
        <stockprice>89.38</stockprice>
        <employees>75300</employees>
    </bigcompany>
    <bigcompany>
        <company>Company OB</company>
        <industry>Oil &amp; Gas</industry>
        <revenue>200494000000</revenue>
        <assets>266026000000</assets>
        <equity>156191000000</equity>
        <netincome>19241000000</netincome>
        <stockprice>108.62</stockprice>
        <employees>64700</employees>
    </bigcompany>
    <bigcompany>
        <company>Company OC</company>
        <industry>Oil &amp; Gas</industry>
        <revenue>13807000000</revenue>
        <assets>4726000000</assets>
        <equity>16445000000</equity>
        <netincome>2720000000</netincome>
        <stockprice>48.5</stockprice>
        <employees>22000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company OD</company>
        <industry>Oil &amp; Gas</industry>
        <revenue>97800000000</revenue>
        <assets>30500000000</assets>
        <equity>10800000000</equity>
        <netincome>2700000000</netincome>
        <stockprice>27.53</stockprice>
        <employees>45340</employees>
    </bigcompany>
    <bigcompany>
        <company>Company OE</company>
        <industry>Oil &amp; Gas</industry>
        <revenue>62004000000</revenue>
        <assets>117144000000</assets>
        <equity>48427000000</equity>
        <netincome>8428000000</netincome>
        <stockprice>66.66</stockprice>
        <employees>16900</employees>
    </bigcompany>
    <bigcompany>
        <company>Company PA</company>
        <industry>Pharmaceuticals</industry>
        <revenue>49605000000</revenue>
        <assets>169274000000</assets>
        <equity>71622000000</equity>
        <netincome>9135000000</netincome>
        <stockprice>30.14</stockprice>
        <employees>78000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company PB</company>
        <industry>Pharmaceuticals</industry>
        <revenue>48047000000</revenue>
        <assets>105128000000</assets>
        <equity>56943000000</equity>
        <netincome>6272000000</netincome>
        <stockprice>55.43</stockprice>
        <employees>76000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company PC</company>
        <industry>Pharmaceuticals</industry>
        <revenue>74331000000</revenue>
        <assets>131119000000</assets>
        <equity>69752000000</equity>
        <netincome>16323000000</netincome>
        <stockprice>102.31</stockprice>
        <employees>126500</employees>
    </bigcompany>
    <bigcompany>
        <company>Company PD</company>
        <industry>Pharmaceuticals</industry>
        <revenue>23113000000</revenue>
        <assets>35249000000</assets>
        <equity>17641000000</equity>
        <netincome>4685000000</netincome>
        <stockprice>67.2</stockprice>
        <employees>37925</employees>
    </bigcompany>
    <bigcompany>
        <company>Company PE</company>
        <industry>Pharmaceuticals</industry>
        <revenue>15879000000</revenue>
        <assets>33749000000</assets>
        <equity>14852000000</equity>
        <netincome>2004000000</netincome>
        <stockprice>58</stockprice>
        <employees>28000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company MA</company>
        <industry>Media</industry>
        <revenue>48813000000</revenue>
        <assets>84186000000</assets>
        <equity>44958000000</equity>
        <netincome>8004000000</netincome>
        <stockprice>93.65</stockprice>
        <employees>180000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company MB</company>
        <industry>Media</industry>
        <revenue>64657000000</revenue>
        <assets>158813000000</assets>
        <equity>51058000000</equity>
        <netincome>7135000000</netincome>
        <stockprice>57.05</stockprice>
        <employees>139000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company MC</company>
        <industry>Media</industry>
        <revenue>31867000000</revenue>
        <assets>54793000000</assets>
        <equity>17418000000</equity>
        <netincome>4514000000</netincome>
        <stockprice>36.52</stockprice>
        <employees>27000</employees>
    </bigcompany>
    <bigcompany>
        <company>TCompany MD</company>
        <industry>Media</industry>
        <revenue>29795000000</revenue>
        <assets>67994000000</assets>
        <equity>29904000000</equity>
        <netincome>3691000000</netincome>
        <stockprice>84.3</stockprice>
        <employees>26000</employees>
    </bigcompany>
    <bigcompany>
        <company>Company ME</company>
        <industry>Media</industry>
        <revenue>15284000000</revenue>
        <assets>26387000000</assets>
        <equity>9966000000</equity>
        <netincome>1879000000</netincome>
        <stockprice>54.88</stockprice>
        <employees>20915</employees>
    </bigcompany>
</data>

XSLT 1

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/>    

  <xsl:template match="data">
    <xsl:copy>
      <xsl:apply-templates>        
        <xsl:sort select="industry" order="ascending"/>
        <xsl:sort select="netincome" data-type="number" order="descending"/> 
      </xsl:apply-templates>
    </xsl:copy>
  </xsl:template>

  </xsl:stylesheet>

XSLT 2

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/> 

  <xsl:key name="indkey" match="bigcompany/industry" use="."/>

  <xsl:template match="data">
    <data>
    <xsl:for-each select="bigcompany/industry[generate-id()    
                         = generate-id(key('indkey', .)[1])]">  
      <xsl:sort select="." order="ascending"/>                    

        <aggdata>
          <xsl:copy-of select="."/>        
          <SumOfRevenue><xsl:copy-of select="sum(key('indkey', .)/../revenue)"/></SumOfRevenue>
          <AvgOfAssets><xsl:copy-of select="sum(key('indkey', .)/../assets) div count(key('indkey', .)/../assets)"/></AvgOfAssets>
          <AvgOfEquity><xsl:copy-of select="sum(key('indkey', .)/../equity) div count(key('indkey', .)/../equity)"/></AvgOfEquity>
          <MaxOfIncome><xsl:value-of select="key('indkey', .)[1]/../netincome"/></MaxOfIncome>
          <MinOfIncome><xsl:value-of select="key('indkey', .)[5]/../netincome"/></MinOfIncome>
          <AvgOfStockPrice><xsl:copy-of select="sum(key('indkey', .)/../stockprice) div count(key('indkey', .)/../stockprice)"/></AvgOfStockPrice>
          <SumOfEmployees><xsl:copy-of select="sum(key('indkey', .)/../employees)"/></SumOfEmployees>
        </aggdata>

    </xsl:for-each>

    </data>
  </xsl:template>
</xsl:stylesheet>

Final and Desired results - but how with one XSLT?

<?xml version='1.0' encoding='UTF-8'?>
<data>
  <aggdata>
    <industry>Media</industry>
    <SumOfRevenue>1.90416e+011</SumOfRevenue>
    <AvgOfAssets>7.84346e+010</AvgOfAssets>
    <AvgOfEquity>3.06608e+010</AvgOfEquity>
    <MaxOfIncome>8004000000</MaxOfIncome>
    <MinOfIncome>1879000000</MinOfIncome>
    <AvgOfStockPrice>65.28</AvgOfStockPrice>
    <SumOfEmployees>392915</SumOfEmployees>
  </aggdata>
  <aggdata>
    <industry>Oil &amp; Gas</industry>
    <SumOfRevenue>7.6821e+011</SumOfRevenue>
    <AvgOfAssets>1.535778e+011</AvgOfAssets>
    <AvgOfEquity>8.12524e+010</AvgOfEquity>
    <MaxOfIncome>32520000000</MaxOfIncome>
    <MinOfIncome>2700000000</MinOfIncome>
    <AvgOfStockPrice>68.138</AvgOfStockPrice>
    <SumOfEmployees>224240</SumOfEmployees>
  </aggdata>
  <aggdata>
    <industry>Pharmaceuticals</industry>
    <SumOfRevenue>2.10975e+011</SumOfRevenue>
    <AvgOfAssets>9.49038e+010</AvgOfAssets>
    <AvgOfEquity>4.6162e+010</AvgOfEquity>
    <MaxOfIncome>16323000000</MaxOfIncome>
    <MinOfIncome>2004000000</MinOfIncome>
    <AvgOfStockPrice>62.616</AvgOfStockPrice>
    <SumOfEmployees>346425</SumOfEmployees>
  </aggdata>
</data>

回答1:

How about this way?

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:key name="co-by-ind" match="bigcompany" use="industry" />

<xsl:template match="/data">
    <xsl:copy>
        <xsl:for-each select="bigcompany[generate-id() = generate-id(key('co-by-ind', industry)[1])]">  
            <xsl:sort select="industry" data-type="text" order="ascending"/>                   
            <!-- variables -->
            <xsl:variable name="curr-group" select="key('co-by-ind', industry)" />
            <xsl:variable name="income-sorted">
                <xsl:for-each select="$curr-group">
                    <xsl:sort select="netincome" data-type="number" order="ascending"/>
                    <xsl:copy-of select="netincome"/>
                </xsl:for-each>
            </xsl:variable>
            <xsl:variable name="income-sorted-set" select="exsl:node-set($income-sorted)/netincome" />
            <!-- output -->
            <aggdata>
                <xsl:copy-of select="industry"/>        
                <SumOfRevenue>
                    <xsl:value-of select="sum($curr-group/revenue)"/>
                </SumOfRevenue>
                <AvgOfAssets>
                    <xsl:value-of select="sum($curr-group/assets) div count($curr-group/assets)"/>
                </AvgOfAssets>
                <AvgOfEquity>
                    <xsl:value-of select="sum($curr-group/equity) div count($curr-group/equity)"/>
                </AvgOfEquity>
                <MaxOfIncome>
                    <xsl:value-of select="$income-sorted-set[last()]"/>
                </MaxOfIncome>
                <MinOfIncome>
                    <xsl:value-of select="$income-sorted-set[1]"/>
                </MinOfIncome>
                <AvgOfStockPrice>
                    <xsl:value-of select="sum($curr-group/stockprice) div count($curr-group/stockprice)"/>
                </AvgOfStockPrice>
                <SumOfEmployees>
                    <xsl:value-of select="sum($curr-group/employees)"/>
                </SumOfEmployees>
            </aggdata>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>


回答2:

Here is a shorter and simpler XSLT 1.0 solution that doesn't use any extension function:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/>

 <xsl:key name="kByInd" match="bigcompany" use="industry"/>

 <xsl:template match="/*">
   <data>
       <xsl:apply-templates select=
           "bigcompany[generate-id() = generate-id(key('kByInd', industry)[1])]">
         <xsl:sort select="industry"/>
       </xsl:apply-templates>
   </data>
 </xsl:template>

  <xsl:template match="bigcompany">
    <xsl:variable name="vGroup" select="key('kByInd', industry)"/>
    <xsl:variable name="vgroupCount" select="count($vGroup)"/>

    <xsl:variable name="vMaxMin">
      <xsl:apply-templates select="$vGroup/netincome">
        <xsl:sort data-type="number"/>
      </xsl:apply-templates>
    </xsl:variable>

    <aggdata>
      <xsl:copy-of select="industry"/>
      <SumOfRevenue><xsl:copy-of select="sum($vGroup/revenue)"/></SumOfRevenue>
      <AvgOfAssets><xsl:copy-of select="sum($vGroup/assets) div $vgroupCount"/></AvgOfAssets>
      <AvgOfEquity><xsl:copy-of select="sum($vGroup/equity) div $vgroupCount"/></AvgOfEquity>
      <MaxOfIncome><xsl:copy-of select="substring-before(substring-after($vMaxMin, '|'), '|')"/></MaxOfIncome>
      <MinOfIncome><xsl:copy-of select="substring-before($vMaxMin, '|')"/></MinOfIncome>
      <AvgOfStockPrice><xsl:copy-of select="sum($vGroup/stockprice) div $vgroupCount"/></AvgOfStockPrice>
      <SumOfEmployees><xsl:copy-of select="sum($vGroup/employees)"/></SumOfEmployees>
    </aggdata>
  </xsl:template>

  <xsl:template match="netincome">
   <xsl:if test="position() = 1 or position() = last()">
       <xsl:value-of select="concat(., '|')"/>
   </xsl:if>
  </xsl:template>
  <xsl:template match="text()"/>
</xsl:stylesheet>


标签： xml xslt
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~