#sql-server #date #aggregation
#sql-сервер #Дата #агрегирование
Вопрос:
У меня есть таблица, которая содержит customer_id и их apply_date. Я хочу показать за все месяцы 2016 года, что количество клиентов, обратившихся за кредитами, на 30% превышает среднемесячное количество клиентов за год.
customer_id apply_date
-------------------------
1 2016-01-01
2 2016-02-01
3 2016-02-01
4 2016-02-01
5 2016-03-01
6 2016-03-01
7 2016-03-01
8 2016-03-01
9 2016-04-01
10 2016-05-01
11 2017-02-01
12 2017-02-01
13 2017-02-01
В этой таблице в 2016 году было 2 клиента, которые подавали заявки каждый месяц (в среднем 10 клиентов, разделенных на 5 месяцев). Значение, на 30% превышающее среднемесячное значение, если умножить среднее значение, 2, на 1.3, получаем 2.6.
Желаемый результат — я хочу показать месяц, в котором более 2,6 клиентов каждый месяц.
Используя таблицу выше, месяцем в 2016 году, который соответствует условию, является только 2-й и 5-й месяц.
Приведенная выше таблица — это всего лишь выборка из данных.
Я пытался использовать этот код
select
datepart(mm, apply_date) as month, count(*) as cnt
from
Leads
where
apply_date between '2016-01-01' and '2017-01-01'
group by
datepart(mm, apply_date)
но я понятия не имею, как фильтровать данные на основе заданного условия.
Комментарии:
1. Не могли бы вы предоставить образцы данных и ожидать результата? Как вы рассчитываете, что количество клиентов, подающих заявки на получение кредитов, равно 30%
2. @muhnandap, не могли бы вы, пожалуйста, предоставить всю структуру таблицы и некоторые примеры данных?
3. @HasanMahmood спасибо за ответ. Я уже обновил свой вопрос
Ответ №1:
попробуйте этот код:
DECLARE @Leads TABLE
(
customer_id INT,
apply_date DATETIME
)
INSERT INTO @Leads(customer_id, apply_date)VALUES(1,'2016-01-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(2,'2016-02-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(3,'2016-02-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(4,'2016-02-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(5,'2016-03-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(6,'2016-03-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(7,'2016-03-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(8,'2016-03-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(9,'2016-04-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(10,'2016-05-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(11,'2017-02-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(12,'2017-02-01');
INSERT INTO @Leads(customer_id, apply_date)VALUES(13,'2017-02-01');
--SELECT * FROM @Leads
DECLARE @avgInYear DECIMAL(18,2)
SELECT @avgInYear = SUM(a.NoOfCus)/ CAST(COUNT(a.MonthNo) AS DECIMAL(18,2)) FROM (
SELECT DISTINCT MonthNo = MONTH(apply_date), COUNT(customer_id) NoOfCus FROM @Leads
where apply_date between '2016-Jan-01' and '2017-Dec-01'
GROUP BY MONTH(apply_date) ) AS a
SELECT @avgInYear = @avgInYear * 1.3
select datepart(mm,apply_date) as month, COUNT(customer_id) as cnt
from @Leads
where apply_date between '2016-Jan-01' and '2017-Dec-01'
group by datepart(mm,apply_date)
HAVING CAST(COUNT(customer_id) AS DECIMAL(18,2)) > @avgInYear
Комментарии:
1. @muhnandap если это сработает, не могли бы вы, пожалуйста, отметить «Этот ответ как полезный»?