Подсчитать и повернуть таблицу по дате

Я хотел бы идентифицировать возвращающихся клиентов из таблицы Oracle(11g) следующим образом:

CustID | Date
-------|----------
XC321  | 2016-04-28
AV626  | 2016-05-18
DX970  | 2016-06-23
XC321  | 2016-05-28
XC321  | 2016-06-02

Таким образом, я могу видеть, какие клиенты вернулись в различные окна, например, в течение 10, 20, 30, 40 или 50 дней. Например:

CustID | 10_day | 20_day | 30_day | 40_day | 50_day 
-------|--------|--------|--------|--------|--------
XC321  |        |        |    1   |        |        
XC321  |        |        |        |    1   |

Я бы даже принял такой результат:

CustID |    Date    | days_from_last_visit
-------|------------|---------------------
XC321  | 2016-05-28 |                   30        
XC321  | 2016-06-02 |                    5

Я предполагаю, что он использовал бы раздел с помощью оконного предложения с неограниченными следующими и предыдущими предложениями... но я не могу найти подходящих примеров. Есть идеи...? Спасибо

Источник

3 ответа

Здесь нет необходимости в оконных функциях, вы можете просто сделать это с помощью условного агрегирования, используя CASE EXPRESSION:

SELECT t.custID,
       COUNT(CASE WHEN (last_visit- t.date) <= 10 THEN 1 END) as 10_day,
       COUNT(CASE WHEN (last_visit- t.date) between 11 and 20 THEN 1 END) as 20_day,
       COUNT(CASE WHEN (last_visit- t.date) between 21 and 30 THEN 1 END) as 30_day,
       .....
FROM (SELECT s.custID,
             LEAD(s.date) OVER(PARTITION BY s.custID ORDER BY s.date DESC) as last_visit
      FROM YourTable s) t
GROUP BY t.custID
Источник

Установка Oracle:

CREATE TABLE customers ( CustID, Activity_Date ) AS
SELECT 'XC321', DATE '2016-04-28' FROM DUAL UNION ALL
SELECT 'AV626', DATE '2016-05-18' FROM DUAL UNION ALL
SELECT 'DX970', DATE '2016-06-23' FROM DUAL UNION ALL
SELECT 'XC321', DATE '2016-05-28' FROM DUAL UNION ALL
SELECT 'XC321', DATE '2016-06-02' FROM DUAL;

Запрос:

SELECT *
FROM   (
  SELECT CustID,
         Activity_Date AS First_Date,
         COUNT(1) OVER ( PARTITION BY CustID
                         ORDER BY Activity_Date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL '10' DAY FOLLOWING )
           - 1 AS "10_Day",
         COUNT(1) OVER ( PARTITION BY CustID
                         ORDER BY Activity_Date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL '20' DAY FOLLOWING )
           - 1 AS "20_Day",
         COUNT(1) OVER ( PARTITION BY CustID
                         ORDER BY Activity_Date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL '30' DAY FOLLOWING )
           - 1 AS "30_Day",
         COUNT(1) OVER ( PARTITION BY CustID
                         ORDER BY Activity_Date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL '40' DAY FOLLOWING )
           - 1 AS "40_Day",
         COUNT(1) OVER ( PARTITION BY CustID
                         ORDER BY Activity_Date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL '50' DAY FOLLOWING )
           - 1 AS "50_Day",
        ROW_NUMBER() OVER ( PARTITION BY CustID ORDER BY Activity_Date ) AS rn
  FROM  Customers
)
WHERE rn = 1;

Выход

USTID FIRST_DATE              10_Day     20_Day     30_Day     40_Day     50_Day         RN
------ ------------------- ---------- ---------- ---------- ---------- ---------- ----------
AV626  2016-05-18 00:00:00          0          0          0          0          0          1 
DX970  2016-06-23 00:00:00          0          0          0          0          0          1 
XC321  2016-04-28 00:00:00          0          0          1          2          2          1
Источник

Вот ответ, который работает для меня, я основал его на ваших ответах выше, спасибо за вклады от MT0 и Sagi:

SELECT CustID,
visit_date,
Prev_Visit ,
COUNT(  CASE    WHEN (Days_between_visits) <=10    THEN 1  END) AS "0-10_day" ,
COUNT(  CASE    WHEN (Days_between_visits) BETWEEN 11 AND 20    THEN 1  END) AS "11-20_day" ,
COUNT(  CASE    WHEN (Days_between_visits) BETWEEN 21 AND 30    THEN 1  END) AS "21-30_day" ,
COUNT(  CASE    WHEN (Days_between_visits) BETWEEN 31 AND 40    THEN 1  END) AS "31-40_day" ,
COUNT(  CASE    WHEN (Days_between_visits) BETWEEN 41 AND 50    THEN 1  END) AS "41-50_day" ,
COUNT(  CASE    WHEN (Days_between_visits) >50    THEN 1  END) AS "51+_day"
FROM
  (SELECT CustID,
  visit_date,
  Lead(T1.visit_date) over (partition BY T1.CustID order by T1.visit_date DESC) AS Prev_visit,
  visit_date - Lead(T1.visit_date) over (
                  partition BY T1.CustID order by T1.visit_date DESC) AS Days_between_visits
  FROM T1
) T2
WHERE Days_between_visits >0
GROUP BY T2.CustID ,
T2.visit_date ,
T2.Prev_visit ,
T2.Days_between_visits;

Это возвращает:

CUSTID | VISIT_DATE | PREV_VISIT | DAYS_BETWEEN_VISIT | 0-10_DAY | 11-20_DAY | 21-30_DAY | 31-40_DAY | 41-50_DAY | 51+DAY 
XC321  | 2016-05-28 | 2016-04-28 |                 30 |          |           |         1 |           |           |
XC321  | 2016-06-02 | 2016-05-28 |                  5 |       1  |           |           |           |           |
Источник
Другие вопросы по тегам