With recursive route, I am thinking I need to build deleteList(x,y). But I can
not come up with a way to use deleteList only once in the FROM after UNION and
not in subqueries , as required by WITH RECURSIVE. Assuming pairsTable(x,y) is
the input table:
WITH RECURSIVE deleteList(x, y) AS
( SELECT NULL, NULL
UNION
SELECT x, y FROM pairsTable
WHERE x IN (SELECT x FROM (SELECT x, y FROM pairsTable
EXCEPT
SELECT x, y FROM
deleteList WHERE x IS NOT NULL)
GROUP BY x HAVING count(x) < 25)
)
SELECT x, y FROM deleteList;
Roman
________________________________________
From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of
David Raymond [david.raym...@tomtom.com]
Sent: Tuesday, May 01, 2018 10:27 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?
My initial thought on this would be recursive on delete triggers. You're
limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really
big cascades wouldn't fully complete. You can raise the limit, but
mathematically speaking there's still going to be a limit then.
Will have to think about the recursive CTE route later.
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of R Smith
Sent: Tuesday, May 01, 2018 8:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] probably recursive?
That depends on what you mean by "Could this be achieved in SQLite?".
There is no query (in any SQL engine) that can depend on a sub-query
that is itself dependent on the outcome of the main query. This is what
makes recursion beautiful, but then there is also no CTE (or other query
in any SQL engine) that can recurse over multiple states of data (i.e.
query data in one single query to reflect results from both before and
after a delete in the source table), nor can a CTE be updated or deleted
from, its data must persist atomically (with some exceptions when using
non-deterministic functions, like random).
These are not so much "inabilities" of SQL engines, but more due to
explicit SQL and set-algebra rules.
So this is not possible in a single query.
You can of course "achieve" it using any SQL engine by constructing a
temporary table, and then repeatedly run a DELETE query for all x values
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x)
< nX, rinse, repeat until both SELECT y HAVING COUNT(x) < nX and SELECT
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this
would be painfully slow next to a simple software algorithm that
prunes/resolves a 2-dimensional array - exponentially worse so for
larger grid sizes.
On 2018/05/01 2:45 AM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have trouble solving this problem, maybe it is impossible?
>
> I have a table with two columns x and y, both integers. Imagine they are
> coordinates on X-Y plane, dots. I need to find all x's that have more than nX
> dots, and all y's that have more than nY dots. Both conditions must be
> simultaneous in the following sense:
>
> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of
> deletion, y=3 which previously had more than nY dots no longer passes the
> threshold and thus y=3 must be deleted too. This could cause deletion of some
> other x, etc. At the end, number of dots on all vertical lines must be more
> than nX and number of dots on all horizontal lines must be more than nY.
>
> Could this be achieved with SQLite?
>
> Roman
> _______________________________________________
> 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
_______________________________________________
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
