sql - SQL Server 中框架子句的组模式的替代方法是什么？

Question

我正在学习高级 SQL。作为其中的一部分，我在窗口函数中遇到了窗口函数和框架子句。本文对这些主题有很好的解释，并且基于 PostgreSQL。

https://mjk.space/advances-sql-window-frames/

现在我正在尝试在 SQL Server 中编写查询。

这是我的桌子：

CREATE TABLE [transactions]
(
    [transaction_id] [nvarchar](255) NULL,
    [date] [datetime] NULL,
    [user_id] [nvarchar](255) NULL,
    [is_blocked] [bit] NOT NULL,
    [transaction_amount] [float] NULL,
    [transaction_category_id] [float] NULL
) 

对于这个表，我想编写一个查询，这样对于每笔交易，它都会返回该用户在过去 7 天内完成的交易数——</p>

我的查询

SELECT
    transaction_id, [user_id], [date],
    COUNT(transaction_id) OVER (PARTITION BY [user_id] ORDER BY [date] 
                                ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS transactions_count
FROM
    transactions

此查询的问题是日期重复。当有重复的文章说我们使用GROUPS而不是ROWS. 但我从 SQL Server 文档中意识到它只支持ROWS和RANGE. 那么如何在 SQL Server 中处理这个重复问题。GROUPSSQL Server中的替代方法是什么？

标签： sqlsql-serverwindow-functions

Answer 1

这里的窗口函数将查找前导或滞后 n 行，但是因为我们不知道我们可能想要查看多少行，所以我不认为窗口函数是执行此操作的最佳方法。我会自己加入餐桌。

编辑：当用户没有执行任何事务时，我添加了一个填充表来填充结果集中的日期。这将为每个用户返回数据集中最早日期和最晚日期之间的每个日期的结果。如果您不需要这个，请随意删除 @cal 和 @fill 引用。

首先：这是我正在使用的示例数据集。我创建了一个日历并填写表格来报告特定用户没有交易的天数。

DECLARE @transactions TABLE (
    [transaction_id] [nvarchar](255) NULL,
    [date] [datetime] NULL,
    [user_id] [nvarchar](255) NULL,
    [is_blocked] [bit] NOT NULL,
    [transaction_amount] [float] NULL,
    [transaction_category_id] [float] NULL
) 

INSERT INTO @transactions
VALUES
('1',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0),1,0,13,16),('2',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0),1,0,13,16),('3',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-1,1,0,13,16),('4',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-1,2,0,13,16),('5',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-1,1,0,13,16),('6',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-2,1,0,13,16),('7',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-3,1,0,13,16),('8',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-3,2,0,13,16),('9',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-3,2,0,13,16),('10',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-3,1,0,13,16),
('11',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-4,1,0,13,16),('12',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-4,1,0,13,16),('13',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-5,1,0,13,16),('14',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-5,2,0,13,16),('15',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-5,1,0,13,16),('16',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-6,1,0,13,16),('17',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-7,1,0,13,16),('18',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-7,2,0,13,16),('19',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-7,2,0,13,16),('20',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-8,1,0,13,16),
('21',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-9,1,0,13,16),('22',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-9,1,0,13,16),('23',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-10,1,0,13,16),('24',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-10,2,0,13,16),('25',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-10,1,0,13,16),('26',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-11,1,0,13,16),('27',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-12,1,0,13,16),('28',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-12,2,0,13,16),('29',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-12,2,0,13,16),('30',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-12,1,0,13,16)
,('21',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-34,1,0,13,16),('21',DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)-30,1,0,13,16)

--Create calendar table based on transactions table to ensure all dates are covered.
DECLARE @date DATETIME = (SELECT MIN(date) FROM @transactions)
DECLARE @endDate DATETIME = (SELECT MAX(date) FROM @transactions)
DECLARE @cal TABLE (
date DATETIME
)

WHILE @date <= @endDate
BEGIN
    INSERT INTO @cal SELECT @date
    SET @date += 1
END

--Create filler table to account for missing transaction dates

DECLARE @fill TABLE (
    [user_id] [nvarchar](255) NULL,
    [date] [datetime] NULL
) 

INSERT INTO @fill
SELECT DISTINCT
    a.[user_id],
    b.[date]
FROM @transactions a
FULL JOIN @cal b ON 1 = 1
WHERE a.[date] <> b.date

这是查询。此查询不包括报告当天的交易，仅包括前 7 天的交易。这可以在 Join 子句中进行调整。

SELECT
    a.[user_id]
    ,a.[date]
    ,COUNT(b.transaction_id) AS Trailing7DayTransactionCount
FROM @transactions a
LEFT JOIN @transactions b ON
    b.[date] BETWEEN DATEADD(DAY,-7,a.[date]) AND DATEADD(DAY,-1,a.[date])  --Look for transactions in the last 7 days. Because BETWEEN is inclusive, we look for -7 to -1.
    AND b.user_id = a.user_id   --Looking for historical transactions for the same user.
GROUP BY
    a.[user_id]
    ,a.[date]
ORDER BY 
    a.[user_id]
    ,a.[date] DESC

这是输出的 SQL Fiddle。