Извлечение основного домена из URL-адресов в PostgreSQL без поддоменов, портов или параметров запроса
Я работаю с запросом PostgreSQL, в котором мне нужно извлечь основной домен (включая домен второго уровня и домен верхнего уровня) из URL-адресов. Однако текущий подход с использованием REGEXP_MATCH или SUBSTRING не обрабатывает все случаи правильно. Например, ему не удается проанализировать такие URL-адреса, как {www.paylink.fr:443} (который должен быть paylink.fr) и {api.ornage.com} (который должен быть ornage.com).
SELECT
"Payments"."Id",
REGEXP_MATCH("ClientRedirectUrl", '^https?://(?:[^/]+\\.)?([^/]+)') AS "PaymentDomain",
REGEXP_MATCH("ClientUri", '^https?://(?:[^/]+\\.)?([^/]+)') AS "ClientDomain",
REGEXP_MATCH("Site", '^https?://(?:[^/]+\\.)?([^/]+)') AS "UserDomain"
FROM
"public"."Payments"
LEFT JOIN "public"."AppUsers" ON "Payments"."UserId" = "AppUsers"."Id"
LEFT JOIN "public"."Clients" ON "Payments"."ClientId" = "Clients"."ClientId"
WHERE
"PayDate" >= (NOW() + INTERVAL '-3 day')
SELECT
"Payments"."Id",
SUBSTRING("ClientRedirectUrl" FROM '^https?://(?:[^/]+\\.)?([^/]+)') AS "PaymentDomain",
SUBSTRING("ClientUri" FROM '^https?://(?:[^/]+\\.)?([^/]+)') AS "ClientDomain",
SUBSTRING("Site" FROM '^https?://(?:[^/]+\\.)?([^/]+)') AS "UserDomain"
FROM
"public"."Payments"
LEFT JOIN "public"."AppUsers" ON "Payments"."UserId" = "AppUsers"."Id"
LEFT JOIN "public"."Clients" ON "Payments"."ClientId" = "Clients"."ClientId"
WHERE
"PayDate" >= (NOW() + INTERVAL '-3 day')
Как я могу изменить этот запрос, чтобы правильно извлечь основные домены без каких-либо поддоменов, портов или параметров запроса?
Вот пример неразобранного значения:
ClientRedirectUrl: https://sonyco.it/wc-api/WC_paylink/?wc_order=673927
ClientUri: https://www.sonyco.it
Site: https://yzx80.paylink.net/
Другими словами, как извлечь доменные имена и TLD (здесь sonyco.it) из списка URL-адресов:
https://sonyco.it/wc-api/WC_paylink/?wc_order=673927
https://www.sonyco.it
https://yzx80.sonyco.it/
http://app.sonyco.it
www.sonyco.it:443
api.sonyco.it
http://www.sonyco.it:443
спасибо, но он не может извлечь какие-либо поддомены без необходимости жесткого его кодирования: https://yzx80.paylink.net/
Я не уверен, что вы имеете в виду: хотите ли вы жестко запрограммировать это или не хотите жестко запрограммировать? См. regex101.com/r/iR9vm8/1
Я стараюсь избегать жесткого кодирования конкретных поддоменов @Thefourthbird
Ответы 1
Вы можете попытаться сделать это без регулярного выражения. Строковые функции и выражения Case могут быть весьма эффективными...
WITH -- S a m p l e D a t a :
urls ( ID, URL ) AS
( Select 1, 'https://sonyco.it/wc-api/WC_paylink/?wc_order=673927' Union All
Select 2, 'https://www.sonyco.it' Union All
Select 3, 'https://yzx80.sonyco.it/' Union All
Select 4, 'http://app.sonyco.it' Union All
Select 5, 'www.sonyco.it:443' Union All
Select 6, 'api.sonyco.it' Union All
Select 7, 'http://www.sonyco.it:443' Union All
Select 8, 'https://stackoverflow.com/questions/78745653/extract-main-domain-from-urls-in-postgresql-without-subdomains-ports-or-query' Union All
Select 9, 'https://www.info-retraite.fr/portail-services/login'
),
... создайте cte, который будет форматировать URL-адреса таким же образом...
urls_2 AS -- url unification
( Select ID, URL,
SUBSTR( REPLACE( Case When Position( '/' in
SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 )
) = 0
Then SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 ) || '/'
Else SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 )
End, ':', '/'
),
1,
POSITION( '/' IN REPLACE( Case When Position( '/' in
SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 )
) = 0
Then SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 ) || '/'
Else SubStr( Case When Position('//' in URL) = 0
Then SubStr('//' || URL, Position('//' in URL) )
Else SubStr(URL, Position('//' in URL) )
End, 3 )
End, ':', '/'
)
) - 1
) as URL_2
From urls
)
/* -- R e s u l t : (urls_2)
id url_2
-- --------------------
1 sonyco.it
2 www.sonyco.it
3 yzx80.sonyco.it
4 app.sonyco.it
5 www.sonyco.it
6 api.sonyco.it
7 www.sonyco.it
8 stackoverflow.com
9 www.info-retraite.fr */
... извлечение доменов
-- M a i n S Q L :
Select ID, URL,
Case When Length(URL_2) - Length(Replace(URL_2, '.', '')) = 1
Then URL_2
When Length(URL_2) - Length(Replace(URL_2, '.', '')) = 2
Then SubStr(URL_2, Position('.' in URL_2) + 1 )
End as DOMAIN_NAME
From urls_2
/* R e s u l t :
id url domain_name
-- --------------------------------------------------------------------------------------------------------------------------- -----------------------------------
1 https://sonyco.it/wc-api/WC_paylink/?wc_order=673927 sonyco.it
2 https://www.sonyco.it sonyco.it
3 https://yzx80.sonyco.it/ sonyco.it
4 http://app.sonyco.it sonyco.it
5 www.sonyco.it:443 sonyco.it
6 api.sonyco.it sonyco.it
7 http://www.sonyco.it:443 sonyco.it
8 https://stackoverflow.com/questions/78745653/extract-main-domain-from-urls-in-postgresql-without-subdomains-ports-or-query stackoverflow.com
9 https://www.info-retraite.fr/portail-services/login info-retraite.fr */
См. скрипку здесь.
Спасибо за ответ. Не могли бы вы объяснить, что делает эта логика: Length(URL_2) - Length(Replace(URL_2, '.', '')) = 2
@sci9 sci9 Это небольшой трюк, который подсчитывает количество точек в строке. Если их 2, возьмите строку после первой точки, если она есть, возьмите строку как есть.
Прекрасный элегантный ответ - +1!
Возможно вот так
https?://(?:api\.)?((?:[^\s.]+\.)+[a-z]{2,})regex101.com/r/XWS5I4/1 Или безwww.например regex101.com/r/1Lde7N/1