title: SPARQL full aggregation on a group aggregation
body: <p>I have an Ontology where users can use one of five predicates to express how much they like an item.</p>
  
  <p>The Ontology contains specific predicates that have a property called hasSimilarityValue.</p>
  
  <p>I am trying to do the following:</p>
  
  <ol>
  <li>Having a user let's say rs:ania</li>
  <li>Extract all the items that this user has rated before. (this is easy because the Ontology already contains triple from the user to the items)</li>
  <li><p>Extract similary items to the items that have been extracted in step 2 and calculate their similarities. (here we are using our own approach to calculate the similarites ). However the issue is: from step 2, we have many items the user has rated, from step there we are extracting and calculating similar items to these items that came from step 2. So, it is possible that an item in step 3 is similar to two (or more) items from step 2. Thus we end up with the following:</p>
  
  <p>user :ania rated item x1
  user :ania rated item x2
  item y is similar by y1 to x1
  item y is similar by y2 to x2
  item z is similar by z1 to x1</p></li>
  </ol>
  
  <p>y1, y2, and z1 are values between 0 and 1</p>
  
  <p>the thing is that we need to normalize these values to know the final similarities for item y and item z. </p>
  
  <p>the normalization is simple, just group by the item and divide by the <strong>maximum number of items</strong></p>
  
  <p>so to know the similarity with y, i should do (y1+y2/2)</p>
  
  <p>to know the similarity with z, i should do (z1/2)</p>
  
  <p><strong>my problem</strong></p>
  
  <p>as you see, i need to count the items and then know the max of this count</p>
  
  <p>this is the query that calculates everything <strong>without the normalization part</strong></p>
  
  <pre><code>select  ?s  (sum(?weight * ?factor) as ?similarity) ( sum(?weight * ?factor * ?ratings) as ?similarityWithRating) (count(distinct ?x) as ?countOfItemsUsedInDeterminingTheSimilarities) where {
  
      values (?user) { (rs:ania) }
      values (?ratingPredict) {(rs:ratedBy4Stars)  (rs:ratedBy5Stars)}
      ?user ?ratingPredict ?x.
      ?ratingPredict rs:hasRatingValue ?ratings.
      {
        ?s ?p ?o .
        ?x ?p ?o .
        bind(4/7 as ?weight)
      }
      union
      {
        ?s ?a ?b . ?b ?p ?o .
        ?x ?c ?d . ?d ?p ?o .
        bind(1/7 as ?weight)
      }
      ?p rs:hasSimilarityValue ?factor .
        filter (?s != ?x)
    }
    group by ?s
  
  order by ?s
  </code></pre>
  
  <p>the result is:
  <a href="https://i.stack.imgur.com/5zDch.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5zDch.png" alt="enter image description here"></a></p>
  
  <p>now I need to divide each row by the maximum of the count column, </p>
  
  <p>my proposed solution is to repeat the exact query twice, once to get the similarities and once to get the max and then join them and then do the divide (normalization). it is working but it is ugly, the performance will be disaster because i am repeating the same query twice. it is stupid solution and i would like to ask you guys for a better one please</p>
  
  <p>here is my stupid solutions</p>
  
  <pre><code> PREFIX rdfs: &lt;http://www.w3.org/2000/01/rdf-schema#&gt;
  PREFIX rs: &lt;http://www.musicontology.com/rs#&gt;
  PREFIX pdf: &lt;http://www.w3.org/1999/02/22-rdf-syntax-ns#&gt;
  
  #select 
  #?s   ?similarityWithRating (max(?countOfItemsUsedInDeterminingTheSimilarities) as ?maxNumberOfItemsUsedInDeterminingTheSimilarities)
  #where {
   # {
  select ?s ?similarity ?similarityWithRating ?countOfItemsUsedInDeterminingTheSimilarities ?maxCountOfItemsUsedInDeterminingTheSimilarities ?finalSimilarity where {
  {
  select  ?s  (sum(?weight * ?factor) as ?similarity) ( sum(?weight * ?factor * ?ratings) as ?similarityWithRating) (count(distinct ?x) as ?countOfItemsUsedInDeterminingTheSimilarities) where {
  
      values (?user) { (rs:ania) }
      values (?ratingPredict) {(rs:ratedBy4Stars)  (rs:ratedBy5Stars)}
      ?user ?ratingPredict ?x.
      ?ratingPredict rs:hasRatingValue ?ratings.
      {
        ?s ?p ?o .
        ?x ?p ?o .
        bind(4/7 as ?weight)
      }
      union
      {
        ?s ?a ?b . ?b ?p ?o .
        ?x ?c ?d . ?d ?p ?o .
        bind(1/7 as ?weight)
      }
      ?p rs:hasSimilarityValue ?factor .
        filter (?s != ?x)
    }
    group by ?s
  #}
  
  #}
  #group by ?s 
  order by ?s
  } #end first part
  {
  select (Max(?countOfItemsUsedInDeterminingTheSimilarities) as ?maxCountOfItemsUsedInDeterminingTheSimilarities) where {
  select  ?s  (sum(?weight * ?factor) as ?similarity) ( sum(?weight * ?factor * ?ratings) as ?similarityWithRating) (count(distinct ?x) as ?countOfItemsUsedInDeterminingTheSimilarities) where {
  
      values (?user) { (rs:ania) }
      values (?ratingPredict) {(rs:ratedBy4Stars)  (rs:ratedBy5Stars)}
      ?user ?ratingPredict ?x.
      ?ratingPredict rs:hasRatingValue ?ratings.
      {
        ?s ?p ?o .
        ?x ?p ?o .
        bind(4/7 as ?weight)
      }
      union
      {
        ?s ?a ?b . ?b ?p ?o .
        ?x ?c ?d . ?d ?p ?o .
        bind(1/7 as ?weight)
      }
      ?p rs:hasSimilarityValue ?factor .
        filter (?s != ?x)
    }
    group by ?s
  #}
  
  #}
  #group by ?s 
  order by ?s
  }
  }#end second part
    bind (?similarityWithRating/?maxCountOfItemsUsedInDeterminingTheSimilarities as ?finalSimilarity)
  }
  order by desc(?finalSimilarity)
  </code></pre>
  
  <h3>Finally</h3>
  
  <p>Here is the data if you want to try it yourself.
  <a href="http://www.mediafire.com/view/r4qlu3uxijs4y30/musicontology" rel="nofollow noreferrer">http://www.mediafire.com/view/r4qlu3uxijs4y30/musicontology</a></p>