确定在 mysql 中给定时间段后发生的客户访问 [英] Identify customer visits occurring after a given time period in mysql

问题描述

我想使用 mysql 确定发生 45 天的客户访问.必须选择客户的第一次访问，必须选择在先前选择的访问之后 45 天发生的以下访问.例如，我有以下示例访问表 -

I want to identify customer visits occurring 45 days using mysql. 1st visit of the customer must be selected, the following visits which occur 45 days after the previously selected visit must be selected. For example, I have the below sample visits table -

Visit_ID    Cust_ID Vst_Beg_Dt  Vst_End_Dt  Post_45_Day_Dt  Selection Criteria
121         678     2015-07-02  2015-07-08  8/22/2015       select since it is the first visit
223         678     2015-10-25  2015-10-27  12/11/2015      select since the visit occurs after 45 days -  8/22/2015
229         678     2016-02-21  2016-02-23  4/8/2016        select since the visit occurs after 45 days of the previously selected visit - 12/11/2015
445         545     2015-11-23  2015-11-28  1/12/2016       select since it is the first visit
198         545     2016-01-07  2016-01-12  2/26/2016       not selected since the visit is not after 45 days
271         545     2016-01-19  2016-01-29  3/14/2016       select since the visit occurs after 45 days of the previously selected visit - 12/11/2015
841         291     2015-09-08  2015-09-12  10/27/2015      select since it is the first visit
987         291     2015-12-19  2015-12-23  2/6/2016        select since the visit occurs after 45 days - 10/27/2015
211         291     2015-12-26  2016-01-06  2/20/2016       not selected since the visit is not after 45 days
122         291     2016-02-25  2016-02-27  4/12/2016       select since the visit occurs after 45 days of the previously selected visit - 2/6/2016

预期的输出必须只有以下记录 -

The expected output must have only the following records -

Visit_ID    Cust_ID Vst_Beg_Dt  Vst_End_Dt      
121         678     2015-07-02  2015-07-08      
223         678     2015-10-25  2015-10-27      
229         678     2016-02-21  2016-02-23      
445         545     2015-11-23  2015-11-28      
271         545     2016-01-19  2016-01-29      
841         291     2015-09-08  2015-09-12      
987         291     2015-12-19  2015-12-23      
122         291     2016-02-25  2016-02-27

这可能吗?当客户的访问次数超过 2 次时，我遇到了识别上次访问的问题.谢谢！

Is this possible? I am running into issues to identify the previous visit when the customer has more than 2 visits. Thanks!

推荐答案

测试这个查询:

SELECT * FROM (
  SELECT cv.*
  , CASE WHEN DATE_ADD(@d, INTERVAL 45 DAY) >= Vst_Beg_Dt AND Cust_ID = @c THEN @d
    WHEN DATE_ADD(@d, INTERVAL 45 DAY) < Vst_Beg_Dt AND Cust_ID = @c THEN @d := Vst_End_Dt
    WHEN (@c := Cust_ID) AND (@d := Vst_End_Dt) THEN @d END AS end45
  FROM customer_visits AS cv, (SELECT @d := CURDATE(), @c := 0) AS p 
  ORDER BY Cust_ID, Vst_Beg_Dt ) part_by_End_Dt45
WHERE Vst_End_Dt = end45
ORDER BY Cust_ID DESC, Vst_Beg_Dt ASC;

子查询进行分区，将当前cust_id 的第一个Vst_End_Dt 日期存储在变量@d 中，如果Vst_Beg_Dt > @d + 45 或cust_id 更改，则再次设置@d = Vst_End_Dt.@d 的值在 end45 中返回.
最终查询过滤器 end45 = Vst_End_Dt，因此如果 Vst_End_Dt <> @d 行被尖峰.

Subquery do partitioning, store first Vst_End_Dt date for current cust_id in variable @d, if Vst_Beg_Dt > @d + 45 or cust_id changed then again set @d = Vst_End_Dt. Value of @d is returned in end45.
Final query filter end45 = Vst_End_Dt, so if Vst_End_Dt <> @d row is spiked.

不知道你还期待什么，这个查询返回的结果和你发的一模一样.

I don't know what you expect more, this query return result exactly like posted by you.

SQL 小提琴

MySQL 5.6 架构设置:

