Snowflake:创建一个默认字段值，每个主键自动递增，每个主键重置

Question

我想创建一个表来容纳以下类型的数据

+--------+-----+----------+
| pk     | ctr | name     |
+--------+-----+----------+
| fish   |  1  | herring  |
| mammal |  1  | dog      |
| mammal |  2  | cat      |
| mammal |  3  | whale    |
| bird   |  1  | penguin  |
| bird   |  2  | ostrich  |
+--------+----_+----------+

PK是主键字符串(100(不为空ctr是我想为每个pk行自动递增1的字段

我试过以下

create or replace  table schema.animals (
pk string(100) not null primary key,
ctr integer not null default  ( select NVL(max(ctr),0) + 1 from schema.animals )
name string (1000) not null);

这产生了以下错误

SQL编译错误：错误行6，位置52聚合函数不允许作为默认值规范的一部分条款

所以我会像一样使用自动递增/身份属性

AUTOINCREMENT | IDENTITY [ ( start_num , step_num ) | START num INCREMENT num ]

但它似乎无法支持每个唯一pk 的重置

寻找任何关于如何解决这个问题的建议，感谢提前提供的任何帮助

Answer 1

不能使用IDENTITY方法执行此操作。建议的解决方案是使用INSTEAD OF触发器来计算INSERTED表的每一行上的ctr值。例如

CREATE TABLE dbo.animals ( 
pk nvarchar(100) NOT NULL, 
ctr integer NOT NULL,
name nvarchar(1000) NOT NULL,
CONSTRAINT PK_animals PRIMARY KEY (pk, ctr)
)
GO
CREATE TRIGGER dbo.animals_before_insert ON dbo.animals INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO animals (pk, ctr, name)
SELECT
i.pk,
(ROW_NUMBER() OVER (PARTITION BY i.pk ORDER BY i.name) + ISNULL(a.max_ctr, 0)) AS ctr,
i.name
FROM inserted i
LEFT JOIN (SELECT pk, MAX(ctr) AS max_ctr FROM dbo.animals GROUP BY pk) a
ON i.pk = a.pk;
END
GO
INSERT INTO dbo.animals (pk, name) VALUES 
('fish'   , 'herring'),
('mammal' , 'dog'),
('mammal' , 'cat'), 
('mammal' , 'whale'), 
('bird'   , 'pengui'),
('bird'   , 'ostrich');
SELECT * FROM dbo.animals;

结果

pk      ctr   name
------- ----- ---------
bird    1     ostrich
bird    2     pengui
fish    1     herring
mammal  1     cat
mammal  2     dog
mammal  3     whale

另一种方法是使用标量用户定义函数作为DEFAULT值，但速度较慢：触发器在所有行上触发一次，而函数在每行上调用。

Answer 2

我不知道为什么要有一个名为pk的列，而不是主键。你不能(轻易(做你想做的事。我建议这样做：

create or replace table schema.animals (
animal_id int identity primary key,
name string(100) not null primary key,
);
create view schema.v_animals as
select a.*, row_number() over (partition by name order by animal_id) as ctr
from schema.animals a;

也就是说，在需要使用ctr时计算它，而不是将它存储在表中。