改写ITPUB newkid的求解数独DuckDB SQL为Clickhouse格式-优快云博客

主要包括
1.将CTE表d改写成非递归形式，其实d单独执行是可以的，不知何故，一个SQL中有两个递归时，非要说它不是递归的
2.将整除符号//改为Floor(除法)，//在Clickhouse中表示注释
3.改写左移位运算符<<为bitShiftLeft，并强制转换参数为Int128和Int。
4.有的子查询表没有别名，用set joined_subquery_requires_alias = 0;避免报错，加上别名更保险
5.按位与&符号改为bitAnd函数

WITH recursive b as(
--select '53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79' b),
--select '..1....68..85...1..9....4..8..........36......7..9.2...5...7.......457.....1...3.' b),
--select '8..........36......7..9.2...5...7.......457.....1...3...1....68..85...1..9....4..' b),
select '9......4..5..2........1......69..1........5.24..7......1....3.....6...9....4.....' b),
--select '..69..1........5.24..7......1....3.....6...9....4.....9......4..5..2........1....' b),
--select '4..7.......69..1........5.2.1....3.....6...9....4.....9......4..5..2........1....' b),

d(z, lp) AS (
  --  select'1', 1
  --  UNION ALL SELECT
  --  CAST(lp+1 AS TEXT), lp+1 FROM d WHERE lp<81
-- UNION query d (z, lp) AS (SELECT '1', 1 UNION ALL SELECT CAST(lp + 1, 'TEXT'), lp + 1 FROM d WHERE lp < 81) is not recursive: While executing RecursiveCTESource. (LOGICAL_ERROR)

select number::text ,number from numbers(1,81)
  ),
grid AS (
SELECT lp                                                   AS pos
      ,Floor((lp-1)/9)::Int                                      AS r  
      ,(lp-1)%9  ::Int                                        AS c  
      ,Floor((lp-1)/27)*3 ::Int+ Floor((lp-1)%9/3)::Int AS g  
  FROM d
)
,all_pos AS (  
SELECT pos,n
      ,bitShiftLeft(1::Int128,(grid.r*9+n-1)::Int) AS r
      ,bitShiftLeft(1::Int128,(grid.c*9+n-1)::Int) AS c
      ,bitShiftLeft(1::Int128,(grid.g*9+n-1)::Int) AS g
  FROM grid,(SELECT lp n FROM d where lp<=9)as m
)
,t(s,rs,cs,gs,next_pos) AS (
SELECT CAST(ANY_VALUE(b) AS text)
       ,SUM(all_pos.r) rs   ---------- 哪些位置已经被占用
       ,SUM(all_pos.c) cs
       ,SUM(all_pos.g) gs
       ,INSTR(ANY_VALUE(b),'.')  
   FROM all_pos,b
  WHERE SUBSTR(b,all_pos.pos,1)=cast(all_pos.n as text)
  UNION ALL
  SELECT SUBSTR(t.s,1,t.next_pos-1)||a.n||SUBSTR(t.s,t.next_pos+1)
        ,t.rs+a.r
        ,t.cs+a.c
        ,t.gs+a.g
        ,case INSTR(SUBSTR(t.s,t.next_pos+1),'.') when 0 then 0 else INSTR(SUBSTR(t.s,t.next_pos+1),'.')+t.next_pos end
    FROM t
        ,all_pos a
   WHERE t.next_pos = a.pos
         AND bitAnd(t.rs,a.r)=0
         AND bitAnd(t.cs,a.c)=0
         AND bitAnd(t.gs,a.g)=0
)
--select count() from t;
--select next_pos,count() from t group by next_pos;
SELECT t.s FROM t WHERE next_pos=0;

执行结果如下

:) \i nkdsudokuck.txt

   ┌─s─────────────────────────────────────────────────────────────────────────────────┐
1. │ 932567841157824963648319725576982134893146572421753689714298356285631497369475218 │
   └───────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 2.562 sec. Processed 4.79 million rows, 699.22 MB (1.87 million rows/s., 272.95 MB/s.)
Peak memory usage: 244.68 MiB.