J'ai besoin d'aide pour analyser l'URL Web à l'aide de BigQuery. Vous devez supprimer la chaîne / le texte après la dernière barre oblique «/» et renvoyer l'URL. La longueur de l'URL d'entrée peut varier enregistrement par enregistrement. Si l'URL d'entrée n'a pas de chaîne / texte après l'adresse du domaine, elle doit renvoyer l'URL telle quelle.
Voici quelques exemples.
Saisir l'URL Web
https://www.stackoverflow.com/questions
Résultat attendu
J'ai essayé d'utiliser la fonction SPLIT qui convertit la chaîne URL en ARRAY et calcule la taille du tableau en utilisant ARRAY_LENGTH. Cependant, il ne couvre pas tous les différents scénarios que j'ai mentionnés ci-dessus.
Veuillez indiquer comment résoudre ce problème? utiliser SQL standard dans BigQuery?
4 Réponses :
Je pense qu'une expression case permet de remplir le vide:
select (case when url like '%//%/%' then regexp_replace(url, '/[^/]+$', '')
else url
end)
from (select 'https://www.stackoverflow.com/questions/ask' as url union all
select 'https://www.stackoverflow.com/questions' as url union all
select 'https://www.stackoverflow.com' as url
) x;
Voici ci-dessous pour BigQuery Standard SQL
Row url value 1 https://www.stackoverflow.com https://www.stackoverflow.com 2 https://www.stackoverflow.com/questions https://www.stackoverflow.com 3 https://www.stackoverflow.com/questions/ask https://www.stackoverflow.com/questions 4 https://stackoverflow.com/questions/ask/some-text https://stackoverflow.com/questions/ask
que vous pouvez tester, jouer avec ci-dessus en utilisant des exemples de données de votre question comme dans l'exemple ci-dessous
#standardSQL WITH `project.dataset.table` AS ( SELECT 'https://www.stackoverflow.com' url UNION ALL SELECT 'https://www.stackoverflow.com/questions' UNION ALL SELECT 'https://www.stackoverflow.com/questions/ask' UNION ALL SELECT 'https://stackoverflow.com/questions/ask/some-text' ) SELECT url, REPLACE(REGEXP_REPLACE(REPLACE(url, '//', '\\'), r'/[^/]+$', ''), '\\', '//') value FROM `project.dataset.table`
avec résultat
#standardSQL SELECT url, REPLACE(REGEXP_REPLACE(REPLACE(url, '//', '\\'), r'/[^/]+$', ''), '\\', '//') FROM `project.dataset.table`
Vous pouvez utiliser un simple REGEXP_REPLACE pour le dernier "/" et les chaînes suivantes.
SELECT REGEXP_REPLACE(url, r"([^/])/[^/]*$", "\\1") FROM (SELECT 'https://www.stackoverflow.com/questions/ask' as url UNION ALL SELECT 'https://www.stackoverflow.com/questions' as url UNION ALL SELECT 'https://www.stackoverflow.com' as url )
Remarque : \\ 1 ( premier groupe de capture) représentent le caractère juste avant "/", nous devons considérer le caractère pour éviter de correspondre avec "//".
Résultat du test:
Bravo! J'ai découvert qu'il y avait un meilleur moyen, mais j'ai raté cette astuce. Bravo!
Vous avez oublié de mentionner - au lieu de "\\ 1" - vous pouvez utiliser r "\ 1"
@MikhailBerlyant - Merci pour votre aide!
@kshaikh - Bien sûr, envisagez également de voter pour des réponses utiles: o)
Fournissez une solution JavaScript UDF. Non pas parce que c'est mieux pour ce scénario, mais c'est toujours votre dernier espoir lorsque les choses deviennent vraiment compliquées.
(De plus, je tiens à souligner que des doubles barres obliques peuvent exister dans une URL comme: https://www.stackoverflow.com//questions//ask , pour gérer ce qui peut nécessiter une logique supplémentaire codée en JavaScript) p >
#standardSQL
CREATE TEMP FUNCTION
remove_last_part_from_url(url STRING)
RETURNS STRING
LANGUAGE js AS """
var last_slash = url.lastIndexOf('/');
var first_double_slash = url.indexOf('//');
if (first_double_slash != -1
&& last_slash != -1
&& last_slash != first_double_slash + 1) {
return url.substr(0, last_slash);
}
return url;
""" ;
SELECT remove_last_part_from_url(url)
FROM (SELECT 'https://www.stackoverflow.com/questions/ask' as url UNION ALL
SELECT 'https://www.stackoverflow.com/questions' as url UNION ALL
SELECT 'https://www.stackoverflow.com//questions' as url UNION ALL -- double slash after https://
SELECT 'https:/invalid_url' as url UNION ALL
SELECT 'https://www.stackoverflow.com' as url
)