I have a table in MariaDB that contains a population, this population is associated to a period and a person. On the population there's a boolean.
I need to calculate the lengths of those, which means if the population (that's a person and period combination) is true for 2 in a row, then the length is 2. If the last is true, then it's active, otherwise it's inactive.
I need to count all lengths, wether it's 1, 2 or more and need for every sum to know which period was the last one.
Does this makes sense?
It's okay if it's a heavy process, then I guess I just create a results table that's easy to read from, but I'm a little blank on the strategy to use here.
hmm if I understand correctly the main problem would be "every sum to know which period was the last one." this would need a variable. And that's usually a stored procedure :)
Unless you could work with groupings and havings since you could use
sum(if yes +1 if no -1) as sumvalue group by common-identifier having sum(field) == 2
in pseudo code because if you look at it mathematically you just need a sequence of two in a row. since you can add and subtract one in any order in the end there has to be a two.
But the way I read it, that's likely not the case
j
stuff ;)
You can write regular logic in SQL using IF statements:
Emil Moe
Senior Data Engineer
It was a long one. Back and forth testing, but I think I managed it now. I used an aggregation table.