I think it's possible with CTE.
Recently I wondered whether it would be possible to implement an operation
that might be called "an accumulated group by". It's when you enumerate the
rows and based on the values of the previous row and current row you apply
some new "group" value that can be used in the following "group by" query.
My use case was a "words" table when every word has a length and the task
is to "format" them as the words are formatted when printing a text on the
page or on the screen, so the new "group" here is a printing row that
increased when the current printing line is no longer able to fit the
following word.
I see your task is similar, even a little simpler since we don't accumulate
here, we just compare previous and next values.
CTE worked for me, but the big problem is that there are much redundancy in
the text of the query since contrary to general select queries, it's much
harder to to reusable aliasing in CTE, you will see this in the final query
of this post.
The basic template for any filtering for "accumulated group by".
given the table
CREATE TABLE [testdata] ([id] integer primary key)
the following query outputs the table as it is but the second select inside
now has a luxury of compare previous and next values (ordered by the
primary key).
with recursive
filter(curid) as
(
select (select min(id) from testdata)
UNION ALL
select (select id from testdata where id > curid order by id limit 1)
as nextid from filter where nextid not null
)
select * from filter
Back to your case
if the table is
CREATE TABLE [testdata] ([id] integer primary key, [bc] integer, [temp]
integer)
the following gigantic query should output the counts for every consecutive
groups ("grp" here is a temporal column used exclusively for the final
group by I was talking about previously).
with recursive
filter(curid, bc, temp, grp) as
(
select (select min(id) from testdata), (select bc from testdata where
id=(select min(id) from testdata)), (select temp from testdata where
id=(select min(id) from testdata)), 1
UNION ALL
select (select id from testdata where id > curid order by id limit 1)
as nextid, (select bc from testdata where id > curid order by id limit 1),
(select temp from testdata where id > curid order by id limit 1),
case when (select bc from testdata where id > curid order by id limit 1) =
bc then grp else grp + 1 end
from filter
where nextid not null
)
select bc, count(*) from filter group by grp
I'll be glad to reduce expressions here to some more readable constructs,
but I suspect it's impossible with current SQL syntax.
Max
On Thu, Dec 7, 2017 at 9:46 PM, <nitpi...@arcor.de> wrote:
> Hi all,
>
> I have a DB i.e. like this:
>
> table values
> bc temp
> 35 123
> 35 124
> 35 123
> 20 123
> 12 123
> 12 123
> 16 123
> 35 123
> 35 123
> 35 123
> 35 123
> 35 123
>
> The value in temp (or all the other columns) is not of interest.
>
> Now I a looking for the basic concept how to count the number of rows where
> bc=bc_of_last_row after the last different bc.
>
> SELECT COUNT(bc) FROM values WHERE bc=35;
>
> gives me the number of all rows with bc=35, so here 8.
> But I am looking for 5, the number of rows after the last change of bc.
>
> Regards Matth
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
