XQuery: how to count how many times a value occurs in a sequence

Question

I know that a function counter can be used to count the number of elements in a given sequence, for example:

count(result/actors/actor)

in this XML:

<result>
    <actors>
        <actor id="00000015">Anderson, Jeff</actor>
        <actor id="00000030">Bishop, Kevin</actor>
        <actor id="0000000f">Bonet, Lisa</actor>
        <actor id="916503207">Parillaud, Anne</actor>
        <actor id="916503208">Pitt, Brad</actor>
        <actor id="916503209">Freeman, Morgan</actor>
        <actor id="916503211">Domingo, Placido</actor>
        <actor id="916503210">Sharif, Omar</actor>
        <actor id="1337">Doqumenteriet2011</actor>
    </actors>
</result>

But what if I want to know how many times a value occurs in a given sequence?

For example, if I wanted to know how many films each actor (actorRef) appeared in the following XML:

<videos>
    <video id="id1235AA0">
        <title>The Fugitive</title>
        <actorRef>00000003</actorRef>
        <actorRef>00000006</actorRef>
    </video>
    <video id="id1244100">
        <title>Enemy of the State</title>
        <actorRef>00000009</actorRef>
        <actorRef>0000000c</actorRef>
        <actorRef>0000000f</actorRef>
        <actorRef>00000012</actorRef>
    </video>
    <video id="id124E230">
        <title>Clerks</title>
        <actorRef>00000015</actorRef>
        <actorRef>00000018</actorRef>
        <actorRef>0000001b</actorRef>
    </video>

I can easily create a list of all the actors that appear and even make them appear as many times in my processed sequence as in XML:

result/videos//actorRef

but I can’t do anything like the fact that, for example, COUNT () and GROUP BY are executed together in SQL to get a list of participants by the number of multiples in the sequence created by the XQuery line above.

How can I create this list?

PS: - , .

Answer 1

, , . , . , .

, , , , . . , , . .

let $actors := fn:distinct-values($results/videos/video/actorRef)

for $actor in $actors
let $count := fn:count($results/videos/video[actorRef = $actor])
Order by $count
return ($actor, $count)

Answer 2

XPath 2.0 (XPath 2.0 - XQuery), , actorRef, ,

 for $maxMovies in 
       max(for $actorId in distinct-values(/*/*/actorRef) 
            return
               count(index-of(/*/*/actorRef, $actorId))
           )
    return 
      (/*/*/actorRef)[index-of(/*/*/actorRef, .)[$maxMovies]]/string()

XML-:

<videos>
    <video id="id1235AA0">
        <title>The Fugitive</title>
        <actorRef>00000003</actorRef>
        <actorRef>00000009</actorRef>
        <actorRef>0000000x</actorRef>
    </video>
    <video id="id1244100">
        <title>Enemy of the State</title>
        <actorRef>00000009</actorRef>
        <actorRef>0000000c</actorRef>
        <actorRef>0000000f</actorRef>
        <actorRef>00000012</actorRef>
    </video>
    <video id="id124E230">
        <title>Clerks</title>
        <actorRef>00000015</actorRef>
        <actorRef>00000018</actorRef>
        <actorRef>0000001b</actorRef>
    </video>
</videos>

, :

00000009

XPath 3.0 ( XQuery 3.0), :

let $vSeq := /*/*/actorRef/string()
  return
    for $maxMovies in 
       max(for $actorId in distinct-values($vSeq) 
            return
              index-of($vSeq, $actorId) ! last()
           )
      return 
        $vSeq[index-of($vSeq, .)[$maxMovies]]

, (!), for-expression:

let $vSeq := /*/*/actorRef/string(),
    $maxOccurs := 
      max(distinct-values($vSeq) ! count(index-of($vSeq, .)) ) 
  return 
    $vSeq[index-of($vSeq, .)[$maxOccurs]]

Answer 3

Tyler's answer is the best solution for what you are ultimately trying to achieve, so I would go with this, but to answer a specific question about how to count the number of times a value occurs in a sequence: you can use the sequence predicate to create a new sequence containing only the values ​​that correspond to what you need, and then calculate that:

let $actors := result/videos//actorRef
for $actor in distinct-values($actors)
return
  ($actor, count($actors[. = $actor]))