問題描述

我有一個關于在 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:

這篇關于如何插入每兩行的減法并將其插入新列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！