特定时间段内的多次交易，受日期范围限制 [英] multiple transactions within a certain time period, limited by date range

问题描述

我有一个包含交易、人员、交易日期、项目等的数据库.每次有人购买商品时，交易都会像这样存储在表中:

I have a database of transactions, people, transaction dates, items, etc. Each time a person buys an item, the transaction is stored in the table like so:

personNumber, TransactionNumber, TransactionDate, ItemNumber

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

What I want to do is to find people (personNumber) who, from January 1st 2012(transactionDate) until March 1st 2012 have purchased the same ItemNumber multiple times within 14 days (configurable) or less. I then need to list all those transactions on a report.

示例数据:

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，但是在什么地方，在什么地方?

The issue is that I don't want TransactionNumber 107, since that's not within the 14 days. I'm not sure where to put in that limit of 14 days. I could do a datediff, but where, and over what?

推荐答案

唉，SQL Server 2005 中的窗口函数不够强大.我会使用相关子查询来解决这个问题.

Alas, the window functions in SQL Server 2005 just are not quite powerful enough. I would solve this using a correlated subquery.

相关子查询计算一个人在每次购买后 14 天内购买该商品的次数(不包括第一次购买).

The correlated subquery counts the number of times that a person purchased the item within 14 days after each purchase (and not counting the first purchase).

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

您可能还想在子查询中设置时间限制.

You may want to put the time limit in the subquery as well.

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

An index on transactions(personnumber, itemnumber, transactionnumber, itemdate) might help this run much faster.

这篇关于特定时间段内的多次交易，受日期范围限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！