如何更改查询以提供过去 15 周的数据，而不是来自 SQL Server 的过去 15 天的数据 [英] How to change the query to give last 15 weeks of data instead of last 15 days from the SQL Server

问题描述

以下查询提供了过去 15 天内每天来自数据库的用户的播放时间.如果没有玩游戏，则加 0.现在我想获取每周播放时间的数据，如果整周没有玩游戏，则为0.所以我希望查询提供过去 15 周的数据.

The following query gives playing time of the users from the database on daily basis for the last 15 days. It adds 0 if no game is played. Now I want to get the data of playing time on weekly basis and 0 if no game is played in the whole week. So I want the query to give the last 15 weeks of data.

这是每日查询.

CREATE PROCEDURE [dbo].[spGetPlayingTimeOfthepeoplesPerDay] @email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinDate DATE
        ,@MaxDate DATE
        ,@LastXDays INT

    SELECT @LastXDays = - 15

    SELECT @MaxDate = peoples.l_update
    FROM peoples
    WHERE peoples.email = @email

    DECLARE @test TABLE (
        quantity VARCHAR(100)
        ,DATE DATE
        ,TimePerDay DECIMAL(5, 2)
        );

    WITH CTE
    AS (
        SELECT peoples.email
            ,peoples.l_update
            ,act.quantity
            ,act.starttime
            ,act.endtime
            ,act.duration AS [Totaltime]
        FROM peoples
        INNER JOIN MPeoples ON peoples.Id = MPeoples.parent_id
        INNER JOIN slines ON MPeoples.id = slines.movesuser_id
        INNER JOIN seg ON slines.id = seg.sline_id
        INNER JOIN act ON seg.id = act.seg_id
        WHERE act.quantity = 'playing'
            AND (peoples.email = @email)
        GROUP BY peoples.email
            ,act.quantity
            ,act.duration
            ,act.starttime
            ,act.endtime
            ,peoples.l_update
        )
    INSERT INTO @test (
        quantity
        ,DATE
        ,TimePerDay
        )
    SELECT quantity
        ,Cast(starttime AS DATE) AS DATE
        ,SUM(datediff(second, starttime, endtime)) / 60.0 AS TimePerDay
    FROM cte WITH (NOLOCK)
    WHERE starttime >= dateadd(day, @LastXDays, l_update)
    GROUP BY quantity
        ,cast(starttime AS DATE)

    SELECT @MaxDate = @MaxDate
        ,@MinDate = dateadd(day, (@LastXDays + 1), @MaxDate);

    WITH AllDates
    AS (
        SELECT @MinDate AS xDate

        UNION ALL

        SELECT Dateadd(Day, 1, xDate)
        FROM AllDates AS ad
        WHERE ad.xDate < @MaxDate
        )
    SELECT 'playing' AS quantity
        ,ad.xDate
        ,Isnull(t.TimePerDay, 0) AS TimePerDay
    FROM AllDates AS ad WITH (NOLOCK)
    LEFT JOIN @test AS t ON ad.xDate = t.DATE
END

推荐答案

将 DATEADD 从每天更改为每周.因此，有两个变化:

Change the DATEADD from day to week. Hence, two changes:

dateadd(week, @LastXDays, l_update)

和

dateadd(week, (@LastXDays + 1), @MaxDate)

在这种情况下，我还会将 @LastXDays 变量重命名为 @LastXWeeks.

In this case, I would also rename the @LastXDays variable to @LastXWeeks.

CREATE PROCEDURE [dbo].[spGetPlayingTimeOfthepeoplesPerDay] @email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinDate DATE
        ,@MaxDate DATE
        ,@LastXDays INT

    SELECT @LastXWeeks = - 15

    SELECT @MaxDate = peoples.l_update
    FROM peoples
    WHERE peoples.email = @email

    DECLARE @test TABLE (
        quantity VARCHAR(100)
        ,DATE DATE
        ,TimePerDay DECIMAL(5, 2)
        );

    WITH CTE
    AS (
        SELECT peoples.email
            ,peoples.l_update
            ,act.quantity
            ,act.starttime
            ,act.endtime
            ,act.duration AS [Totaltime]
        FROM peoples
        INNER JOIN MPeoples ON peoples.Id = MPeoples.parent_id
        INNER JOIN slines ON MPeoples.id = slines.movesuser_id
        INNER JOIN seg ON slines.id = seg.sline_id
        INNER JOIN act ON seg.id = act.seg_id
        WHERE act.quantity = 'playing'
            AND (peoples.email = @email)
        GROUP BY peoples.email
            ,act.quantity
            ,act.duration
            ,act.starttime
            ,act.endtime
            ,peoples.l_update
        )
    INSERT INTO @test (
        quantity
        ,DATE
        ,TimePerDay
        )
    SELECT quantity
        ,Cast(starttime AS DATE) AS DATE
        ,SUM(datediff(second, starttime, endtime)) / 60.0 AS TimePerDay
    FROM cte WITH (NOLOCK)
    WHERE starttime >= dateadd(week, @LastXWeeks, l_update)
    GROUP BY quantity
        ,cast(starttime AS DATE)

    SELECT @MaxDate = @MaxDate
        ,@MinDate = dateadd(week, (@LastXWeeks + 1), @MaxDate);

    WITH AllDates
    AS (
        SELECT @MinDate AS xDate

        UNION ALL

        SELECT Dateadd(Day, 7, xDate)
        FROM AllDates AS ad
        WHERE ad.xDate < @MaxDate
        )
    SELECT 'playing' AS quantity
        ,ad.xDate
        ,Isnull(t.TimePerDay, 0) AS TimePerDay
    FROM AllDates AS ad WITH (NOLOCK)
    LEFT JOIN @test AS t ON ad.xDate = t.DATE
END

另外，还有一条建议:如果您不了解查询提示 (NOLOCK)，请不要使用它们.在这种情况下，使用 NOLOCK 会对您的结果产生灾难性的影响.

Also, a piece of advice: don't use query hints (NOLOCK) if you do not understand their use. In this case, using NOLOCK can have disastrous effects on your results.

在决定是否继续使用 NOLOCK 之前，您应该阅读以下几篇文章.

Here are a few articles which you should read before deciding if you are going to keep using NOLOCK or not.

了解 SQL Server NOLOCK 提示

不良习惯:NOLOCK 无处不在

这篇关于如何更改查询以提供过去 15 周的数据，而不是来自 SQL Server 的过去 15 天的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！