CREATE TABLE customer_visits
    ( Visit_ID        INT
  , Cust_ID         INT
  , Vst_Beg_Dt      DATE
  , Vst_End_Dt      DATE
  , Post_45_Day_Dt  DATE
  , Selection_Criteria VARCHAR(90))
;

INSERT INTO customer_visits
    (Visit_ID, Cust_ID, Vst_Beg_Dt, Vst_End_Dt, Post_45_Day_Dt, Selection_Criteria)
VALUES
  (121, 678, '2015-07-02', '2015-07-08', '2015-08-22', 'select since it is the first visit'),
  (223, 678, '2015-10-25', '2015-10-27', '2015-12-11', 'select since the visit occurs after 45 days - 8/22/2015'),
  (229, 678, '2016-02-21', '2016-02-23', '2016-04-08', 'select since the visit occurs after 45 days of the previously selected visit - 12/11/2015'),
  (445, 545, '2015-11-23', '2015-11-28', '2016-01-12', 'select since it is the first visit'),
  (198, 545, '2016-01-07', '2016-01-12', '2016-02-26', 'not selected since the visit is not after 45 days'),
  (271, 545, '2016-01-19', '2016-01-29', '2016-03-14', 'select since the visit occurs after 45 days of the previously selected visit - 12/11/2015'),
  (841, 291, '2015-09-08', '2015-09-12', '2015-10-27', 'select since it is the first visit'),
  (987, 291, '2015-12-19', '2015-12-23', '2016-02-06', 'select since the visit occurs after 45 days - 10/27/2015'),
  (211, 291, '2015-12-26', '2016-01-06', '2016-02-20', 'not selected since the visit is not after 45 days'),
  (122, 291, '2016-02-25', '2016-02-27', '2016-04-12', 'select since the visit occurs after 45 days of the previously selected visit - 2/6/2016')

;

查询 1:

SELECT Visit_ID
, Cust_ID
, DATE_FORMAT(Vst_Beg_Dt, '%Y-%m-%d') as Beg_Dt
, DATE_FORMAT(Vst_End_Dt, '%Y-%m-%d') as End_Dt
, Selection_Criteria
FROM (
  SELECT cv.*
  , CASE WHEN DATE_ADD(@d, INTERVAL 45 DAY) >= Vst_Beg_Dt AND Cust_ID = @c THEN @d
    WHEN DATE_ADD(@d, INTERVAL 45 DAY) < Vst_Beg_Dt AND Cust_ID = @c THEN @d := Vst_End_Dt
    WHEN (@c := Cust_ID) AND (@d := Vst_End_Dt) THEN @d END AS end45
  FROM customer_visits AS cv, (SELECT @d := CURDATE(), @c := 0) AS p 
  ORDER BY Cust_ID, Vst_Beg_Dt ) part_by_End_Dt45
WHERE Vst_End_Dt = end45
ORDER BY Cust_ID DESC, Vst_Beg_Dt ASC

结果:

| Visit_ID | Cust_ID |     Beg_Dt |     End_Dt |                                                                        Selection_Criteria |
|----------|---------|------------|------------|-------------------------------------------------------------------------------------------|
|      121 |     678 | 2015-07-02 | 2015-07-08 |                                                        select since it is the first visit |
|      223 |     678 | 2015-10-25 | 2015-10-27 |                                   select since the visit occurs after 45 days - 8/22/2015 |
|      229 |     678 | 2016-02-21 | 2016-02-23 | select since the visit occurs after 45 days of the previously selected visit - 12/11/2015 |
|      445 |     545 | 2015-11-23 | 2015-11-28 |                                                        select since it is the first visit |
|      271 |     545 | 2016-01-19 | 2016-01-29 | select since the visit occurs after 45 days of the previously selected visit - 12/11/2015 |
|      841 |     291 | 2015-09-08 | 2015-09-12 |                                                        select since it is the first visit |
|      987 |     291 | 2015-12-19 | 2015-12-23 |                                  select since the visit occurs after 45 days - 10/27/2015 |
|      122 |     291 | 2016-02-25 | 2016-02-27 |   select since the visit occurs after 45 days of the previously selected visit - 2/6/2016 |

这篇关于确定在 mysql 中给定时间段后发生的客户访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！