#sql #sql-server #date #join #duplicates
Вопрос:
Допустим, у нас есть 2 таблицы с именами Записи и возможности:
Записи:
| РекордИД | Идентификатор клиента | Дата создания |
|---|---|---|
| 777 | 1 | 1/1/2021 |
| 888 | 2 | 1/1/2021 |
| 999 | 1 | 2/1/2021 |
Возможности:
| ОппИД | Идентификатор клиента | OppCreateDate |
|---|---|---|
| 10 | 1 | 12/31/2020 |
| 11 | 1 | 1/10/2021 |
| 12 | 2 | 2/1/2021 |
| 13 | 1 | 4/1/2021 |
| 14 | 1 | 8/5/2025 |
Желаемый Результат:
| РекордИД | Идентификатор клиента | Дата создания | #Возможности |
|---|---|---|---|
| 777 | 1 | 1/1/2021 | 1 |
| 888 | 2 | 1/1/2021 | 1 |
| 999 | 1 | 2/1/2021 | 1 |
Как вы можете видеть, таблица записей содержит первые 3 столбца желаемых выходных данных, а столбец «#Возможности» создается путем подсчета количества возможностей, которые возникают после создания записи для данного клиента.
Две ключевые вещи, которые следует отметить в этой логике:
- Учитывайте возможности только в том случае, если они возникают в течение 6 месяцев с момента записи.
- Если для клиента создана другая запись, учитывайте возможности только для самой последней записи.
Более конкретно, OppID = 11 будет зачислен на RecordID = 777; с 12 по 888; и с 13 по 999. 10 и 14 не будут зачислены ни на один из RecordID.
Я написал приведенный ниже код, который не учитывает #2 выше:
CREATE TABLE #Records
(
RecordID int
, CustomerID int
, CreateDate Date
)
INSERT INTO #Records
VALUES
(777, 1, '2021-01-01')
, (888, 2, '2021-01-31')
, (999, 1, '2021-02-01')
CREATE TABLE #Opportunities
(
OppID int
, CustomerID int
, OppCreateDate Date
)
INSERT INTO #Opportunities
VALUES
(10, 1, '2020-12-31')
, (11, 1, '2021-01-10')
, (12, 2, '2021-02-01')
, (13, 1, '2021-04-01')
, (14, 1, '2025-08-25')
select *
from #Records
select *
from #Opportunities
select rec.*
, (select count(*)
from #Opportunities opp
where rec.CustomerID=opp.CustomerID
and rec.CreateDate<=opp.OppCreateDate --record happened on the same day or before the opportunity
and datediff(month,rec.CreateDate,opp.OppCreateDate) < 6 --opened and created within 6 months
) as [#Opportunities]
from #Records rec
Есть какие-либо предложения по включению #2 выше и получению желаемого результата?
Комментарии:
1. разве RecordID не мешает вам правильно подсчитать возможности для этого клиента?
2. Ну, это кажется трудным сделать, так как, похоже, нет связи между записями и возможностями — только клиенты и возможности. Ваши подсчеты всегда будут равны одному, верно?
3. Итак, позвольте мне уточнить — существует ли определение первичного ключа (идентификатор записи,идентификатор клиента)? Потому что на первый взгляд то, что вы говорите, либо плохой дизайн структуры данных, либо вы упускаете детали, которые нам нужны.
4. Я усмехнулся, когда вы упомянули плохую структуру данных. Причина, по которой я должен расколоть этот орех, заключается в этом факте! Позвольте мне попытаться объяснить это следующим образом: для клиента может быть создано много записей; для клиента может быть создано много возможностей; запись не всегда требуется для создания возможности; когда создается возможность, я хотел бы отнести это к записи, которая произошла ближе всего к дате создания этой возможности (если это было в течение 6 месяцев с даты создания [записи]. Это помогает?
5. Да, и ой, как больно, лол
Ответ №1:
Определите, какая #records строка связана со #Opportunities строкой на основе #records.CreateDate
select RecordID, CustomerID, CreateDate, count(*) cnt
from (
select r.RecordID, r.CustomerID, r.CreateDate,
row_number() over(partition by op.OppID order by r.CreateDate desc) rn
from #records r
join #Opportunities op on r.CustomerID = op.CustomerID and datediff(month, r.CreateDate, op.OppCreateDate) < 6 and r.CreateDate <= op.OppCreateDate
) t
where rn = 1
group by RecordID, CustomerID, CreateDate
ВОЗВРАТ
RecordID CustomerID CreateDate cnt
777 1 2021-01-01 1
888 2 2021-01-31 1
999 1 2021-02-01 1
Комментарии:
1. Спасибо, что попытались это сделать. Я думаю, что мой первоначальный пост тоже сбил вас с толку. Как я уже говорил выше с Критической ошибкой, единицей наблюдения является идентификатор записи, а не идентификатор клиента. Результат должен отражать то, что я написал выше в разделе «Желаемый результат» моего исходного поста. Помогает ли это усовершенствовать ваш подход? Спасибо вам за вашу помощь!!
2. Я включил это в свой первоначальный пост. Это третий стол. Пожалуйста, дайте мне знать, если вы все еще этого не видите. Спасибо!
3. Я понимаю. Не могу понять, почему 10 не будут зачислены ни на один из идентификаторов записи. Почему не 999 или 777?
4. 10 произошло до 777 и 999 (12/31/2020). Имеет ли это смысл?
5. Да, ваши данные, вы правите. Переписали ответ.
Ответ №2:
Попробуйте это:
DECLARE @Records table ( RecordID int, CustomerID int, CreateDate date );
INSERT INTO @Records VALUES
( 777, 1, '2021-01-01' ), ( 888, 2, '2021-01-31' ), ( 999, 1, '2021-02-01' );
DECLARE @Opportunities table ( OppID int, CustomerID int, OppCreateDate date );
INSERT INTO @Opportunities VALUES
( 10, 1, '2020-12-31' )
, ( 11, 1, '2021-01-10' )
, ( 12, 2, '2021-02-01' )
, ( 13, 1, '2021-04-01' )
, ( 14, 1, '2025-08-25' );
SELECT
*
FROM @Records r
OUTER APPLY (
SELECT
COUNT ( * ) AS [#Opportunities]
FROM @Opportunities AS o
WHERE
o.CustomerID = r.CustomerID
AND o.OppCreateDate >= r.CreateDate
AND DATEDIFF ( month, r.CreateDate, o.OppCreateDate ) <= 6
AND o.OppID NOT IN (
SELECT
OppID
FROM @Records AS r2
INNER JOIN @Opportunities AS o2
ON r2.CustomerID = o2.CustomerID
WHERE
r2.CustomerID = o.CustomerID
AND o2.OppCreateDate >= r2.CreateDate
AND r2.RecordID > r.RecordID
)
) AS Opps
ORDER BY
r.RecordID;
ВОЗВРАТ
---------- ------------ ------------ ----------------
| RecordID | CustomerID | CreateDate | #Opportunities |
---------- ------------ ------------ ----------------
| 777 | 1 | 2021-01-01 | 1 |
| 888 | 2 | 2021-01-31 | 1 |
| 999 | 1 | 2021-02-01 | 1 |
---------- ------------ ------------ ----------------
Комментарии:
1. Спасибо вам за вашу помощь! Я поддержал ваш пост, поскольку ваше решение, похоже, решает проблему, которую Серг также смог решить с помощью функции «раздел». Тем не менее, ваш подход больше соответствует тому, как я думал об этой конкретной проблеме. Отличная работа и еще раз спасибо!!