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
