主页 / dba / 问题 / 112173
Accepted
Erik
Asked: 2015-08-26 12:57:48 +0800 CST

基于时间戳的窗口偏移量

  • 772

我正在编写一个查询，该查询将用于对社交提要的结果进行分页。这个概念是移动应用程序将请求 N 个项目，并提供我在@CutoffTime下面调用的开始日期时间。截止时间的目的是确定寻呼窗口何时开始。我们使用时间戳而不是行偏移量的原因是，即使添加了较新的社交内容，时间戳也能让我们在获取较旧的帖子时从一致的位置进行分页。

由于社交提要项目可以来自您自己或您的朋友，因此我使用UNION来合并这两个组的结果。最初我尝试了TheQuery_CTE没有的逻辑UNION，它很慢。

这就是我所做的（包括相关的表模式）：

    CREATE TABLE [Content].[Photo]
(
    [PhotoId] INT NOT NULL PRIMARY KEY IDENTITY (1, 1), 
    [Key] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    [FullResolutionUrl] NVARCHAR(255) NOT NULL, 
    [Description] NVARCHAR(255) NULL, 
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
);

CREATE TABLE [Content].[UserPhotoAssociation]
(
    [PhotoId] INT NOT NULL, 
    [UserId] INT NOT NULL, 
    [ShowInSocialFeed] BIT NOT NULL DEFAULT 0,

    CONSTRAINT [PK_UserPhotos] PRIMARY KEY ([PhotoId], [UserId]), 
    CONSTRAINT [FK_UserPhotos_User] FOREIGN KEY ([UserId]) 
        REFERENCES [User].[User]([UserId]), 
    CONSTRAINT [FK_UserPhotos_Photo] FOREIGN KEY ([PhotoId]) 
        REFERENCES [Content].[Photo]([PhotoId])
);

CREATE TABLE [Content].[FlaggedPhoto]
(
    [FlaggedPhotoId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [PhotoId] INT NOT NULL,
    [FlaggedBy] INT NOT NULL,
    [FlaggedOn] DATETIME2(0) NOT NULL DEFAULT SYSDATETIME(),
    [FlaggedStatus] INT NOT NULL DEFAULT 1,
    [ReviewedBy] INT NULL,
    [ReviewedAt] DATETIME2(0) NULL

    CONSTRAINT [FK_Photos_PhotoId_to_FlaggedPhotos_PhotoId] FOREIGN KEY ([PhotoId]) 
        REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_FlaggedPhotoStatus_FlaggedPhotoStatusId_to_FlaggedPhotos_FlaggedStatus] FOREIGN KEY ([FlaggedStatus]) 
        REFERENCES [Content].[FlaggedContentStatus]([FlaggedContentStatusId]),
    CONSTRAINT [FK_User_UserId_to_FlaggedPhotos_FlaggedBy] FOREIGN KEY ([FlaggedBy]) 
        REFERENCES [User].[User]([UserId]),
    CONSTRAINT [FK_User_UserId_to_FlaggedPhotos_ReviewedBy] FOREIGN KEY ([ReviewedBy]) 
        REFERENCES [User].[User]([UserId])
);


CREATE TABLE [User].[CurrentConnections]
(
    [MonitoringId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Monitor] INT NOT NULL,
    [Monitored] INT NOT NULL,
    [ShowInSocialFeed] BIT NOT NULL DEFAULT 1,

    CONSTRAINT [FK_Monitoring_Monitor_to_User_UserId] FOREIGN KEY ([Monitor]) 
         REFERENCES [dbo].[User]([UserId]),
    CONSTRAINT [FK_Monitoring_Monitored_to_User_UserId] FOREIGN KEY ([Monitored]) 
         REFERENCES [dbo].[User]([UserId])
);

CREATE TABLE [Content].[PhotoLike]
(
    [PhotoLikeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PhotoId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,

    CONSTRAINT [FK_PhotoLike_PhotoId_to_Photo_PhotoId] FOREIGN KEY ([PhotoId]) 
         REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_PhotoLike_UserId_to_User_UserId] FOREIGN KEY ([UserId]) 
         REFERENCES [User].[User]([UserId])
);

CREATE TABLE [Content].[Comment]
(
    [CommentId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PhotoId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Comment] NVARCHAR(255) NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [CommentOrder] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,

    CONSTRAINT [FK_Comment_PhotoId_to_Photo_PhotoId] FOREIGN KEY ([PhotoId]) 
         REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_Comment_UserId_to_User_UserId] FOREIGN KEY ([UserId]) 
         REFERENCES [User].[User]([UserId])
);

/*

      End table schema

*/



