Thank you Cezary and others who commented.
For some reason, I did not receive email from Cezary, only comments on it.
I was under impression that RECURSIVE can not be used in sub-query. I see that
it can.
But, most importantly, could you elaborate more on how it works. I agree it is
n-to-n problem. But the solution merges all data into a single cell with all
pairs, which is counter to relational solution.
I ask for details, if possible, because the actual problem that I have to
solve is a bit more complicated: I have two of such lists good(x,y) and
bad(x,y) with a coupling condition that if x is removed from one list, it must
be removed from the other. This is easy to add for ones who understand how it
works.
Roman
________________________________________
From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of
E.Pasma [pasm...@concepts.nl]
Sent: Friday, May 04, 2018 10:35 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?
Cezary H. Noweta wrote:
>>
>>> At the beginning I would like to agree with that the problem is
>>> iterative rather then recursive one. However....
R. Smith wrote:
>
>> LOL, that might be the hackiest query I ever seen, but kudos mate,
>> that's bloody marvellous!
Cezary, thanks for the diverting solution. I've been looking into
solving sudokus along the same lines. A function GROUP_SPLIT as an
inverse of GROUP_CONCAT would be handy here. Below is the query as it
looks when there was such a function, or actually a (function like)
virtual table with column elem. It is exactly half the size.
Nevertheless the fun is to achieve the goal purely in SQL.
Thanks, E Pasma.
CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 + 10)
% 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT (random()
% 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1 FROM cte
WHERE n < 100) SELECT x, y FROM cte;
WITH
params(nx, ny) AS (SELECT 6, 8),
main(elem, rest) AS (
SELECT NULL, (
WITH
state(clock, points, xaxis, yaxis, nxmin, nxmax, nymin,
nymax) AS (
SELECT
0,
(SELECT GROUP_CONCAT(x || ' ' || y) FROM points),
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM
points GROUP BY x)), (SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT
y, COUNT(y) n FROM points GROUP BY y)), (SELECT MIN(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MAX(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MIN(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y)), (SELECT MAX(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y))
UNION ALL
SELECT
(clock + 1) % 3,
CASE clock WHEN 0 THEN
(SELECT GROUP_CONCAT(x || ' ' || y) FROM (
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM GROUP_SPLIT (state.points)
)
WHERE (x NOT IN (
SELECT x FROM (
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.xaxis)
) WHERE n < (SELECT nx FROM params)
)) AND (y NOT IN (
SELECT y FROM (
SELECT
CAST(elem AS INTEGER) y,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.yaxis)
) WHERE n < (SELECT ny FROM params)
))) ELSE points END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM (
SELECT
CAST(elem AS INTEGER) x
FROM GROUP_SPLIT (state.points)
) GROUP BY x)) ELSE xaxis END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y, COUNT(y) n FROM (
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM GROUP_SPLIT (state.points)
) GROUP BY y)) ELSE yaxis END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.xaxis)
)) ELSE nxmin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.xaxis)
)) ELSE nxmax END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.yaxis)
)) ELSE nymin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM GROUP_SPLIT (state.yaxis)
)) ELSE nymax END
FROM state
WHERE (SELECT nx FROM params) BETWEEN nxmin + 1 AND nxmax OR
(SELECT ny FROM params) BETWEEN nymin + 1 AND nymax
) SELECT points FROM state WHERE nxmin >= (SELECT nx FROM params) AND
nymin >= (SELECT ny FROM params)
)
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0) - 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM main
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM main WHERE elem IS NOT NULL
;
_______________________________________________
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
