Находит число с одинаковой последней цифрой из 2 разных столбцов

У меня есть 2 столбца телефонного номера, и мне нужно получить номера с одинаковыми последними 8 цифрами. Номера столбца A состоят из 11 цифр, а номера столбца B состоят из 9 или 10 цифр. Я попытался использовать для решения функции SUBSTR или НРАВИТЬСЯ и ЛЕВО ПРАВО, но проблема в том, что данные слишком велики, и я не могу их использовать.

select trunc(ta.timeA), ta.columnA
from table1A ta,
     tableB tb
WHERE substr(ta.columnA,-8) LIKE substr(tb.columnB,-8)
  and trunc(ta.timeA) = trunc(ta.timeB)
  AND trunc(ta.timeA) >= TO_DATE('01/01/2018', 'dd/mm/yyyy')
  AND trunc(ta.timeA) < TO_DATE('01/01/2018', 'dd/mm/yyyy') + 1
GROUP BY ta.columnA, trunc(ta.timeA)  

Пожалуйста, не помечайте спамом все СУБД. Укажите фактическую СУБД, которую вы используете.

Madhur Bhaiya 31.10.2018 10:04

Вы используете MySQL, MS SQL Server или Oracle?

jarlh 31.10.2018 10:04

Oracle <> SQL Server <> MySQL. Это явно не SQL Server, поскольку trunc и TO_DATE не являются допустимыми функциями в T-SQL. Пожалуйста, не помечайте нерелевантные СУБД.

Larnu 31.10.2018 10:04

Какую технологию SQL-сервера вы используете? Неужто не 3 разных?

Dragonthoughts 31.10.2018 10:04

Совет сегодня: переключитесь на современный, явный синтаксис JOIN. Легче писать (без ошибок), легче читать (и поддерживать) и при необходимости проще преобразовать во внешнее соединение.

jarlh 31.10.2018 10:05

Зачем использовать GROUP BY, если агрегатные функции не задействованы? Вы пытаетесь сделать ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ?

jarlh 31.10.2018 10:07

Я использую разработчика SQL, но я тоже могу использовать mysql

Tuan Anh Tran 31.10.2018 10:07

Этот запрос не будет выполняться ни в MySQL, ни в Oracle. Вам остается выбирать.

jarlh 31.10.2018 10:08

Разработчик SQL - IDE .. Не RDBMS .. Сообщите, какой RDBMS вы используете

dwir182 31.10.2018 10:10

К какому типу данных относятся columnS и column? Вы относитесь к ним как к строкам, но вы говорите, что они содержат числа.

Thorsten Kettner 31.10.2018 10:10

Вы выбираете только столбцы из таблицы A. Это нарочно? Казалось бы, естественно показать и столбец A, и столбец B. Вы хотите выбрать только те строки из таблицы A, которые совпадают с таблицей B? Это было бы лучше сделать с помощью WHERE EXISTS вместо соединения.

Thorsten Kettner 31.10.2018 10:13

Мой rdbms - это оракул

Tuan Anh Tran 31.10.2018 10:14

Так в чем же проблема с вашим запросом? Вы говорите, что это правильно, но с большими таблицами становится слишком медленно?

Thorsten Kettner 31.10.2018 10:15

Мне просто нужен столбец A, поэтому все, что я хочу получить, это столбец A.

Tuan Anh Tran 31.10.2018 10:15

Почему последние восемь цифр имеют для вас особое значение? Похоже, вы должны хранить их в отдельном столбце, если они имеют значение отдельно от числа в целом.

Thorsten Kettner 31.10.2018 10:17

@ Торстен Кеттнер: Моя проблема в том, что запрос слишком медленный, и я просто хочу найти самый быстрый способ получить результаты. номер телефона был изменен с 11 номеров на 10, и они попросили меня вернуть старый номер из новых данных, которые были до 15 октября, но ничего не изменили

Tuan Anh Tran 31.10.2018 10:35

Опять же: какой тип данных? Я полагаю, что это строковый тип, потому что номера телефонов могут содержать строки (например, «+49401234567» или «+ 45-33-1234-123»)? А в вашей системе последние восемь цифр / букв телефонного номера имеют особое значение? Это странно. Как придешь? Возможно, вы имеете дело с местными номерами, состоящими только из восьмизначных телефонных номеров плюс код города из одной, двух или трех цифр? Объясните, пожалуйста, как именно отформатированы числа.

Thorsten Kettner 31.10.2018 10:39

@Thorsten Kettner: да, это строковый тип и был отфильтрован весь префикс +. в прошлый раз префикс был 012x, а теперь его 07x, и все последние 8 цифр остались прежними. И да, это только местный номер телефона. без символов внутри

Tuan Anh Tran 31.10.2018 10:44
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
18
262
3

Ответы 3

