Попытка подсчитать общее количество заказов и количество уникальных клиентов за месяц
Я пытаюсь получить общее количество заказов и количество уникальных клиентов за 30-дневный период.
У меня есть такая таблица:
order_id, order_date, order_country, customer_id.
(1, '2021/03/04', 'SWE', 1),
(2, '2021/03/01', 'SWE', 1),
(3, '2021/03/01', 'DK', 3),
(4, '2021/03/01', 'DK', 3),
(5, '2021/03/03', 'NOR', 2),
(6, '2021/02/27', 'DK', 3),
(7, '2020/12/30', 'Ger', 4);
Я пробовал что-то вроде этого:
SELECT order_date
, COUNT(order_id) as orderAmount
, COUNT(distinct customer_id) as customerAmount
FROM orders
WHERE order_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP
BY order_date
Это дает общее количество заказов на дату, чего я хочу. Но он учитывает только уникальных клиентов на дату, а не ежемесячно, поэтому один и тот же клиент появляется несколько раз. Я пробовал небольшие варианты этого sql-скрипта, но не могу заставить его работать.
2 ответа
Решение
Пожалуйста, проверьте, желаемый ли это ваш ответ.
Схема (MySQL v5.5)
create table orders (order_id int, order_date date, order_country varchar(10), customer_id int);
insert into orders values(1, '2021/03/04', 'SWE', 1);
insert into orders values(2, '2021/03/01', 'SWE', 1);
insert into orders values(3, '2021/03/01', 'DK', 3);
insert into orders values(4, '2021/03/01', 'DK', 3);
insert into orders values(5, '2021/03/03', 'NOR', 2);
insert into orders values(6, '2021/02/27', 'DK', 3);
insert into orders values(7, '2020/12/30', 'Ger', 4);
Запрос №1
SELECT
o.order_date,COUNT(order_id) as orderAmount
, coalesce(max(Customer_Count),0) as customerAmount
FROM orders o left join (select order_date,count(distinct customer_id)Customer_Count from orders o where
not exists (select customer_id from orders oa where o.customer_id=oa.customer_id and oa.order_date<o.order_date
and oa.order_date > now() - INTERVAL 30 DAY )
group by order_date) oc on o.order_date=oc.order_date
WHERE o.order_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
group by o.order_date
order by o.order_date;
Просто посчитайте, когда впервые появляется покупатель:
SELECT order_date, COUNT(*) as num_orders,
COUNT(DISTINCT customer_id) as distinct_customers_on_day,
SUM(SUM(seqnum = 1)) OVER (ORDER BY order_date) as running_distinct_customers
FROM (SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as seqnum
FROM orders o
WHERE o.order_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
) o
GROUP BY order_date;
Вот скрипка db <>.