特定时间段内的多次交易，受日期范围限制

小编典典

特定时间段内的多次交易，受日期范围限制

sql

我有一个交易，人员，交易日期，物品等数据库。每当一个人购买一件物品时，交易就存储在表格中，如下所示：

personNumber, TransactionNumber, TransactionDate, ItemNumber

我想做的事情是找到从2012年1月1日（transactionDate）到2012年3月1日在14天内（可配置）或更短时间内多次购买相同ItemNumber的人（personNumber）。然后，我需要在报告中列出所有这些交易。

样本数据：

personNumber, TransactionNumber, TransactionDate, ItemNumber
1           |               100|      2001-01-31|        200
2           |               101|      2001-02-01|        206
2           |               102|      2001-02-11|        300
1           |               103|      2001-02-09|        200
3           |               104|      2001-01-01|        001
1           |               105|      2001-02-10|        200
3           |               106|      2001-01-03|        001
1           |               107|      2001-02-28|        200

结果：

personNumber, TransactionNumber, TransactionDate, ItemNumber
1           |               100|      2001-01-31|        200
1           |               103|      2001-02-09|        200
1           |               105|      2001-02-10|        200
3           |               104|      2001-01-01|        001
3           |               106|      2001-01-03|        001

您将如何去做？

我已经尝试过这样做：

select * 
from (
    select personNumber, transactionNumber, transactionDate, itemNumber,
count(*) over (
    partition by personNumber, itemNumber) as boughtSame)
from transactions
where transactionDate between '2001-01-01' and '2001-03-01')t
where boughtSame > 1

这让我明白了：

personNumber, TransactionNumber, TransactionDate, ItemNumber
1           |               100|      2001-01-31|        200
1           |               103|      2001-02-09|        200
1           |               105|      2001-02-10|        200
1           |               107|      2001-02-28|        200
3           |               104|      2001-01-01|        001
3           |               106|      2001-01-03|        001

问题是我不想要TransactionNumber
107，因为那不在14天内。我不确定在哪14天的限制。我可以做一个datediff，但是在哪里，在什么地方呢？

阅读 213

2021-04-14

共1个答案

小编典典

SQL，SQL Server 2005中的窗口功能还不够强大。我将使用相关子查询解决此问题。

相关的子查询计算一个人在每次购买后14天内购买该商品的次数（不计算第一次购买的次数）。

select t.*
from (select t.*,
             (select count(*)
              from t t2
              where t2.personnumber = t.personnumber and
                    t2.itemnumber = t.itemnumber and
                    t2.transactionnumber <> t.transactionnumber and
                    t2.transactiondate >= t.transactiondate and 
                    t2.transactiondate < DATEADD(day, 14, t.transactiondate
             ) NumWithin14Days
      from transactions t
      where transactionDate between '2001-01-01' and '2001-03-01'
     ) t
where NumWithin14Days > 0

您可能还希望将时间限制放入子查询中。

上的索引transactions(personnumber, itemnumber, transactionnumber, itemdate)可能会帮助它更快地运行。

2021-04-14