如何在每两行后的行中插入减法并将其插入到新列中

一尘不染

如何在每两行后的行中插入减法并将其插入到新列中

sql

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

在图片1中，我想从第1行中减去第2行（以列日期为准），并将其结果插入标题为Recency的新列的row1中。然后再次从第2列减去第3列，并将其插入新列的第2列，依此类推。

实际上，我想计算每个用户活动的新近度。例如，在下图中，我为一个用户（手动）计算了这个；我想通过在sql中编写查询来为所有用户执行此操作。

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

和其他问题：

我还想计算当前日期之前每个用户的活动频率。我想计算每一行的频率。例如，对于此示例，对于abkqz用户，我们有：

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

阅读 117

2021-05-16

共1个答案

一尘不染

假设下表结构

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)

然后，您可以编写以下查询

;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

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

2021-05-16