Skip to content
Advertisement

Count rows with equal values in a window function

I have a time series in a SQLite Database and want to analyze it.

The important part of the time series consists of a column with different but not unique string values. I want to do something like this:

I don’t know how to get the countValue column. It should count all Values of the partition equal to the current rows Value.

I tried this but it just counts all Values in the partition and not the Values equal to this rows Value.

The query is also limited by these factors:

  1. The query should work with any amount of unique Values
  2. The query should work with any Partition Size (ROWS BETWEEN n PRECEDING AND CURRENT ROW)

Is this even possible using only SQL?

Advertisement

Answer

Here is an approach using string functions:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement