问题[cte](dba)

我正在使用 PostgreSQL 12.0 并尝试获取特定列和值的最长连续行序列。

该表称为 team2，包含团队的结果，如下所示：

match_id (pk), team_name (varchar), opposition (varchar), match_result (varchar)

我的查询试图找到 match_result 中最长的“Win”序列。总共有 23 场胜利，用肉眼观察，最长的序列应该返回 5。然而，返回的是 23。如何更改查询以仅选择最长的序列？

WITH ConsecutiveSequences AS (
  SELECT
    match_result,
    ROW_NUMBER() OVER () - ROW_NUMBER() OVER (ORDER BY match_id) AS grp
  FROM team2
  WHERE match_result = 'Win'
),
GroupedSequences AS (
  SELECT
    match_result,
    COUNT(*) AS consecutive_count
  FROM ConsecutiveSequences
  GROUP BY match_result, grp
)
SELECT
  COALESCE(MAX(consecutive_count), 0) AS longest_consecutive_sequence
FROM GroupedSequences;

给定这样的表格：https ://dbfiddle.uk/Z8hOhnYG

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

要求是：

• 每个帐户必须始终至少有一个与之关联的个人资料。
• 因此，新帐户必须始终创建新配置文件并将其关系行添加到数据库
• 当整个操作因任何原因失败时，什么都不会创建。

所以为了批处理的目的，我想把它写成一个多插入查询，所以我想出了这个算法：

1. 创建一系列长度与账户数相同的ID
2. 添加帐户
3. 加入该系列的新帐户
4. 添加配置文件
5. 加入该系列的新个人资料
6. 在其系列 ID 上加入帐户和个人资料系列表并将结果插入到关系表中
7. 返回新帐户

我有一个 IF EXISTS 'upsert' 在它自己的存储过程中运行良好。但是当我尝试使用引用 CTE 的相同语句时，它无法识别 CTE。我在相关帖子中看到我不允许使用 CTE 作为子查询。我很好奇为什么会这样，我还能如何做到这一点？

使用 IF EXISTS 的工作存储过程：

ALTER Procedure [dbo].[sproc_receive]
    @StockCode VARCHAR(50), 
    @Qty DECIMAL(18,6)
AS

--source: https://weblogs.sqlteam.com/dang/2007/10/28/conditional-insertupdate-race-condition/

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE StockCode = @StockCode)
    BEGIN
          UPDATE tblReceivedQty
          SET ReceivedQty = ReceivedQty + @Qty
          WHERE StockCode = @StockCode
    END
ELSE
    BEGIN
          INSERT INTO tblReceivedQty (StockCode, ReceivedQty)
          VALUES (@StockCode, @Qty)
    END
COMMIT

RETURN @@ERROR
GO

这是我尝试在另一个以 json 字符串作为输入的存储过程中重新调整 IF EXISTS 的用途。

USE [<databasename>]
GO