Вы можете попробовать ниже, используя = operator вместо like operator

как вы хотите сопоставить последние 2 цифры

select trunc(ta.timeA),ta.columnA 
from table1A ta inner join tableB tb 
on substr(ta.columnA,-8) = substr(tb.columnB,-8)
and trunc(ta.timeA) = trunc(ta.timeB)
AND trunc(ta.timeA) >= TO_DATE('01/01/2018', 'dd/mm/yyyy')
AND trunc(ta.timeA) < TO_DATE('01/01/2018', 'dd/mm/yyyy') + 1 
GROUP BY ta.columnA, trunc(ta.timeA)  

@TuanAnhTran Вы должны изменить структуру своей базы данных так, чтобы столбцы, которые вам нужно сравнивать, можно было использовать напрямую, без использования дорогостоящих строковых операций.

Tim Biegeleisen 31.10.2018 10:11

Было бы легче помочь, если бы вы были более конкретны в своей среде SQL. Ниже приведены некоторые советы по этому запросу, которые будут применяться в большинстве сред.

При работе с большими наборами данных производительность становится еще более критичной, и небольшие изменения в технике могут иметь большое влияние.

Например: Как правило, используется для частичного совпадения с подстановочными знаками, разве вы не имеете в виду равных? Подобное медленнее, чем равно, если вы не используете подстановочные знаки, я рекомендую искать равенство.

Кроме того, вы сначала начинаете с соединения (кросс-декартово произведение), но затем ваше предложение where определяет очень конкретные критерии соответствия (совпадающие поля времени), если вам нужно поле совпадения времени, сделайте его частью соединения таблицы, это уменьшит количество результатов соединения, которое значительно сократит набор данных, к которому затем необходимо применить другие критерии.

Кроме того, расчет значений даты в предложении where выполняется медленно. Лучше установить параметры @fromDate и @toDate перед запросом, а затем использовать их в предложении where в качестве литералов, которые не нужно вычислять для каждой строки.

Вы хотите выбрать из tableA, так что сделайте это. Не присоединяйся. Вам нужно выбрать только те строки tableA, которые совпадают с tableB. Так что поместите предложение EXISTS в предложение WHERE.

select trunc(timea), columna
from table1a ta
where trunc(timea) >= date '2018-01-01'
  and trunc(timea) < date '2018-01-02'
  and exists
  (
    select *
    from tableb tb
    where trunc(tb.timeb) = trunc(ta.timea)
    and substr(tb.columnb, -8) = substr(ta.columna, -8)
  )
order by trunc(timea), columna;

Чтобы это работало быстро, создайте следующие индексы:

create idxa on tablea( trunc(timea), substr(columna, -8) );
create idxb on tableb( trunc(timeb), substr(columnb, -8) );

Однако я не понимаю, почему вы так стремитесь к быстрому бегу. Вы хотите сохранить все данные как есть и запускать запрос снова и снова? Должно быть лучшее решение. Первое, что приходит в голову, - это разделить код города и номер на два отдельных столбца.

Обновлено: Еще быстрее, чем предлагаемый idxa, должен быть индекс покрытия для tableA:

create idxa on tablea( trunc(timea), substr(columna, -8), columna );

Здесь СУБД может работать только с индексом и не имеет доступа к таблице. Так что на тот случай, если приведенное выше было все еще слишком медленным для вас, вы можете попробовать с этим измененным индексом.

И, как отметил Алекс Пул в комментариях ниже, это должно быть

where trunc(timea) = date '2018-01-01'

только если диапазон, на который вы смотрите, всегда составляет один день, как в примере.

Вам действительно не нужен trunc(ta.timea) для сравнения диапазонов (я понимаю, что это то, что делал OP, а не то, что вы добавляли); либо использовать исходное значение (чтобы можно было использовать существующий индекс, а idxa тогда не нужен), либо, по сравнению с одним днем, сохранить усечение, но оно должно быть только равным этой фиксированной дате?

Alex Poole 31.10.2018 11:20

@ Торстен Кеттнер: Спасибо, сэр

Tuan Anh Tran 31.10.2018 11:26

@Alex Poole: Да, я мог бы переключиться с trunc(ta.timea) на ta.timea для диапазона, но это было бы медленнее, так как я хочу, чтобы СУБД использовала указатель функций, который я предложил. Вы правы, что это должен быть простой trunc(ta.timea) = date '2018-01-01', когда рассматриваемый диапазон всегда составляет один день, как в примере. Спасибо, что указали на это.

Thorsten Kettner 31.10.2018 12:04

Честно говоря, я думал, что idxa не нужен, и предполагал, что индекс существует только на необработанную дату; но особенно с вашей модификацией это имеет смысл. При виде такого усика меня дергает * 8-)

Alex Poole 31.10.2018 12:38

Другие вопросы по теме