DECLARE @UserId INT,
    @NumberOfItems INT,
    @CutoffTime DATETIME2(2) = NULL -- Stored Proc input params

-- Make the joins and grab the social data we need once since they are used in subsequent queries that aren't shown
DECLARE @SocialFeed TABLE ([Key] UNIQUEIDENTIFIER, [PhotoId] INT
            , [Description] NVARCHAR(255), [FullResolutionUrl] NVARCHAR(255)
            , [Created] DATETIME2(2), [CreatorId] INT, [LikeCount] INT
            , [CommentCount] INT, [UserLiked] BIT);
-- Offset might be different for each group
DECLARE @OffsetMine INT = 0, @OffsetTheirs INT = 0;

IF @CutoffTime IS NOT NULL
    BEGIN
        -- Get the offsets
        ;WITH [GetCounts_CTE] AS
        (
            SELECT
                [P].[PhotoId] -- INT
                , 1 AS [MyPhotos]
            FROM [Content].[Photo] [P]
                INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
                    [UPA].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [UPA].[ShowInSocialFeed] = 1
                LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
                    [FP].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
            WHERE
                [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
                AND
                [UPA].[UserId] = @UserId -- Show the requesting user
                AND
                [P].[Created] >= @CutoffTime -- Get the newer items
            UNION
            SELECT
                [P].[PhotoId] -- INT
                , 0 AS [MyPhotos]
            FROM [Content].[Photo] [P]
                INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
                    [UPA].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [UPA].[ShowInSocialFeed] = 1
                INNER JOIN [User].[CurrentConnections] [M] ON 
                    [M].[Monitored] = [UPA].[UserId] 
                    AND 
                    [M].[Monitor] = @UserId AND [M].[ShowInSocialFeed] = 1 -- this join isn't present above  
                LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
                    [FP].[PhotoId] = [P].[PhotoId] 
                    AND 
                    (
                        [FP].[FlaggedStatus] = 3 
                        OR 
                        ([FP].[FlaggedBy] = @UserId AND [FP].[FlaggedStatus] = 1)
                    ) -- Flagged photos that are confirmed apply to everyone, pending flags apply to the user
            WHERE
                [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
                AND
                [P].[Created] >= @CutoffTime -- Get the newer items
        )
        SELECT
            @OffsetMine = SUM(CASE WHEN [MyPhotos] = 1 THEN 1 ELSE 0 END)
            , @OffsetTheirs = SUM(CASE WHEN [MyPhotos] = 0 THEN 1 ELSE 0 END)
        FROM [GetCounts_CTE]
    END

-- Prevent absence of social data from throwing an error below.
SET @OffsetMine = ISNULL(@OffsetMine, 0); 
SET @OffsetTheirs = ISNULL(@OffsetTheirs, 0);

-- Actually select the data I want
;WITH TheQuery_CTE AS
(
    SELECT
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
        , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount] -- Count distinct used due to common join key
        , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
        , CAST(ISNULL(MAX(CASE WHEN [PL].[UserId] = @UserId THEN 1 END), 0) AS BIT) AS [UserLiked]
    FROM [Content].[Photo] [P]
        INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
            [UPA].[PhotoId] = [P].[PhotoId] 
            AND 
            [UPA].[ShowInSocialFeed] = 1
        LEFT JOIN [Content].[PhotoLike] [PL] ON 
            [PL].[PhotoId] = [P].[PhotoId] 
            AND 
            [PL].[Archived] IS NULL
        LEFT JOIN [Content].[Comment] [C] ON 
            [C].[PhotoId] = [P].[PhotoId] 
            AND 
            [C].[Archived] IS NULL
        LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
            [FP].[PhotoId] = [P].[PhotoId] 
            AND 
            [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
    WHERE
        [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
        AND
        [UPA].[UserId] = @UserId -- Show the requesting user
    GROUP BY
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
    ORDER BY  
        [P].[Created] DESC
        , [P].[Key]  -- Ensure consistent order in case of duplicate timestamps
        OFFSET @OffsetMine ROWS FETCH NEXT @NumberOfItems ROWS ONLY
    UNION
    SELECT
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
        , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount]
        , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
        , CAST(ISNULL(MAX(CASE WHEN [PL].[UserId] = @UserId THEN 1 END), 0) AS BIT) AS [UserLiked]
    FROM [Content].[Photo] [P]
        INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
            [UPA].[PhotoId] = [P].[PhotoId] 
            AND 
            [UPA].[ShowInSocialFeed] = 1
        INNER JOIN [User].[CurrentConnections] [M] ON 
            [M].[Monitored] = [UPA].[UserId] 
            AND 
            [M].[Monitor] = @UserId AND [M].[ShowInSocialFeed] = 1
        LEFT JOIN [Content].[PhotoLike] [PL] ON 
            [PL].[PhotoId] = [P].[PhotoId] 
            AND 
            [PL].[Archived] IS NULL
        LEFT JOIN [Content].[Comment] [C] ON 
            [C].[PhotoId] = [P].[PhotoId] 
            AND 
            [C].[Archived] IS NULL
        LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
            [FP].[PhotoId] = [P].[PhotoId] 
            AND 
            (
                [FP].[FlaggedStatus] = 3 
                OR 
                ([FP].[FlaggedBy] = @UserId AND [FP].[FlaggedStatus] = 1)
            ) -- Flagged photos that are confirmed apply to everyone, pending flags apply to the user
    WHERE
        [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
    GROUP BY
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
    ORDER BY  
        [P].[Created] DESC
        , [P].[Key]  -- Ensure consistant order in case of duplicate timestamps
        OFFSET @OffsetTheirs ROWS FETCH NEXT @NumberOfItems ROWS ONLY
)
INSERT INTO @SocialFeed ([Key], [PhotoId], [Description], [FullResolutionUrl]
            , [Created], [CreatorId], [LikeCount], [CommentCount], [UserLiked])
SELECT TOP (@NumberOfItems)
    [Key]
    , [PhotoId]
    , [Description]
    , [FullResolutionUrl]
    , [Created]
    , [UserId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM [TheQuery_CTE]
ORDER BY  -- Order here so the top works properly
    [Created] DESC
    , [Key]  -- Ensure consistent order in case of duplicate timestamps

-- Output the social feed
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description] AS [PhotoDescription]
    , [P].[FullResolutionUrl]
    , [P].[Created] AS [Posted]
    , [P].[CreatorId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM @Photos [P]

-- Select other data needed to build the object tree in the application layer

我意识到我可以摆脱UNIONin theGetCounts_CTE但我认为它不会真正解决我在下面看到的任何问题。

我看到一些潜在的问题：

  1. 这是很多重复的逻辑，所以我可能让自己的生活更加艰难。
  2. 如果在计算计数和选择数据之间发生插入，我将关闭。我不认为这会经常发生，但它会导致奇怪/难以调试的错误。
  3. 通过上述设置，人们会发现所有更聪明/更有经验的问题。

编写此查询的最佳方式是什么？奖励积分解决方案使我的生活更简单。

编辑：

我不想选择所有数据并让客户端懒惰地显示项目，因为我不想通过强迫人们下载他们永远看不到的项目来滥用人们的数据计划。诚然，在宏伟的计划中，数据可能不会那么大，但一分钱一分货……

编辑 2：

我强烈怀疑这不是最佳解决方案，但这是迄今为止我想到的最好的解决方案。

将我的UNION查询移动到VIEW像Greg建议的那样可以很好地隐藏该逻辑并在我的存储过程中提供更简洁的查询。该视图还抽象掉了联合的丑陋/复杂性，这很好，因为我在我的选择中使用了它两次。这是视图的代码：

CREATE VIEW [Social].[EverFeed]
    AS 
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount] -- Distinct due to common join key
    , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
    , CAST(ISNULL(
        MAX(CASE WHEN [PL].[UserId] = [UPA].[UserId] THEN 1 END), 0) AS BIT) AS [UserLiked]
    , NULL AS [Monitor]
FROM [Content].[Photo] [P]
    INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
        [UPA].[PhotoId] = [P].[PhotoId] 
        AND 
        [UPA].[ShowInSocialFeed] = 1
    LEFT JOIN [Content].[PhotoLike] [PL] ON 
        [PL].[PhotoId] = [P].[PhotoId] 
        AND 
        [PL].[Archived] IS NULL
    LEFT JOIN [Content].[Comment] [C] ON 
        [C].[PhotoId] = [P].[PhotoId] 
        AND 
        [C].[Archived] IS NULL
    LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
        [FP].[PhotoId] = [P].[PhotoId] 
        AND 
        [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
WHERE
    [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
GROUP BY
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
UNION
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount]
    , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
    , CAST(ISNULL(
        MAX(CASE WHEN [PL].[UserId] = [M].[Monitor] THEN 1 END), 0) AS BIT) AS [UserLiked]
    , [M].[Monitor]
FROM [Content].[Photo] [P]
    INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
        [UPA].[PhotoId] = [P].[PhotoId] 
        AND 
        [UPA].[ShowInSocialFeed] = 1
    INNER JOIN [User].[CurrentConnections] [M] ON 
        [M].[Monitored] = [UPA].[UserId] 
        AND 
        [M].[ShowInSocialFeed] = 1
    LEFT JOIN [Content].[PhotoLike] [PL] ON 
        [PL].[PhotoId] = [P].[PhotoId] 
        AND 
        [PL].[Archived] IS NULL
    LEFT JOIN [Content].[Comment] [C] ON 
        [C].[PhotoId] = [P].[PhotoId] 
        AND 
        [C].[Archived] IS NULL
    LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
        [FP].[PhotoId] = [P].[PhotoId] 
        AND 
        (
            [FP].[FlaggedStatus] = 3 
            OR 
            ([FP].[FlaggedBy] = [M].[Monitor] AND [FP].[FlaggedStatus] = 1)
        ) -- Flagged photos that are confirmed (3) apply to everyone
          -- , pending flags (1) apply to the user
WHERE
    [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
GROUP BY
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , [M].[Monitor]

Using that view I shortened my query to the following. Note I'm setting the OFFSET with a subquery.

DECLARE @UserId INT, @NumberOfItems INT, @CutoffTime DATETIME2(2);

SELECT
    [Key]
    , [PhotoId]
    , [Description]
    , [FullResolutionUrl]
    , [Created]
    , [UserId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM  [Social].[EverFeed] [EF]
WHERE
    (
        ([EF].[UserId] = @UserId AND [EF].[Monitor] IS NULL)
        OR 
        [EF].[Monitor] = @UserId
    )
ORDER BY  -- Order here so the top works properly
    [Created] DESC
    , [Key]  -- Ensure consistant order in case of duplicate timestamps
    OFFSET CASE WHEN @CutoffTime IS NULL THEN 0 ELSE        
            (
                SELECT
                    COUNT([PhotoId])
                FROM [Social].[EverFeed] [EF]
                WHERE
                    (
                        ([EF].[UserId] = @UserId AND [EF].[Monitor] IS NULL)
                        OR 
                        [EF].[Monitor] = @UserId
                    )
                    AND
                    [EF].[Created] >= @CutoffTime -- Get the newer items
            ) END 
    ROWS FETCH NEXT @NumberOfItems ROWS ONLY

The view nicely separates the complexity of the UNION from the filtering. I think the subquery in the OFFSET clause will prevent concurrency issues that I was worried about by making the whole query atomic.

One problem I just found while typing this is: in the code above if two photos with the same creation date are on different "pages" then the photos on the subsequent pages will be filtered out. Consider the following data:

PhotoId | Created | ...
------------------------
   1    | 2015-08-26 01:00.00
   2    | 2015-08-26 01:00.00
   3    | 2015-08-26 01:00.00

With a page size of 1 on the initial page, PhotoId 1 will be returned. With the same page size on the second page no results will be returned. I think in order to resolve this I'm going to have to add the Key Guid as a parameter....

sql-server azure-sql-database

2 个回答

  1. Best Answer

    I suggest you go down a different path with this. It looks like a pattern of select top() .... order by should be sufficient.

    select top(@NumberOfItems)
  P.PhotoId,
  P.[Key],
  P.Created
from Content.Photo as P
  inner join Content.UserPhotoAssociation as UPA
    on P.PhotoId = UPA.PhotoId
where
  -- Older than CutoffTime
  P.Created < @CutoffTime and
  (
  -- My photos
  UPA.UserId = @UserId or
  -- Photos by someone monitored by me
  UPA.UserID in (
                select CC.Monitored
                from [User].CurrentConnections as CC
                where CC.Monitor = @UserId
                )
  )
order by P.Created desc, P.[Key]

    To handle cases where Created happens to be equal you need to include the Key value in the cutoff handling. Perhaps a where clause something like this.

    P.Created < @CutoffTime or (P.Created = @CutoffTime and P.Key > @CutoffKey)
    • 5

  2. First, factorize the select statement in a view, the simplest illustration hereafter :

    CREATE VIEW ThePhotos AS (
    SELECT
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Created]
    FROM [Content].[Photo] [P]
        INNER JOIN [Content].[UserPhotoAssociation] [UPA]
        ON [UPA].[PhotoId] = [P].[PhotoId] AND [UPA].[ShowInSocialFeed] = 1
)

    Edit : And if you want to include the UNION and filter on userId, instead include them in an inline table user defined function with a @UserId parameter.

    Second, consider (cautiously) using something like SET TRANSACTION ISOLATION LEVEL SNAPSHOT on your session in order to avoid discrepancies between the COUNT and the SELECT statements. However you could use the highest PhotoId at a given moment to prevent selecting more recent photos.

    Third, you could use a lazy loading mechanism on your front end, loading posts progressively as the users scroll down the page, and avoid the (somehow outdated) paging system.

    • 0

相关问题