Tasarım Kodlama

SQL ile Dizeleri Ayıklamak

Sadece birkaç saat sonra çıldırmak için bir string bölme problemi ile görevlendirildiniz mi?Dışarıdaki tüm potansiyel string işlevlerinde kendinizi kaybetmek kolay olabilir.

Stringler, SQL kullanarak çalışmak için en az sevdiğim veri türüdür. Desen eşleştirme için bilmeniz gereken birçok farklı işlev ve karakter vardır. Ancak bu yazıda size gösterdiklerim doğru kullanıldığında oldukça basittir.

Bu makaledeki amacım, dizeleri sizin için daha az can sıkıcı hale getirmek. Hatalarımdan ve saatlerce süren hayal kırıklığımdan ders alın! Size baktığım problemde, çözümümde kilit rol oynayan fonksiyonlarda ve çözümün kendisinden bahsedeceğim. Makalenin sonunda, SQL kullanarak dizeleri parçalama konusunda uzman olacaksınız.

Problem:

Yakın zamanda pazarlama ekibi için bir veri modelini yeniden oluşturmak için bir problem ile karşılaştım. Mantığın büyük bir kısmı, kampanya bağlantısından utm bilgilerini çıkarmaktı. Google’da reklam verdiğinizde, aracı, yayıncı, kampanya ve kaynak gibi utm bilgileri kullanılarak özel bağlantılar oluşturulur. Bunların hepsi, şuna benzeyen bir bağlantı oluşturmak için birleştirilir:

Anahtar, kalıpları tanımlamaktır. Sadece bir model değil, tüm olası modeller. İşe yarayan bir kalıp bulduğunuzu düşünebilirsiniz. Ancak, daha sonra özel bir kullanım durumu ortaya çıkar ve bunu mantığınıza da eklemeniz gerekir. Uğraştığınız dize sütunundaki tüm kalıpları inceleyin. İnanın bana, onları bir an önce bulmak daha iyi gibi geldi. Bu kalıpları aşağıdaki gibi bir listeye yazdım:

  • utm_source “utm_source”dan sonra ve “&”den önce gelir
  • utm_medium “utm_medium”dan sonra ve “&“den önce gelir
  • utm_campaign, “utm_campaign=”den sonra gelir

Dizelerinizdeki kalıpları bulduktan sonra bunları koda çevirebilirsiniz. Mantığı kod yerine kelimelerle yazmaya başlamak çok daha kolay. Dize çıkarma ile kodunuz gerçekten dağınık ve çok hızlı olma eğilimindedir. Artık okunamayacak kadar çok…

İpucu: Kullandıkça kodunuzu yorumlayın. Bu sadece yazarken daha iyi anlamanıza yardımcı olmakla kalmayacak, aynı zamanda kodunuzu okuyan kişiye de yardımcı olacaktır.

SQL String Function

CHARINDEX()

Dizelerle uğraşırken kullanmayı sevdiğim iki farklı işlev var. Birincisi, CHARINDEX() işlevidir. Bu, onu beslediğiniz dizenin dizinini döndürür. Aradığınız dize ve içinde aradığınız sütun olmak üzere iki girdi alır. Dolayısıyla, “kampanya_link” sütununda “utm_medium” ifadesini arıyorsam, bunu şöyle yazardım:

Bu kod parçası, o sütun değerinde ‘utm_medium’un bulunduğu dizinin dizinini döndürür. Bu sütun değerinde değilse, işlev 0 döndürür.

SUBSTRING()

Kullanacağım bir sonraki işlev SUBSTRING() işlevidir. Bu, dizeyi yerleştirmek istediğiniz sütunu ve iki dizini alır – biri dizenin başlangıcı ve diğeri son için. Sağlanan sütundaki bu iki dizin arasındaki dizeyi döndürür. ‘utm_medium’ dizesinin 5 ve 10 dizinleri arasında olduğunu bilseydim, şuna benzeyen bir kod yazardım:

Bu, kampanya_bağlantısı sütunundaki 5 ve 10 dizinleri arasındaki dizeyi döndürür.

Diğer Faydalı SQL Komutları

CASE statement

