将递归CTE从后SQL转换为SQL Server

发布于 2025-02-09 14:58:49 字数 972 浏览 1 评论 0原文

我很难适应一些递归CTE代码从PostgreSQL到SQL Server。

这是我的桌子：

CREATE TABLE flight (
  src CHAR(3)
, dest CHAR(3)
, stt DATETIME
, endt DATETIME);

INSERT INTO flight VALUES 
('MSP', 'SLC', '2022-10-02 11:45:00', '2022-10-02 14:10:00'),
('SLC', 'LAX', '2022-10-02 15:20:00', '2022-10-02 17:45:00'),
('MSP', 'LAX', '2022-10-02 12:15:00', '2022-10-02 15:05:00')

我要适应的东西：

WITH RECURSIVE flight_paths (src, flights, path, dest, stt, endt) AS (
SELECT
  src
, ARRAY[src || '-' || dest]
, ARRAY[src]
, dest 
, stt
, endt
FROM flight
UNION ALL
SELECT
  fp.src
, fp.flights || (f.src || '-' || f.dest)
, fp.path || f.src
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest 
WHERE NOT f.src = ANY(fp.path) 
  AND NOT 'LAX' = ANY(fp.path) 
  AND f.stt > fp.endt
) 
SELECT flights, stt, endt, path[2:] stopovers
FROM flight_paths
WHERE src = 'MSP' AND dest = 'LAX'

我一直在适应阵列使用的问题。任何指针都会有所帮助！

原文

I am having trouble adapting some recursive CTE code from PostgreSQL to SQL Server.

Here is my table:

CREATE TABLE flight (
  src CHAR(3)
, dest CHAR(3)
, stt DATETIME
, endt DATETIME);

INSERT INTO flight VALUES 
('MSP', 'SLC', '2022-10-02 11:45:00', '2022-10-02 14:10:00'),
('SLC', 'LAX', '2022-10-02 15:20:00', '2022-10-02 17:45:00'),
('MSP', 'LAX', '2022-10-02 12:15:00', '2022-10-02 15:05:00')

and what I am trying to adapt:

WITH RECURSIVE flight_paths (src, flights, path, dest, stt, endt) AS (
SELECT
  src
, ARRAY[src || '-' || dest]
, ARRAY[src]
, dest 
, stt
, endt
FROM flight
UNION ALL
SELECT
  fp.src
, fp.flights || (f.src || '-' || f.dest)
, fp.path || f.src
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest 
WHERE NOT f.src = ANY(fp.path) 
  AND NOT 'LAX' = ANY(fp.path) 
  AND f.stt > fp.endt
) 
SELECT flights, stt, endt, path[2:] stopovers
FROM flight_paths
WHERE src = 'MSP' AND dest = 'LAX'

I have been having issues adapting the use of ARRAYs. Any pointers would be really helpful!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风和你 2025-02-16 14:58:49

更快的更快是：

