I'm trying to find an optimized way to identify if a specific set of values exists in a list.
For example, lets assume the following list of records in a table
Id Value
1 A
2 B
3 A
4 C
5 A
6 B
7 C
8 C
9 A
I'm trying to find a way to check how much times the sequence {A, B} or {A, B, C} occurs, for example.
I know I can do this with cursors but I was checking if there's any other option that would be preferable in terms of performance.
The result I'd expect would by something like this:
{A, B}: 2 times:
{A, B, C}: 1 time.
I'm using Sql Server.
The easiest way to count the number of occurrences in a Python list of a given item is to use the Python . count() method. The method is applied to a given list and takes a single argument. The argument passed into the method is counted and the number of occurrences of that item in the list is returned.
To find an element in the list, use the Python list index() method, The index() is an inbuilt Python method that searches for an item in the list and returns its index. The index() method finds the given element in the list and returns its position.
Probably the simplest way is to use the ANSI standard functions lag()
and/or lead()
:
select count(*)
from (select t.*,
lead(value) over (order by id) as next_value,
lead(value, 2) over (order by id) as next_value2,
from t
) t
where value = 'A' and next_value = 'B' and next_value2 = 'C';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With