/****** Object:  StoredProcedure [dbo].[sproc_PutAway]    Script Date: 6/13/2022 4:14:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[sproc_PutAway]
(@json NVARCHAR(MAX) = '')
AS
BEGIN

-- Create CTE from JSON input
WITH json_received(StockCode, Qty)
AS
(
SELECT StockCode, Qty
    FROM OPENJSON(@json)
    WITH (
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty'
        )
)

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE tblReceivedQty.StockCode = json_received.StockCode)
    BEGIN
        UPDATE tblReceivedQty
        SET tblReceivedQty.ReceivedQty = tblReceivedQty.ReceivedQty - (
            SELECT Sum(Qty)
            FROM json_received
            WHERE tblReceivedQty.StockCode = json_received.StockCode
            GROUP BY json_received.StockCode
            )
    END
ELSE
    BEGIN
        INSERT INTO tblReceivedQty (StockCode, ReceivedQty)
        VALUES (json_received.StockCode, (-1 * json_received.Qty))
    END

COMMIT

RETURN @@ERROR
GO

这在 CTE 之后给了我一个语法错误，并且在对 CTE 的所有引用上都出现了“无法绑定多部分标识符”。

感谢任何提示！

我有一个带有自引用外键的表，我想使用相同的递归更新来更新给定的父级以及父级的所有后代。

当使用带有递归 CTE 的 UPDATE 时，它仅返回 25K 行中的 10 行，优化器使用散列半连接和对正在更新的表进行顺序扫描，而不是更优化的嵌套循环和索引扫描。它的执行时间很慢，约为 5-10 毫秒。

将此表的大小增加到 250K 行将导致使用索引扫描。具有讽刺意味的是，与 25K 表（约 5-10 毫秒秒）相比，执行时间实际上要快得多（约 0.5 到 1.0 毫秒）一个数量级，这正是因为它使用索引而不是顺序扫描。

我在这里的猜测是优化器无法先运行 CTE 然后计划更新，而是需要提前计划，并且错误地假设 CTE 返回的行数比实际多得多。

Postgres 不允许索引优化器提示。在生产中没有将 enable_seqscan 设置为 off，是否有任何解决方法可以让 postgres 使用索引？

设置：

drop table emp;

create table emp (id int primary key, manager_id int, department text);
create index emp_manager_id on emp (manager_id);

insert into emp 
select i id, 
    case when mod(i, 10) = 0 then null else i - 1 end manager_id, 
    null department
from generate_series(0, 25000) as i;

analyze emp;
vacuum emp;

这是更新的 DML。这只会更新 10 行。不管我是使用 IN、EXISTS 还是从递归 CTE 更新，它们都会导致顺序扫描

explain 
with recursive foo as (
    select id, manager_id, department
    from emp
    where id = 1000

    union all

    select emp.id, emp.manager_id, emp.department
    from emp join foo on emp.manager_id = foo.id
)
update emp
set department = 'IT'
where id in (select id from foo);

结果是

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Update on emp  (cost=766.85..939.24 rows=101 width=74)
   CTE foo
     ->  Recursive Union  (cost=0.29..763.57 rows=101 width=40)
           ->  Index Scan using emp_pkey on emp emp_1  (cost=0.29..8.30 rows=1 width=40)
                 Index Cond: (id = 1000)
           ->  Nested Loop  (cost=0.29..75.32 rows=10 width=40)
                 ->  WorkTable Scan on foo foo_1  (cost=0.00..0.20 rows=10 width=4)
                 ->  Index Scan using emp_manager_id on emp emp_2  (cost=0.29..7.50 rows=1 width=40)
                       Index Cond: (manager_id = foo_1.id)
   ->  Hash Semi Join  (cost=3.28..175.67 rows=101 width=74)
         Hash Cond: (emp.id = foo.id)
         ->  Seq Scan on emp  (cost=0.00..145.01 rows=10001 width=14)
         ->  Hash  (cost=2.02..2.02 rows=101 width=32)
               ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32)

解释分析给出相同的结果。为简洁起见，我在这里使用说明。

考虑到 25,000 行中只有 10 行正在更新，这种带有顺序扫描的散列半连接并不是最优的。带有索引扫描的嵌套循环在这里是理想的。

设置 enable_seqscan=off 将时间减少到 ~0.1ms（从 ~5-10ms）

如果我不使用递归 CTE，则使用以下更新generate_series显示 emp_id 索引已正确用于通过嵌套循环执行更新。这是我对递归 CTE 更新的期望。

explain 
update emp 
set department = 'IT' 
where id in (
    select i from generate_series(1000,1009) i
);


                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Update on emp  (cost=0.43..83.59 rows=11 width=74)
   ->  Nested Loop  (cost=0.43..83.59 rows=11 width=74)
         ->  HashAggregate  (cost=0.14..0.25 rows=11 width=32)
               Group Key: i.i
               ->  Function Scan on generate_series i  (cost=0.00..0.11 rows=11 width=32)
         ->  Index Scan using emp_pkey on emp  (cost=0.29..7.58 rows=1 width=14)
               Index Cond: (id = i.i)

如果我将表中的行数从 10K 增加到 250K，则说明计划确实会导致索引的最佳使用。但是，对于 25K 行/seq 扫描，执行需要大约 5-10 毫秒。对于 250K 行，索引扫描大约需要 0.5-0.1 毫秒。

我的猜测是 postgres 无法先运行 CTE，然后计算更新计划。它需要在运行 CTE 之前计算一个计划。因此 postgres 无法知道 CTE 只返回了 10 行，而是必须猜测数字。所以 postgres 猜测 CTE 将返回 1000 行之类的东西，这使得它在表仅包含 25K 时更喜欢顺序扫描。我假设我的 250K 表使用索引扫描的原因是 postgres 继续猜测 CTE 正在返回 1000 行，但在 250K 中，索引扫描更有意义。

Postgres 不允许索引优化器提示。在生产中没有将 enable_seqscan 设置为 off，是否有任何解决方法可以让 postgres 使用索引？

@a_horse_with_no_name 使用的解决方案emp.id = any(array(select id from foo))很棒。它导致以下解释简单，略有不同：

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Update on emp  (cost=44.19..48.93 rows=10 width=46)
   CTE foo
     ->  Recursive Union  (cost=0.00..42.17 rows=101 width=11)
           ->  Seq Scan on emp emp_1  (cost=0.00..3.08 rows=1 width=11)
                 Filter: (id = 0)
           ->  Hash Join  (cost=0.33..3.71 rows=10 width=11)
                 Hash Cond: (emp_2.manager_id = foo.id)
                 ->  Seq Scan on emp emp_2  (cost=0.00..2.66 rows=166 width=11)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4)
                       ->  WorkTable Scan on foo  (cost=0.00..0.20 rows=10 width=4)
   InitPlan 2 (returns $2)
     ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
   ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
         Filter: (id = ANY ($2))

谁能解释这两个部分之间的区别：

原始的 enable_seqscan=off：

   ->  Nested Loop  (cost=2.56..294.11 rows=101 width=74) (actual time=0.091..0.118 rows=10 loops=1)
         ->  HashAggregate  (cost=2.27..3.28 rows=101 width=32) (actual time=0.076..0.080 rows=10 loops=1)
               Group Key: foo.id
               Batches: 1  Memory Usage: 24kB
               ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32) (actual time=0.024..0.068 rows=10 loops=1)
         ->  Index Scan using emp_pkey on emp  (cost=0.29..2.88 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=10)
               Index Cond: (id = foo.id)

使用any(array(...))：

   InitPlan 2 (returns $2)
     ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
   ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
         Filter: (id = ANY ($2))

foo.id首先，在执行 CTE 扫描后，我的原始查询导致递归 cte 的 HashAggregate 。只有在此之后，它才会遍历emp索引。我不明白它为什么这样做。使用any(array(...))，它将跳过这一步，并简单地在 cte 扫描和索引扫描上嵌套循环。

其次，可能也是最重要的，any(array(...))在 this 中使用结果InitPlan 2。我相信这里发生的事情是以any(array(...))某种方式迫使查询规划器将它们作为两个不同的查询来执行。首先它执行 CTE，它只返回 10 行。然后，规划器知道只有 10 行它可以使用索引扫描而不是 seqscan。由于某种原因，我的原始解决方案无法强制查询规划器将这些作为两个不同的查询执行，因此查询规划器事先不知道要返回多少行。

有任何想法吗？

我正在尝试编写一个在 Postgresql 14 中使用递归 CTE 的数据生成查询。

考虑包含函数 getfreq 的模式“sc”。getfreq 将一个 int 作为参数（表示另一个表的外键），并返回一个 int 返回，它表示频率。

现在考虑这个查询：

WITH RECURSIVE rec AS 
(
SELECT 1 as fk FROM generate_series(1, sc.getfreq(1), 1)
UNION ALL
SELECT r.fk + 1 FROM rec AS r WHERE r.fk + 1 <= 10
)
select row_number() OVER () as pk, fk from rec

getfreq 期望从 1 到 10 的 int （因此 r.fk <= 10 退出条件）。它返回一个频率 N。我希望递归 CTE 的每次迭代都创建 N 行。每次迭代的结果将由 UNION ALL 子句组合在一起。最后，我想要一个结果，其中行数等于 getfreq 在 10 次迭代中返回的频率总和。

在上面的示例中，sc.getfreq(1) 将始终返回 5，因此我得到了 50 行的结果集；第一个 5 fk = 1，第二个 5 fk = 2，依此类推。但是，实际上应该使用迭代值调用 sc.getfreq()，因此第二次迭代应该是 sc.getfreq(2) 等等。自然，sc.getfreq(2) 将返回不同的频率而不是 5，因此最终结果不应有 50 行。

我曾尝试在 getfreq 中使用“fk”，如下所示： sc.getfreq(fk); 因为“fk”被 CTE 的递归部分递增（因此在第二次迭代中为 2，在第三次迭代中为 3，依此类推），但列“fk”在 FROM 的上下文中不存在，大概是因为“SELECT”部分还没有运行。

递归 CTE 适合解决这个问题吗？我可以通过一些调整来实现我想要的吗？

示例输出，其中 getfreq(1) 返回 5，getfreq(2) 返回 2，getfreq(3) 返回 1。

......等等（这是3次迭代的不完整示例）。

好的，首先让我说我从以下方面得到了这个问题：

• 在 CTE 中寻求 Tsql 错误连接解释 [关闭]

我试图帮助找出问题，但在尝试一次调试代码时被难住了。我知道问题是由于嵌套的 CTE（因为在调试过程中，如果您将每个步骤（也称为 cteX）转储到临时表中，则会获得正确的结果）但不知道它们是如何“在幕后”工作的，我无法在外面以合理的方式解释它“它不工作哟。” 我怀疑这与编译器如何在运行时同时评估它们有关，但没有更多上下文我不能肯定地说。

我的问题只是试图了解它们如何在幕后工作以及它与这种情况有何关系。现在我参与其中，我只想了解这个问题，以便将来可以与它交谈并同时学习一些有趣的东西。

在这里回答的人也可以在 SO 上发帖并在那里回答。

代码设置：

declare @t1 TABLE (ID varchar(max),Action varchar(max), DateTime datetime );
INSERT INTO @t1
Select *
from
(
VALUES 
('w2337','Open','2020-11-06 12:28:10.000'),
('w2337','Hold','2021-06-14 14:50:59.000'),
('w2337','Open','2021-06-14 14:51:26.000'),
('w2337','Hold','2021-06-15 14:50:59.000'),
('w2337','Open','2021-06-17 14:51:26.000'),
('w2337','Open','2021-06-18 14:51:26.000')

) t (ID, Action, DateTime);

with cte1 as (
select [ID],[Action],[DateTime]
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]
,DENSE_RANK () OVER (ORDER BY ID) as [Index by ID]
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DateTime]) as [Index by DateTimeID]
,CASE when [Action]='Hold' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesHeld]
FROM @t1 
)
,cte2 as (
select *, MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD] from cte1 
)
,cte3 as(
select *, CASE when [Action]='Open' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesOpened]
from cte2 
where FD is not null
)
select 
    a.*, ' ' as thing, b.DateTime  -- b.*   alternating between the direct column versus all is the issue
from cte3 a 
LEFT OUTER JOIN cte3 b
ON a.ID=b.ID and a.TimesHeld=b.TimesOpened 
where a.TimesHeld is not null and b.TimesOpened is not null

细节：

在编译下面的查询时，在最后的 LEFT OUTER JOIN 中，如果您选择 b.*，您会得到正确的结果。但是，如果您只选择一列（例如日期时间），则结果不正确。

正确的：

不正确：

这是用于测试的表格和数据：

CREATE TABLE if not exists cars (
    id serial PRIMARY KEY, 
    parent_code INT,
    descr CHARACTER VARYING(50) NOT NULL
);

-- Level 1
INSERT INTO cars(parent_code, descr) VALUES (null, 'Volkswagen');
INSERT INTO cars(parent_code, descr) VALUES (null, 'Tesla');

-- Level 2
INSERT INTO cars(parent_code, descr) VALUES (1, 'Polo');
INSERT INTO cars(parent_code, descr) VALUES (1, 'Golf');

INSERT INTO cars(parent_code, descr) VALUES (2, '3');
INSERT INTO cars(parent_code, descr) VALUES (2, 'S');
INSERT INTO cars(parent_code, descr) VALUES (2, 'X');

-- Level 3
INSERT INTO cars(parent_code, descr) VALUES (3, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (3, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (4, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (4, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (5, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (6, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (7, 'electric');

这是带有递归 CTE 的视图（我尝试过）：

WITH RECURSIVE tree_cars AS (
  select descr
  from cars
  union all

  select concat('  -> ', descr)
  from cars
)
--table tree_cars;
select * from tree_cars;

但输出不是我所期望的：

Volkswagen
Tesla
Polo
Golf
3
S
X
diesel
gasoline
diesel
gasoline
electric
electric
electric
  -> Volkswagen
  -> Tesla
  -> Polo
  -> Golf
  -> 3
  -> S
  -> X
  -> diesel
  -> gasoline
  -> diesel
  -> gasoline
  -> electric
  -> electric
  -> electric

因为我想要类似的东西：

Tesla
  -> 3
      -> electric
  -> S
      -> electric
  -> X
      -> electric
Volkswagen
  -> Golf
      -> diesel
      -> gasoline
  -> Polo
      -> diesel
      -> gasoline

我有一张这样的桌子：

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

我需要生成这种输出：

Radiology
   -->Normal Radiology
   -->Resonance
      -->Cerebral Resonance with contrast
Lab Test
    --> Blood Test
    --> Calcium

我正在研究 PostgreSQL。我一直在尝试使用递归 CTE，但无法生成我喜欢的内容：

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list
    FROM    orders
    WHERE   parent_id is null
      UNION
    SELECT  c.id,
            CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    FROM orders c
    INNER JOIN hierarchy c2 ON c.parent_id = c2.id )

SELECT  id, parent_list
FROM     hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

该递归 CTE 会产生以下不受欢迎的输出：

Radiology
Radiology--> Normal Radiology
Radiology--> Resonance
Radiology--> Resonance --> Cerebral Resonance with contrast
Lab Test
Lab Test --> Blood Test
Lab Test --> Calcium

我该怎么做？

我有一个这样的表来保存 90 个服务的目录：

-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

我需要更改表结构，以使三个级别（服务、子服务和描述）在同一列中具有自己的 id 并指向具有级别编号的新列。即类似这样的东西（请注意，id是我编造的）：

-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

我正在使用 Postgresql 12。我已经创建了新列 parent_id 并且我正在尝试执行以下操作：

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

任何人都可以大致了解如何转换表格的结构吗？

我正在尝试进行以下工作：

WITH results(n, string ) AS (
            SELECT 1,'lol'
            UNION ALL
            SELECT n+1, string + ' lol'
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

但 SQL 似乎无法识别第二列中的字符串连接并返回错误：

递归查询“结果”的“字符串”列中的锚点和递归部分之间的类型不匹配。

我想要的输出将类似于

1，哈哈

2、哈哈哈哈哈

3、哈哈哈哈哈

等等