Блог

У меня есть такой вопрос,

 select distinct c.EMAIL, 
c.ORDER_CREATED_DATE as order_date, 
c.action_date , 
DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff

From Customers  c
 

Который возвращает, например, следующие результаты

 EMAIL          ORDER_DATE      ACTION_DATE  DIFF
a@gmail.com    2021-04-26      2021-02-25   60
b@gmail.com    2021-03-15      2021-02-25   18
c@gmail.com    2021-03-05      2021-03-03   2
aa@gmail.com   2021-04-26      2021-02-25   60
 

Мне нужны 2 отдельных запроса,

1. Подсчитайте количество людей, которые покупали в каждый день,

Ожидаемые результаты

 day      email_count
0          0
2          1
18         1
60         2
 

2. подсчитайте количество людей в промежутке между днями

    day  email_count
    0-7    1
    7-14   0
    14-28  1
    

Я действительно не понимаю, как получить результаты примерно так? Пожалуйста, кто-нибудь может помочь.

Для первого запроса вы можете попробовать —

 SELECT 0 DAYS, 0 EMAIL_COUNT
UNION ALL
SELECT diff, COUNT(diff)
  FROM (select distinct c.EMAIL, 
               c.ORDER_CREATED_DATE as order_date, 
               c.action_date , 
               DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff
          From Customers) c
 GROUP BY diff;
 

А для 2-го самый простой из возможных способов-это —

 WITH CTE AS (SELECT diff, COUNT(diff)
               FROM (select distinct c.EMAIL,
                            c.ORDER_CREATED_DATE as order_date, 
                            c.action_date, 
                            DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff
                       From Customers) c
              GROUP BY diff)
SELECT '0-7' day, COUNT(CASE WHEN diff BETWEEN 0 AND 7 THEN email END) email_count
  FROM cte
UNION ALL
SELECT '7-14', COUNT(CASE WHEN diff BETWEEN 7 AND 14 THEN email END)
  FROM cte
UNION ALL
SELECT '14-28', COUNT(CASE WHEN diff BETWEEN 14 AND 28 THEN email END)
  FROM cte
 

Похоже, вы используете Sql-сервер (не MySQL).

Вы можете генерировать интервалы с помощью рекурсивного запроса.

Первый запрос сгенерируйте «интервалы»: сгенерируйте 15 интервалов по восемь дней каждый, вы можете изменить условие, чтобы увеличить количество интервалов. Имейте в виду, что каждый интервал заканчивается каким-то днем, а следующий интервал начинается с того же дня, поэтому строка может совпадать с двумя интервалами.

 WITH intervals AS (SELECT 0 AS f, 7 AS t

                   UNION ALL
                   
                   SELECT f   7, t   7
                   FROM intervals
                   WHERE t < 100),

     sqDiff AS (SELECT DISTINCT 
                          c.EMAIL, 
                          c.ORDER_CREATED_DATE as order_date, 
                          c.action_date,
                          DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) AS diff
                FROM Customers c),
                
     sqCount AS (SELECT
                       diff,
                       count(EMAIL) AS cEmail
                 FROM sqDiff
                 GROUP BY diff)

SELECT CONCAT(i.f, '-', i.t) AS day , 
       CASE WHEN sq.cEmail IS NULL THEN 0 ELSE sq.cEmail END AS email_count
FROM intervals i
LEFT JOIN sqCount sq ON sq.diff between i.f AND i.t;
 

Выход: