On 2018/05/04 1:54 AM, Cezary H. Noweta wrote:
Hello,
At the beginning I would like to agree with that the problem is
iterative rather then recursive one. However....
LOL, that might be the hackiest query I ever seen, but kudos mate,
that's bloody marvellous!
I corrected the table to not have duplicate points, and then added some
bits to visualize the points, which makes it easier to see what is
happening - also just for fun :)
(Might have to run it a couple times to get a point-set that actually
passes)
-- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed
version 2.0.2.4.
--
================================================================================================
DROP TABLE IF EXISTS points;
CREATE TABLE points(
x INT, y INT, PRIMARY KEY (x,y)
);
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
)
INSERT INTO points(x,y)
SELECT DISTINCT x, y FROM cte;
SELECT 'Y_'||y AS 'Points',
MAX(CASE X WHEN 1 THEN ' * ' ELSE ' ' END) AS X_1,
MAX(CASE X WHEN 2 THEN ' * ' ELSE ' ' END) AS X_2,
MAX(CASE X WHEN 3 THEN ' * ' ELSE ' ' END) AS X_3,
MAX(CASE X WHEN 4 THEN ' * ' ELSE ' ' END) AS X_4,
MAX(CASE X WHEN 5 THEN ' * ' ELSE ' ' END) AS X_5,
MAX(CASE X WHEN 6 THEN ' * ' ELSE ' ' END) AS X_6,
MAX(CASE X WHEN 7 THEN ' * ' ELSE ' ' END) AS X_7,
MAX(CASE X WHEN 8 THEN ' * ' ELSE ' ' END) AS X_8,
MAX(CASE X WHEN 9 THEN ' * ' ELSE ' ' END) AS X_9,
MAX(CASE X WHEN 10 THEN ' * ' ELSE ' ' END) AS X_10
FROM points
GROUP BY y
;
-- Points | X_1 | X_2 | X_3 | X_4 | X_5 | X_6 | X_7 | X_8 | X_9 | X_10
-- ------ | --- | --- | --- | --- | --- | --- | --- | --- | --- | ----
-- Y_1 | * | | | * | * | | | | | *
-- Y_2 | * | * | | | | * | * | * | |
-- Y_3 | * | | * | * | * | * | * | | * |
-- Y_4 | * | * | | * | | * | * | * | |
-- Y_5 | * | | * | | * | * | * | * | * |
-- Y_6 | | | * | * | * | * | | * | * | *
-- Y_7 | * | * | * | * | * | | * | * | | *
-- Y_8 | * | * | | * | | * | * | | * |
-- Y_9 | | * | * | * | * | * | * | * | * | *
-- Y_10 | * | * | | * | * | * | | * | * | *
WITH
params(nx, ny) AS (SELECT 5, 6),
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 (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM cte WHERE elem IS NOT NULL
)
WHERE (x NOT IN (
SELECT x FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','),
0) - 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
) WHERE n < (SELECT nx FROM params)
)) AND (y NOT IN (
SELECT y FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','),
0) - 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) y,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
) 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 (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x
FROM cte WHERE elem IS NOT NULL
) GROUP BY x)) ELSE xaxis END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y,
COUNT(y) n FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM cte WHERE elem IS NOT NULL
) GROUP BY y)) ELSE yaxis END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nxmin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nxmax END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nymin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) 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
), res(x,y) AS (
SELECT CAST(elem AS INTEGER),
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER)
FROM main WHERE elem IS NOT NULL
), grid(gy) AS (
SELECT 1
UNION ALL
SELECT gy+1 FROM grid WHERE gy<10
)
SELECT 'Y_'||gy AS 'Points',
MAX(CASE X WHEN 1 THEN ' * ' ELSE ' ' END) AS X_1,
MAX(CASE X WHEN 2 THEN ' * ' ELSE ' ' END) AS X_2,
MAX(CASE X WHEN 3 THEN ' * ' ELSE ' ' END) AS X_3,
MAX(CASE X WHEN 4 THEN ' * ' ELSE ' ' END) AS X_4,
MAX(CASE X WHEN 5 THEN ' * ' ELSE ' ' END) AS X_5,
MAX(CASE X WHEN 6 THEN ' * ' ELSE ' ' END) AS X_6,
MAX(CASE X WHEN 7 THEN ' * ' ELSE ' ' END) AS X_7,
MAX(CASE X WHEN 8 THEN ' * ' ELSE ' ' END) AS X_8,
MAX(CASE X WHEN 9 THEN ' * ' ELSE ' ' END) AS X_9,
MAX(CASE X WHEN 10 THEN ' * ' ELSE ' ' END) AS X_10
FROM grid
LEFT JOIN RES ON RES.y = grid.gy
GROUP BY gy
;
-- Points | X_1 | X_2 | X_3 | X_4 | X_5 | X_6 | X_7 | X_8 | X_9 | X_10
-- ------ | --- | --- | --- | --- | --- | --- | --- | --- | --- | ----
-- Y_1 | | | | | | | | | |
-- Y_2 | | | | | | | | | |
-- Y_3 | * | | * | * | * | * | * | | * |
-- Y_4 | * | * | | * | | * | * | * | |
-- Y_5 | * | | * | | * | * | * | * | * |
-- Y_6 | | | * | * | * | * | | * | * |
-- Y_7 | * | * | * | * | * | | * | * | |
-- Y_8 | * | * | | * | | * | * | | * |
-- Y_9 | | * | * | * | * | * | * | * | * |
-- Y_10 | * | * | | * | * | * | | * | * |
WITH
params(nx, ny) AS (SELECT 5, 6),
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 (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM cte WHERE elem IS NOT NULL
)
WHERE (x NOT IN (
SELECT x FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','),
0) - 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
) WHERE n < (SELECT nx FROM params)
)) AND (y NOT IN (
SELECT y FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','),
0) - 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) y,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
) 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 (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(elem AS INTEGER) x
FROM cte WHERE elem IS NOT NULL
) GROUP BY x)) ELSE xaxis END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y,
COUNT(y) n FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.points
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
FROM cte WHERE elem IS NOT NULL
) GROUP BY y)) ELSE yaxis END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nxmin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.xaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nxmax END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) ELSE nymin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
WITH
cte(elem, rest) AS (
SELECT NULL, state.yaxis
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0)
- 1, LENGTH(rest))),
SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM cte
WHERE rest IS NOT NULL
)
SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
FROM cte WHERE elem IS NOT NULL
)) 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;
-- x | y
-- --- | ---
-- 5 | 6
-- 3 | 9
-- 1 | 10
-- 5 | 5
-- 9 | 5
-- 2 | 9
-- 3 | 5
-- 9 | 10
-- 1 | 8
-- 8 | 5
-- 4 | 6
-- 2 | 8
-- 4 | 4
-- 3 | 3
-- 7 | 5
-- 6 | 5
-- 5 | 9
-- 2 | 4
-- 7 | 4
-- 4 | 10
-- 6 | 8
-- 9 | 3
-- 9 | 6
-- 8 | 7
-- 7 | 9
-- 6 | 4
-- 5 | 3
-- 6 | 10
-- 4 | 8
-- 7 | 7
-- 1 | 3
-- 2 | 10
-- 5 | 7
-- 6 | 9
-- 7 | 8
-- 7 | 3
-- 1 | 7
-- 2 | 7
-- 4 | 9
-- 8 | 6
-- 8 | 10
-- 4 | 7
-- 8 | 9
-- 4 | 3
-- 6 | 3
-- 9 | 8
-- 1 | 4
-- 6 | 6
-- 3 | 7
-- 1 | 5
-- 9 | 9
-- 8 | 4
-- 5 | 10
-- 3 | 6
-- 2018-05-04 03:00:41.383 | [Success] Script Success.
-- 2018-05-04 03:00:41.385 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2018-05-04 03:00:41.324] APPLICATION : Script
E:\Documents\SQLiteAutoScript.sql started at 03:00:41.324 on 04 May.
-- [2018-05-04 03:00:41.359] ERROR (284) : automatic index on res(y)
--
================================================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
