Good day to all. There is a database with columns id, date, value. id - id of a person, can be repeated. date is the year of recording. value - for example, the digits 0 or 3.

With each entry in the database, the person is assigned a value, which can change (make the transition) in consequence (next year). The value may increase, but not decrease.

The essence of the problem. Build a table with columns: date, count. Where date is the year, and count is a field that shows the number of transitions from any group to group 3.

In which direction to think? The task is not tied to any dB, we just need an algorithm for how to do it. If it helps, then I work for pandas. I tried the option of grouping by date and count of records that have a value less than 3. And the second group have a value of 3. After I subtracted the value, something nonsense was obtained ...

  • Give an example of the source data, and what you need to get as a result - Bald
  • Build a table - this is a prerequisite? may be enough view or just the result of the select query? - aleksandr barakin

1 answer 1

If you mean the algorithm without binding to the database, then I would create a trigger that would change the count value every time a value of a person would change.