CASE deyimi her zaman kullanışlıdır, ancak daha çok dizelerle uğraşırken. Verilerimizde bulduğumuz tüm tuhaf tek seferlik senaryoları ele almamıza yardımcı olur. CASE ifadeleri basit bir “bu olduğunda, o zaman şunu yap” modelini takip eder.

Ayrıca, kodunuzu potansiyel olarak bozabilecek diğer olası senaryoları işlemek için CASE ifadesine bir ELSE ekleyebilirsiniz. Ve CASE ifadenizi END ile kapatın!

LIKE operator

LIKE operatörü, bir dizideki kalıpları aramak için özellikle yararlıdır. CHARINDEX() ve SUBSTRING() gibi kalıpların çıkarılmasına yardımcı olmasa da, özellikle CASE ifadelerinde kullanışlıdır. Genellikle yazdığım vaka ifadelerinin “when” kısmını oluştururlar.

LIKE kullanırken, aradığınız bir diziyi belirtirsiniz. Aradığınız sütunda nerede olmasını istediğinize bağlı olarak özel operatörler kullanmanız gerekir.

  • _ tek bir karakteri temsil eder
  • % bir, bir veya birden çok karakteri temsil eder

Genelde aradığım dizenin başında ve sonunda % kullanırım. Bu, dize, sütun değerinde herhangi bir yerde mevcut olduğu sürece doğru olacaktır.

Aradığım dizeyi, % işaretleri ile çevrili tek tırnak içine aldığıma dikkat edin.

Çözüm

Şimdi sorunumuzu çözen ve az önce incelediğim bu fonksiyonları kullanan çözüme geçelim. Bir sürü deneme yanılma sonucu bu noktaya geldiğimi unutmayın. Doğru görünmeyen bir şey görürseniz kodunuzu gözden geçirmeye ve tekrar etmeye devam etmeniz önemlidir.
Dize çıkarma kodumu yazarken parça parça yaptım. İlk olarak, aradığım string parçasının indeksini bulmak için CHARINDEX() fonksiyonunu kullandım. Bu, utm_medium’daki “u”nun dizinini döndürür, bu nedenle bu dizenin uzunluğunu dizin işlevine eklemek istiyoruz. Bu, = işaretinden sonra dizenin dizinini döndürür.

Sonra bunu SUBSTRING() işleviyle birlikte kullanarak her şeyi dize dizininin sağına getirdim (bunu 100 işlevinde ikinci dizini yaparak yaptım).

Ardından, bir sonraki karakterin solundaki dizeyi bulmak için başka bir CHARINDEX() işlevi ekledim. Bunun için, dizinin sonu olduğu ve yalnızca 1 karakter uzunluğunda olduğu için dizinden 1 çıkarılır.

Son olarak bu mantık içerisinde bir CASE fonksiyonu ekledim. AMA, bu yalnızca, her kampanya bağlantısında utm_source, utm_medium, utm_publisher vb. olmadığını fark ettiğimde eklendi… Bu dizelerin ardından “&” gelmediğinde işleyebilecek bir vakaya ihtiyacım vardı. mantık.

İşlerin nasıl hızla karıştığını gördün mü? Mantığınıza bir CASE ifadesi eklemek, bu kodu oldukça okunamaz hale getirir, bu nedenle kodunuzu yorumlamak çok önemlidir. Onu okuyan birinin ne yaptığını anlaması oldukça uzun zaman alacaktı.
İşleri daha da karmaşık hale getirmek için, bunu yalnızca utm_medium’u kampanya_bağlantısında mevcutsa ayıklayan başka bir vaka ifadesine eklememiz gerekiyor. Bu şuna benziyor:

Sonuç

Dize çıkarma ile anahtarın sabırlı olmak olduğunu buldum. Tüm kod parçasını tek seferde yazmaya çalışmayın. Tek tek parçaları yazın, beklediğiniz gibi çalıştıklarını doğrulayın ve ardından bunları bir araya getirin. Bu daha fazla iş gibi görünse de, uzun vadede daha az zaman alır. Hemen içine atlarsanız, bir şeyler ters gitmeye mahkumdur ve kodunuzda hata ayıklamaya çalışmak için çok daha fazla zaman harcarsınız.

Kaynak :https://towardsdatascience.com/fool-proof-formula-to-extracting-strings-with-sql-9b35c57de224

Yorum yap