## Forum Stats

• 3,853,829 Users
• 2,264,280 Discussions

Discussions

# Count consecutive repetitive element in a sequence using XQuery

If the Sequence = [a a b c c c a d d e e e f g h h]

then the Output = [1 2 1 1 2 3 1 1 2 1 2 3 1 1 1 2]

Have tried to use recursion but no luck...Please Help..Thanks in Anticipation

Note: Using XQuery implementation 1.0

One of my failed implementation looks like:

`<span class="pln" data-wr_replaced="true" style="color: #000000;">declare function local:test(\$sequence,\$count){<br/><br/>for \$counter in (1 to count(\$sequence))<br/><br/>let \$maxIndex := count(\$sequence)<br/><br/>return<br/><br/>if (matches(subsequence(\$sequence,1,\$maxIndex)[\$counter],subsequence(\$sequence,1,\$maxIndex)[\$counter + +1])) then let \$count := \$count + 1 return \$count[last()]<br/><br/>else let \$count := 1 return \$count[last()]<br/><br/><br/>};</span>`

It seems that as \$counter is a sequence hence it returns a sequence in matches condition and hence this implementation fail

• Interesting exercise, easily solved with SQL analytic functions but a little bit harder using XQuery.

Here's one way :

```declare function local:sequence-group(\$seq as item()*) as item()*
{
let \$start-of-group :=
fn:index-of(
for \$i in 1 to count(\$seq)
let \$prev := \$seq[\$i - 1]
return if (\$prev != \$seq[\$i] or not(\$prev)) then 1 else 0
, 1
)
return
for \$i in 1 to count(\$seq)
return \$i - \$start-of-group[. le \$i][last()] + 1
};
let \$seq := ("a", "a", "b", "c", "c", "c", "a", "d", "d", "e", "e", "e", "f", "g", "h", "h")
return local:sequence-group(\$seq)
```

The idea is to first find the starting index of each group, in this case :

\$start-of-group := (1, 3, 4, 7, 8, 10, 13, 14, 15)

Then for each item in the sequence, we just have to retrieve the associated starting index and substract it from the current position.

• Interesting exercise, easily solved with SQL analytic functions but a little bit harder using XQuery.

Here's one way :

```declare function local:sequence-group(\$seq as item()*) as item()*
{
let \$start-of-group :=
fn:index-of(
for \$i in 1 to count(\$seq)
let \$prev := \$seq[\$i - 1]
return if (\$prev != \$seq[\$i] or not(\$prev)) then 1 else 0
, 1
)
return
for \$i in 1 to count(\$seq)
return \$i - \$start-of-group[. le \$i][last()] + 1
};
let \$seq := ("a", "a", "b", "c", "c", "c", "a", "d", "d", "e", "e", "e", "f", "g", "h", "h")
return local:sequence-group(\$seq)
```

The idea is to first find the starting index of each group, in this case :

\$start-of-group := (1, 3, 4, 7, 8, 10, 13, 14, 15)

Then for each item in the sequence, we just have to retrieve the associated starting index and substract it from the current position.

• Thanks @odie_63. Nice example of function declaration as well.

```> SELECT XMLQuery(
'declare function local:sequence-group(\$seq as item()*) as item()*
{
let \$start-of-group :=
fn:index-of(
for \$i in 1 to count(\$seq)
let \$prev := \$seq[\$i - 1]
return if (\$prev != \$seq[\$i] or not(\$prev)) then 1 else 0
, 1
)
return
for \$i in 1 to count(\$seq)
return \$i - \$start-of-group[. le \$i][last()] + 1
};
let \$seq := ("a", "a", "b", "c", "c", "c", "a", "d", "d", "e", "e", "e", "f", "g", "h", "h")
return local:sequence-group(\$seq)'
RETURNING CONTENT) as content
FROM dual

CONTENT
------------------------------------
1 2 1 1 2 3 1 1 2 1 2 3 1 1 1 2
```
This discussion has been closed.