WITH flight_paths (src, flights, path, dest, stt, endt) AS (SELECT
  src
, CAST('{"' + src + '-' + dest +'"}' AS nvarchar(max))
, CAST('{"' + src + '"}' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight
WHERE src = 'MSP'

UNION ALL

SELECT
  fp.src
, fp.flights + '{"' +f.src+'"}' + '{"' +f.dest+'"}'
, fp.path + '{"' +f.src+'"}'
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
  AND fp.dest <> 'LAX'
WHERE fp.path NOT LIKE '%' + '{"' +f.src+'"}' + '%'
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE dest = 'LAX';

用100003行测试，结果是：

查理节查询1：5'05“
charlieface查询2：4'58”
我自己的一个：0'01”

在Windows 2019 evelptper / PC上运行256 GO RAM和24个核心X 2 Intel Xeon CPU E5-1650 V4

结论：JSON或XML的递归查询非常昂贵...

Much more quicker will be :

WITH flight_paths (src, flights, path, dest, stt, endt) AS (SELECT
  src
, CAST('{"' + src + '-' + dest +'"}' AS nvarchar(max))
, CAST('{"' + src + '"}' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight
WHERE src = 'MSP'

UNION ALL

SELECT
  fp.src
, fp.flights + '{"' +f.src+'"}' + '{"' +f.dest+'"}'
, fp.path + '{"' +f.src+'"}'
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
  AND fp.dest <> 'LAX'
WHERE fp.path NOT LIKE '%' + '{"' +f.src+'"}' + '%'
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE dest = 'LAX';

Tested with 100003 rows, the results are :

Charlieface Query 1 : 5' 05"
Charlieface Query 2 : 4' 58"
My own one : 0' 01"

Running on a Windows 2019 Developper / PC with 256 Go RAM and 24 cores x 2 intel Xeon CPU E5-1650 v4

Conclusion : JSON or XML are very costly in recursive queries...

回复收藏 0 原文

酒儿 2025-02-16 14:58:49

不幸的是，SQL Server不支持数组。但是您可以使用JSON数组。

您的原始代码的一个非常直接的副本会导致

WITH flight_paths (src, flights, path, dest, stt, endt) AS (

SELECT
  src
, CAST('["' + src + '-' + dest +'"]' AS nvarchar(max))
, CAST('["' + src + '"]' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight

UNION ALL

SELECT
  fp.src
, JSON_MODIFY(fp.flights, 'append 
逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：
WITH flight_paths (src, flights, path, dest, stt, endt) AS (

SELECT
  src
, CAST('["' + src + '-' + dest +'"]' AS nvarchar(max))
, CAST('["' + src + '"]' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight
WHERE src = 'MSP'

UNION ALL

SELECT
  fp.src
, JSON_MODIFY(fp.flights, 'append 
   db＆lt;＆gt;＆gt; fiddle  
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

   db＆lt;＆gt;＆gt; fiddle  
, f.src)
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
WHERE EXISTS (SELECT 1
    FROM OPENJSON(fp.path) arr
    WHERE arr.value NOT IN(f.src, 'LAX')
  )
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE src = 'MSP'
  AND dest = 'LAX'

逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

   db＆lt;＆gt;＆gt; fiddle  
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
   db＆lt;＆gt;＆gt; fiddle  
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

   db＆lt;＆gt;＆gt; fiddle  
, f.src)
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
WHERE EXISTS (SELECT 1
    FROM OPENJSON(fp.path) arr
    WHERE arr.value NOT IN(f.src, 'LAX')
  )
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE src = 'MSP'
  AND dest = 'LAX'

逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

db＆lt;＆gt;＆gt; fiddle

, f.src) , f.dest , fp.stt , f.endt FROM flight f JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src AND fp.dest <> 'LAX' WHERE EXISTS (SELECT 1 FROM OPENJSON(fp.path) arr WHERE arr.value <> f.src ) AND f.stt > fp.endt ) SELECT flights, stt, endt, path stopovers FROM flight_paths WHERE dest = 'LAX';

db＆lt;＆gt;＆gt; fiddle

, f.src + '-' + f.dest) , JSON_MODIFY(fp.path, 'append

逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

db＆lt;＆gt;＆gt; fiddle

, f.src) , f.dest , fp.stt , f.endt FROM flight f JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src WHERE EXISTS (SELECT 1 FROM OPENJSON(fp.path) arr WHERE arr.value NOT IN(f.src, 'LAX') ) AND f.stt > fp.endt ) SELECT flights, stt, endt, path stopovers FROM flight_paths WHERE src = 'MSP' AND dest = 'LAX'

逻辑上的一些微小变化：

很难从JSON数组中删除元素。因此，路径仅构建中间节点，并进行单独的检查以排除起点。

我注意到，明确检查目的地是否尚未达到目的地，并且将起始点推入CTE的锚点：

db＆lt;＆gt;＆gt; fiddle

Unfortunately, SQL Server does not support arrays. But you could use a JSON array instead.

A pretty straight-up copy of your original code results in this

WITH flight_paths (src, flights, path, dest, stt, endt) AS (

SELECT
  src
, CAST('["' + src + '-' + dest +'"]' AS nvarchar(max))
, CAST('["' + src + '"]' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight

UNION ALL

SELECT
  fp.src
, JSON_MODIFY(fp.flights, 'append 
Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:
WITH flight_paths (src, flights, path, dest, stt, endt) AS (

SELECT
  src
, CAST('["' + src + '-' + dest +'"]' AS nvarchar(max))
, CAST('["' + src + '"]' AS nvarchar(max))
, dest 
, stt
, endt
FROM flight
WHERE src = 'MSP'

UNION ALL

SELECT
  fp.src
, JSON_MODIFY(fp.flights, 'append 
db<>fiddle
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle
, f.src)
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
WHERE EXISTS (SELECT 1
    FROM OPENJSON(fp.path) arr
    WHERE arr.value NOT IN(f.src, 'LAX')
  )
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE src = 'MSP'
  AND dest = 'LAX'

Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
db<>fiddle
, f.src + '-' + f.dest)
, JSON_MODIFY(fp.path, 'append 
Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle
, f.src)
, f.dest
, fp.stt
, f.endt
FROM flight f
JOIN flight_paths fp ON f.src = fp.dest AND f.dest <> fp.src
WHERE EXISTS (SELECT 1
    FROM OPENJSON(fp.path) arr
    WHERE arr.value NOT IN(f.src, 'LAX')
  )
  AND f.stt > fp.endt

) 
SELECT flights, stt, endt, path stopovers
FROM flight_paths
WHERE src = 'MSP'
  AND dest = 'LAX'

Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle

, f.src + '-' + f.dest) , JSON_MODIFY(fp.path, 'append

Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle

Some minor changes in the logic were necessary:

It's difficult to delete an element from a JSON array. So instead the path only builds the intermediate nodes, and a separate check is made to exclude the start point.

I note that it's probably faster to check explicitly that the destination has not been reached, and also to push the start point into the anchor part of the CTE:

db<>fiddle

回复收藏 0 原文

~没有更多了~