如何插入每两行的减法并将其插入新列

我有一个关于在 sql 中编写查询的问题.

在图片 1 中，我想从 row1(在列日期中)减去第 2 行，并将其结果插入到标题为 Recency 的新列的 row1 中.并再次从第 2 行减去第 3 行并将其插入新列的第 2 行，依此类推.

:

..........................................................................................

和其他问题:

我还想计算每个用户在当前日期之前的活动频率.我想计算每一行的频率.例如对于这个例子，对于用户 abkqz，我们有:

用户名频率abkqz 4abkqz 3abkqz 2abkqz 10

解决方案

假设如下表结构

CREATE TABLE [15853354] -- 堆栈溢出问题编号([用户名] VARCHAR(20),[提交] INT，[日期] 日期，[得分] NUMERIC(9,2),[点数] NUMERIC(9,1))插入 [15853354]价值观('abkqz', 5, '12 JUL 2010', 83.91, 112.5),('abkqz', 5, '9 JUN 2010', 77.27, 0),('abkqz', 5, '17 May 2010', 91.87, 315)

然后你可以写下面的查询

;WITH [cte15853354] AS(选择[用户名]，[提交]，[日期]，[分数]，【积分】，ROW_NUMBER() OVER (ORDER BY [user-name], [date] DESC) AS [ROWNUMBER]来自 [15853354])选择t.[用户名],t.[提交]，DATEDIFF(DAY, ISNULL([t-1].[date],t.[date]),t.[date]) AS [recency],t.[得分],t.[点数]从 [cte15853354] t左连接 [cte15853354] [t-1]ON [t-1].[用户名] = t.[用户名]AND [t-1].[ROWNUMBER] = t.[ROWNUMBER] + 1

这使用公用表表达式来计算行号，然后进行自联接以将每一行与下一行联接，然后计算以天为单位的日期差异.

结果如下:

I have a question about writing query in sql.

in the picture 1 I want to subtract row 2 from row1 (in column date) and insert it's result in row1 of new column with the title of Recency. and again subtract row3 from row2 and insert it in row2 of the new column, and so on.

picture 1:

in fact I want to calculate the recency of each user's activity. for example in the following picture, I calculated this for one user(manually); I want to do this for all of the users by writing a query in sql.

picture 2:

..........................................................................................

and other question:

I also want to calculate the frequency of activity of each user before the current date. I want to calculate frequency for each row. for example for this example, for user abkqz we have:

user name     frequency
abkqz             4
abkqz             3
abkqz             2
abkqz             1
abkqz             0

解决方案

Assuming the following table structure

CREATE TABLE [15853354] -- Stack Overflow question number
(
    [user-name] VARCHAR(20),
    [submissions] INT,
    [date] DATE,
    [score] NUMERIC(9,2),
    [points] NUMERIC(9,1)
)

INSERT [15853354]
VALUES
    ('abkqz', 5, '12 JUL 2010', 83.91, 112.5),
    ('abkqz', 5, '9 JUN 2010', 77.27, 0),
    ('abkqz', 5, '17 MAY 2010', 91.87, 315)

Then you could write the following query

;WITH [cte15853354] AS
(
    SELECT 
        [user-name],
        [submissions],
        [date],
        [score],
        [points],
        ROW_NUMBER() OVER (ORDER BY [user-name], [date] DESC) AS [ROWNUMBER]
    FROM [15853354]
)
SELECT 
    t.[user-name],
    t.[submissions],
    DATEDIFF(DAY, ISNULL([t-1].[date],t.[date]),t.[date]) AS [recency],
    t.[score],
    t.[points]
FROM [cte15853354] t
LEFT JOIN [cte15853354] [t-1]
    ON [t-1].[user-name] = t.[user-name]
    AND [t-1].[ROWNUMBER] = t.[ROWNUMBER] + 1

This uses a Common Table Expression to calculate a row number, and then does a self join to join each row with the next, and then calculates the date difference in days.

This is the result: