sql - 如何在 microsoft sql server 上的有限时间范围内从同一个人获取 2 笔交易？

Question

我想在 Microsoft SQL Server 上获取同一用户在 2 分钟内发生的任何交易

我基于 this question试图解决我的问题，并以这个查询结束:

SELECT css.*
FROM
(
    SELECT css.*,
(
    SELECT COUNT(*)
    FROM CashlessTransactions t2
    WHERE t2.IdCustomerSmartcard = css.IdCustomerSmartcard
          AND t2.IdCashlessTransaction <> css.IdCashlessTransaction
          AND t2.Date >= css.Date
          AND t2.Date < DATEADD(minute, 2, css.Date)
) tranwithin2min
    FROM CashlessTransactions css
) css
JOIN [dbo].[POSDevices] pd ON pd.IdPOSDevice = css.IdPOSDevice
JOIN [dbo].[EventSessionSetups] ess ON ess.IdEventSessionSetup = pd.IdEventSessionSetup
JOIN [dbo].[Events] e ON e.IdEvent = ess.IdEvent
WHERE e.IdEvent = 2
      AND tranwithin2min > 0
ORDER BY css.Date;

问题是:1- 我不完全理解它是如何工作的，所以结果不是我所期望的 -> 当这段时间有 2 笔交易匹配时，我只得到其中的 1 笔。我试过乱搞，添加一些过滤器并删除其他过滤器，但无法获得我想要的结果

2- 查询运行时间过长。我认为在执行 where IdEvent = 2 之前从所有无现金交易中获取子查询，这会大大缩小搜索范围

编辑 1:添加数据样本:

IdCashlessTransaction                 IdCustomerSmartcard                   Amount  Date                    IdPOSDevice
4DA8FA70-905F-44B3-8513-26F4A9AFD212    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    10.00     2017-02-23 18:18:50.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49
0B486470-2CB6-4145-8BA5-5B54AABEF997    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    0.00      2017-02-23 18:18:51.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49
8B156710-29CF-4104-B669-63A004F1B19B    364DBAA2-E85D-45C3-A4DC-7549A4E7ECE5    -9.79     2017-02-23 18:19:06.000   D3E5452E-4DAB-4B4D-B64D-24599DDA7A49

编辑 2:添加请求的 DDL:

CREATE TABLE [dbo].[CashlessTransactions](
  [IdCashlessTransaction] [uniqueidentifier] NOT NULL,
  [IdUser] [uniqueidentifier] NOT NULL,
  [IdCustomerSmartcard] [uniqueidentifier] NOT NULL,
  [IdPOSDevice] [uniqueidentifier] NULL,
  [Amount] [decimal](10, 2) NOT NULL,
  [Date] [datetime] NOT NULL,
CONSTRAINT [PK_POSTransactions] PRIMARY KEY CLUSTERED 
(
  [IdCashlessTransaction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

1) 查询为每笔交易所做的是获取原始交易后 2 分钟内使用同一张卡进行的不同交易的计数。它为主查询中的每个事务获取此计数。然后它返回每笔交易，该交易至少有 1 笔其他交易发生在第一笔交易的 2 分钟内。因此，对于您的三个样本记录，第一个 tran (tran1) 返回计数 2(因为接下来的两个 trans 在 2 分钟内)。下一个 tran (tran2) 返回时计数为 1，因为只有列表中的最后一笔交易在 tran2 的 2 分钟内。最后一笔交易没有返回，因为计数为0(后面没有交易)，因此被WHERE过滤掉。

2) 如果您只关心 IdEvent 为 2 的事务，则需要加入 POSDevices、EventSessionSetups 和 events两次。

这是我想出的。您可以更改主 SELECT 返回的列，我鼓励您将 LEFT JOIN 子查询中的 css.* 更改为仅在主 SELECT 中返回您需要的列。

SELECT 
    UsersCardID             = c1.IdCustomerSmartcard 
    ,OriginalTransaction    = c1.IdCashlessTransaction
    ,OrigTranDate           = c1.Date
    ,OrigTranAmount         = c1.Amount
    ,TranWithin2Min         = c2.IdCashlessTransaction
    ,TranWithin2MinDate     = c2.Date
    ,TranWithin2MinAmount   = c2.Amount
FROM #CashlessTransactions      c1
JOIN [dbo].[POSDevices]         pd1 ON pd1.IdPOSDevice = c1.IdPOSDevice
JOIN [dbo].[EventSessionSetups] ess1 ON ess1.IdEventSessionSetup = pd1.IdEventSessionSetup
JOIN [dbo].[events]             e1 ON e1.IdEvent = ess1.IdEvent
LEFT JOIN (
            SELECT 
                css.*
            FROM CashlessTransactions css
            JOIN [dbo].[POSDevices]         pd2 ON pd2.IdPOSDevice = css.IdPOSDevice
            JOIN [dbo].[EventSessionSetups] ess2 ON ess2.IdEventSessionSetup = pd2.IdEventSessionSetup
            JOIN [dbo].[events]             e2 ON e2.IdEvent = ess2.IdEvent
            WHERE e.IdEvent = 2
) c2 ON c2.IdCustomerSmartcard = c1.IdCustomerSmartcard AND c2.IdCashlessTransaction <> c1.IdCashlessTransaction
WHERE c2.Date >= c1.Date 
    AND c2.Date < DATEADD(MINUTE, 2, c1.Date)
    AND c2.IdCashlessTransaction IS NOT NULL
ORDER BY c1.date, c2.date