#sql #sql-server #tsql
Вопрос:
В телефонной сети есть абоненты: from_number и приемники: to_numbers . Мне нужен список всех отдельных австралийских номеров из from_number to_numbers столбцов/ переменных и. Список должен действовать как фильтр и указывать direction , как в «от» или «до». Если число повторилось в любом месте в обоих полях, то есть в любом месте в поле from_number и в любом месте to_numbers поля, то это должно быть указано как both .
У меня есть пример кода: желаемый результат, а также то, что я пробовал:
create table calls
(
call_date date,
from_number varchar(16),
to_number varchar(16)
);
INSERT calls
VALUES ('2020-07-03','619876544', '022445545'),
( '2020-07-03','61123456', '642445544'),
('2020-07-03','03123456', '61333333'),
('2020-07-03','65123456', '619876543'),
('2020-07-04','642445545', '61123456'),
( '2020-07-04','61333333', '632445555'),
('2020-07-04','642445545', '049876543'),
('2020-07-03','649876543', '61333333'),
( '2020-07-04','612445555', '022445545');
SELECT DISTINCT
callers.*,
FROM calls callers
желаемый результат с указанием направления : к, от, оба:
create table calls
(
call_date date,
from_number varchar(16),
to_number varchar(16),
direction varchar (8)
);
INSERT calls
VALUES ('2020-07-03','619876544', '022445545', 'from'),
( '2020-07-03','61123456', '642445544', 'both'),
('2020-07-03','03123456', '61333333', 'both'),
('2020-07-03','65123456', '619876543', 'to'),
('2020-07-04','642445545', '61123456', 'both'),
( '2020-07-04','61333333', '632445555', 'both'),
('2020-07-04','642445545', '049876543', 'none'),
('2020-07-03','649876543', '61333333', 'both'),
( '2020-07-04','612445555', '022445545', 'from');
SELECT DISTINCT
callers.*
FROM calls callers
Как я могу увидеть, повторилось ли число где-либо в [From_Number] И [To_Number] и задать направление для обоих в этом случае? Если это произошло только в номере «от», то оно должно быть установлено в «от», если это произошло только в номере «до», то должно быть установлено в : to
что я пытался:
create table calls
(
call_date date,
from_number varchar(16),
to_number varchar(16),
direction varchar (8)
);
INSERT calls
VALUES ('2020-07-03','619876544', '022445545', 'to'),
( '2020-07-03','61123456', '642445544', 'both'),
('2020-07-03','03123456', '61333333', 'both'),
('2020-07-03','65123456', '619876543', 'to'),
('2020-07-04','642445545', '61123456', 'both'),
( '2020-07-04','61333333', '632445555', 'both'),
('2020-07-04','642445545', '049876543', '0'),
('2020-07-03','649876543', '61333333', 'both'),
( '2020-07-04','612445555', '022445545', 'from');
SELECT DISTINCT
callers.call_date
,callers.[From_Number]
,callers.[To_Number]
,DIRECTION = ISNULL(recipients.From_Number, 'both')
--,CASE
--WHEN LEFT (callers.[From_Number] ,2) = '61' then 'from'
--WHEN LEFT (callers.[To_Number] ,2) = '61' then 'to' ELSE '0' END AS direction1
FROM calls callers
LEFT JOIN calls recipients
ON recipients.from_number = callers.to_number;
В ответ на предыдущий вопрос, скорее всего, это будет что-то вроде этого.
Как я могу установить:
,DIRECTION = ISNULL(recipients.From_Number, 'both')
таким образом, он делает то, что мне нужно, и НАПРАВЛЕНИЕ может быть отфильтровано для / от / обоих ?
Комментарии:
1. Что означает это значение
0для направления? Я на самом деле не понимаю, чего вы на самом деле здесь добиваетесь, хотя, если честно; то, что вы описываете, вы хотите, а затем ожидаемые результаты на самом деле не совпадают. Например, если вам нужен список всех чисел в ваших данных, почему ваши ожидаемые результаты содержат 2 столбца для указанных чисел, а не один?2. почему это
'2020-07-03','619876544', '022445545', 'to'необходимо учитыватьTO?3. Что вы хотите показать, если, например, число » От «встречается только в «От», но число «Кому» также встречается в «От»?
4. @Lamu Я изменил опечатки, спасибо
5. @Charlieface — этого не произойдет, потому что это подтвержденные направленные телефонные звонки. Так что австралийский номер всегда будет только в » от » или «до». Но я хочу, чтобы они были помечены как при появлении номера в любое время в вызовах » От » или «кому».
Ответ №1:
Это хорошее место, чтобы отказаться от использования apply :
select v.number,
(case when min(v.direction) = max(v.direction)
then min(v.direction)
else 'both'
end),
count(*)
from calls c cross apply
(values (from_number, 'from'),
(to_number, 'to')
) v(number, direction)
group by number;
Комментарии:
1. Привет @Гордон Линофф спасибо за ваше предложение. Я не понимаю, однако, откуда должно исходить направление min / max в качестве переменного направления со значениями от/ до, и оба должны быть созданы ? Я мог только подумать, что можно было бы оценить числа и утверждать, что 61 число-это минимум, а 64 числа-максимум. Но что, если у меня там коды стран ниже 61. Так что я не уверен, как это может сработать.
2. @Anna . . . Это использует логику, указанную вами в вопросе: «Если это произошло только в номере»от», то оно должно быть установлено в «от», если это произошло только в номере «до», то должно быть установлено в «до». Я добавил «все остальные
both«.
Ответ №2:
Учитывая , что вы говорите, что есть только одна сторона , которая начинается с 61 , и это те, которые вы хотите посчитать, чтобы установить, в каком направлении были сделаны вызовы, вы можете рассчитать, какое число использовать внутри APPLY , а затем использовать функции окна над этим:
SELECT c.*,
CASE WHEN COUNT(CASE WHEN from_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
THEN
CASE WHEN COUNT(CASE WHEN to_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
THEN 'both'
ELSE 'from'
END
ELSE
CASE WHEN COUNT(CASE WHEN to_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
THEN 'to'
ELSE 'none'
END
END
FROM calls c
CROSS APPLY (VALUES
(CASE WHEN from_number LIKE '61%' THEN from_number
WHEN to_number LIKE '61%' THEN to_number
END)
) v(num);