需要一些帮助来制作具有 2 个 COUNT_BIG 的索引视图

发布于 2024-07-24 09:41:33 字数 688 浏览 10 评论 0原文

好的，我正在尝试创建一个索引视图，该视图针对一个简单的表，该表存储人们认为对帖子有利/不利的结果。这是对帖子投票的结果。

所以这是我的伪表：-

HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostId INT NOT NULL,
IsHelpful BIT NOT NULL,
UserId INT NOT NULL

所以用户每个帖子只能投一票。它要么是 1（有帮助），要么是 0（无帮助）<-- 不确定是否有更好的方法来处理这个问题，如果有更好的方法的话。

好的。我想做的是获得如下所示的视图。

~~HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,~~

PostId INT NOT NULL,
IsHelpfulCount COUNT_BIG (WHERE IsHelpful = 1)
IsNotHelpfulCount COUNT_BIG (WHERE IsHelpful = 0)

最后，我需要使其可模式绑定，以便我可以在 PK 上添加一个索引，然后在 PostId 上添加一个索引。

我不知道创建视图的sql。有什么建议么？

干杯:)

原文

Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts.

So here's my pseduo fake table :-

HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostId INT NOT NULL,
IsHelpful BIT NOT NULL,
UserId INT NOT NULL

So a user can only have one vote per post. It's either a 1 (helpful) or 0 (unhelpful) <-- not sure of a better way to handle that, if there is a better way.

Ok.
What i'm trying to do is get a view that looks like the following.

~~HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,~~

PostId INT NOT NULL,
IsHelpfulCount COUNT_BIG (WHERE IsHelpful = 1)
IsNotHelpfulCount COUNT_BIG (WHERE IsHelpful = 0)

And finally, i'll need to make it schemabindable so i can add an index on the PK and then an index on the PostId.

I have no idea about the sql to make the view. Any suggestions?

Cheers :)

分享到QQ

分享到微博

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

发布评论

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

葬シ愛 2024-07-31 09:41:33

想法：

您不能在索引视图中使用 COUNT(*)
您不能聚合位字段

还有其他索引视图的限制

CREATE VIEW dbo.Example
WITH SCHEMABINDING
AS
SELECT
    PostId,
    SUM(CAST(IsHelpful AS bigint)) AS IsHelpfulCount,
    SUM(CAST(1-IsHelpful AS bigint)) AS IsNotHelpfulCount,
    COUNT_BIG(*) AS Dummy   --Used to satisfy requirement
FROM
    dbo.bob
GROUP BY
    PostId
GO
CREATE UNIQUE CLUSTERED INDEX IXC_Test ON dbo.Example (PostId)
GO

编辑：
删除了“身份”字段，该字段被意外添加到原始问题/帖子中。

编辑2（GBN）：
我忘记了索引视图中的任何聚合也需要 COUNT_BIG(*)。因此，只需添加一个作为虚拟列即可。我已经测试过这个。

如果视图定义使用
聚合函数，SELECT 列表
还必须包含 COUNT_BIG (*)。

Thoughts:

You can't use COUNT(*) in an indexed view
You can't aggregate bit fields

There are other limitations of indexed views

CREATE VIEW dbo.Example
WITH SCHEMABINDING
AS
SELECT
    PostId,
    SUM(CAST(IsHelpful AS bigint)) AS IsHelpfulCount,
    SUM(CAST(1-IsHelpful AS bigint)) AS IsNotHelpfulCount,
    COUNT_BIG(*) AS Dummy   --Used to satisfy requirement
FROM
    dbo.bob
GROUP BY
    PostId
GO
CREATE UNIQUE CLUSTERED INDEX IXC_Test ON dbo.Example (PostId)
GO

Edit:
Removed the Identity field, which was accidently added to the original question/post.

Edit 2 (gbn):
I forgot that any aggregate in an indexed view also needs a COUNT_BIG(*). So, simply add one as a dummy column. I've tested this.