how to insert subtract of each two subsequent rows

问题:

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

回答1:

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:

回答2:

Try something like this (untested, since sample data was only posted in a picture). The query users analytical function options that were introduced in SQL Server 2012, so this won't work on an earlier version.

select
  [user-name],
  submissions,
  score,
  datediff(day,
    lag([date],1) over (
      partition by [user-name]
      order by [date],
    [date]) as recency,
  count(*) over (
    partition by [user-name]
    order by [date] desc) -1 as frequency
from yourTable;