sql - 根据交易日期计算客户的行程

Question

此处为基本 T-SQL 用户。我在尝试完成任务时遇到问题，希望得到一些指导。对于任何错误提前道歉，因为英语不是我的母语。

我有一个包含大量交易的表，为了简单起见，假设我只有两列:CUSTOMER_ID(我的客户)和 DATE(交易日期)。

我的客户在城里时会进行大量交易，但他们可能会花费数周、数月甚至数年的时间才回来并再次开始进行交易。我想以某种方式识别每一个“旅行”并对所涉及的交易进行分组，然后我想做一些事情，例如计算旅行持续时间、交易数量等。

我希望将旅行视为 10 天空闲期后发生的任何新交易。

让我尝试使用一些简单的示例来更好地解释我的请求:

这是我的交易表:

+-------------+------------+
| CUSTOMER_ID |    DATE    |
+-------------+------------+
| JHON        | 01-01-2016 |
| JHON        | 01-02-2016 |
| PEDRO       | 01-02-2016 |
| JHON        | 01-05-2016 |
| MIKE        | 01-05-2016 |
| MIKE        | 01-10-2016 |
| JHON        | 01-07-2016 |
| …           | …          |
| JHON        | 02-15-2016 |
| JHON        | 02-18-2016 |
| MIKE        | 02-19-2016 |
| MIKE        | 02-19-2016 |
+-------------+------------+

到目前为止，我已经进行了此查询来枚举客户的访问:

SELECT
    CUSTOMER_ID,
    DATE,
    ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATE) as VISIT_NUM

FROM
    TRANSACTIONS
WHERE
    CUSTOMER_ID IN ('JHON','MIKE','PEDRO')

运行该查询将得到与此类似的结果:

+-------------+------------+-----------+
| CUSTOMER_ID |    DATE    | VISIT_NUM |
+-------------+------------+-----------+
| JHON        | 01-01-2016 |         1 |
| JHON        | 01-02-2016 |         2 |
| JHON        | 01-07-2016 |         3 |
| JHON        | 02-15-2016 |         4 |
| JHON        | 02-18-2016 |         5 |
| MIKE        | 01-05-2016 |         1 |
| MIKE        | 01-10-2016 |         2 |
| MIKE        | 02-19-2016 |         3 |
| MIKE        | 02-19-2016 |         4 |
| PEDRO       | 01-02-2016 |         1 |
+-------------+------------+-----------+

现在是棘手的部分:我需要以某种方式创建一个查询(可能使用上面的查询作为上一步)向我展示客户的行程信息，继续示例，理想的结果将是这样的:

+-------------+----------+---------------+-------------+---------------+--------------+
| CUSTOMER_ID | TRIP_NUM | TRIP_START_DT | TRIP_END_DT | TRIP_DURATION | TRANSACTIONS |
+-------------+----------+---------------+-------------+---------------+--------------+
| JHON        |        1 | 01-01-2016    | 01-07-2016  |             7 |            3 |
| JHON        |        2 | 02-15-2016    | 02-18-2016  |             3 |            2 |
| MIKE        |        1 | 01-05-2016    | 01-10-2016  |             5 |            2 |
| MIKE        |        2 | 02-19-2016    | 02-19-2016  |             1 |            2 |
| PEDRO       |        1 | 01-02-2016    | 01-02-2016  |             1 |            1 |
+-------------+----------+---------------+-------------+---------------+--------------+

如您所见，Jhon 先生在一月份来了 3 次，并在二月份再次回来。距离他一月份的最后一笔交易已经过去十多天了，我想把他的这组新交易视为他的一次新的“旅行”。迈克在一月份也有一些事件，二月份也回来了，在他的第二次旅行中，他在同一天进行了两笔交易，我也想说明这一点。如果客户只来一天并进行了一些事件(如 Pedro 先生的情况)，我也想将单日单笔交易记录视为行程记录。

我非常感谢对此的任何了解，老实说，我不知道如何继续(我一直在阅读有关光标的内容，但在这一点上似乎是黑魔法，无法找到一种方法来实现它们) .

对于我的任何语法错误和任何可能的遗漏，再次表示歉意。如有必要，我会进一步澄清。

Answer 1

在您的示例中，计算行程持续时间对于所有员工来说并不是标准的，因此我对其进行了调整，以遵循所有员工的第一个客户 ID

DEMO HERE

 ;with cte
 as
 (select cid,datee,datepart(month,datee) as monthh,
  dense_rank () over (partition by cid order by datepart(month,datee)) as samemonth,
 count(0) over (partition by cid,datepart(month,datee) ) as cnt
 from #temp
)
,cte1 as
 (
select cid,max(samemonth) as tripnumber,min(datee) as startdate,max(datee) as enddate,
max(cnt) as numberoftrips
from  cte 
group by cid,samemonth
)
select *,datediff(day,startdate,dateadd(day,1,enddate))as duration
from  cte1 

输出:

cid   tripnumber startdate      enddate    numberoftransactions duration
JHON    1        2016-01-01    2016-01-07   3                    7
JHON    2        2016-02-15    2016-02-18   2                    4
MIKE    1        2016-01-05    2016-01-10   2                    6
MIKE    2        2016-02-19    2016-02-19   2                    1
PEDRO   1        2016-01-02    2016-01-02   1                    1