Yeni Başlayanların Bilmesi Gereken 16 SQL Tekniği

1'den 10'a kadar bir ölçekte veri depolama becerileriniz ne kadar iyi?

7/10'un üzerine çıkmak ister misiniz? O halde bu yazı tam size göre.

SQL'iniz ne kadar iyi? Bir an önce iş görüşmesine hazırlanmak ister misiniz?

Bu blog yazısında en karmaşık veri ambarı SQL teknikleri ayrıntılı olarak açıklanmaktadır. Bu konuyla ilgili birkaç düşünceyi karalamak için BigQuery standart SQL lehçesini kullanacağım.

1. Artımlı tablolar ve BİRLEŞTİRME

Tablonun güncellenmesi önemlidir. Gerçekten önemli. İdeal durum, BİRİNCİL anahtar, benzersiz tamsayılar ve otomatik artış olan işlemlerinizin olduğu zamandır. Bu durumda tablo güncellemesi basittir:

Modern veri ambarlarında normalleştirilmemiş yıldız şeması veri kümeleriyle çalışırken durum her zaman böyle değildir. SQL ile oturumlar oluşturmanız ve/veya verilerin yalnızca bir kısmıyla veri kümelerini aşamalı olarak güncellemeniz görevlendirilebilir. transaction_id mevcut olmayabilir ancak bunun yerine benzersiz anahtarın bilinen en son transaction_id (veya zaman damgasına) bağlı olduğu veri modeliyle uğraşmanız gerekecektir. Örneğin, last_online veri kümesindeki user_id bilinen en son bağlantı zaman damgasına bağlıdır. Bu durumda mevcut kullanıcıları update ve yenilerini insert isteyebilirsiniz.

BİRLEŞTİRME ve artımlı güncellemeler

MERGE'i kullanabilir veya işlemi iki eyleme bölebilirsiniz. Biri mevcut kayıtları yenileriyle güncellemek için, diğeri ise çıkmayan tamamen yeni kayıtları eklemek için (LEFT JOIN durumu).

MERGE genellikle ilişkisel veritabanlarında kullanılan bir ifadedir. Google BigQuery MERGE Komutu, Veri İşleme Dili (DML) ifadelerinden biridir. Genellikle tek bir ifadede üç ana işlevi atomik olarak gerçekleştirmek için kullanılır. Bu işlevler UPDATE, INSERT ve DELETE'dir.

• İki veya daha fazla veri eşleştiğinde UPDATE veya DELETE deyimi kullanılabilir.
• INSERT deyimi, iki veya daha fazla verinin farklı olması ve eşleşmemesi durumunda kullanılabilir.
• UPDATE veya DELETE deyimi, verilen veriler kaynakla eşleşmediğinde de kullanılabilir.

Bu, Google BigQuery MERGE Komutunun, Google BigQuery tablolarınızdaki verileri güncelleyerek, ekleyerek ve silerek Google BigQuery verilerini birleştirmenize olanak sağladığı anlamına gelir.

Bu SQL'i düşünün:

2. Kelimeleri saymak

UNNEST() yapmak ve ihtiyacınız olan kelimenin listede olup olmadığını kontrol etmek birçok durumda yararlı olabilir, örneğin veri ambarı duyarlılık analizi:

3. IF() ifadesinin SELECT ifadesinin dışında kullanılması

Bu bize bazı kod satırlarını kaydetme ve kod açısından daha anlamlı olma fırsatı verir. Normalde bunu bir alt sorguya koymak ve Where cümleciğine bir filtre eklemek istersiniz ancak bunun yerine şunu yapabilirsiniz:

Bölümlenmiş tablolarla nasıl KULLANILMAYACAĞINA başka bir örnek. Bunu yapma . Bu kötü bir örnek çünkü eşleşen tablo son ekleri muhtemelen dinamik olarak (tablonuzdaki bir şeye göre) belirlendiğinden , tam tablo taraması için ücretlendirileceksiniz.

HAVING yan tümcesinde ve AGGREGATE işlevlerinde de kullanabilirsiniz.

4. GRUPLAMAYA GÖRE GRUPLAMA'yı kullanma

ROLLUP işlevi birden çok düzeyde toplama gerçekleştirmek için kullanılır. Bu, boyut grafikleriyle çalışmanız gerektiğinde kullanışlıdır.

Aşağıdaki sorgu, Where cümlesinde belirtilen işlem türüne (is_gift) göre günlük toplam kredi harcamasını döndürür ve ayrıca her gün için toplam harcamayı ve mevcut tüm tarihlerdeki toplam harcamayı da gösterir.

5. Tabloyu JSON'a dönüştürün

Tablonuzu, her kaydın iç içe geçmiş bir dizinin öğesi olduğu JSON nesnesine dönüştürmeniz gerektiğini düşünün. to_json_string() fonksiyonunun kullanışlı olduğu yer burasıdır:

Daha sonra onu her yerde kullanabilirsiniz: tarihler, pazarlama hunileri, endeksler, histogram grafikleri vb.

6. PARTITION BY'yi kullanma

user_id , date ve total_cost sütunları verilmiştir. HER tarih için, tüm satırları korurken HER müşterinin toplam gelir değerini nasıl gösterirsiniz? Bunu şu şekilde başarabilirsiniz:

7. Hareketli ortalama

Çoğu zaman BI geliştiricilerine raporlarına ve harika kontrol panellerine hareketli bir ortalama ekleme görevi verilir. Bu 7, 14, 30 günlük/ay ve hatta yıllık MA çizgi grafiği olabilir. Peki bunu nasıl yapacağız?

8. Tarih dizileri

Kullanıcıları elde tutma konusunda çalıştığınızda veya bazı veri kümelerinde eksik değerler (ör. tarihler) olup olmadığını kontrol etmek istediğinizde gerçekten kullanışlı olur. BigQuery'nin GENERATE_DATE_ARRAY adında bir işlevi vardır:

9. Satır_numarası()

Bu, verilerinizden en son bilgileri (en son güncellenen kayıt vb.) almak ve hatta kopyaları kaldırmak için kullanışlıdır:

10. NTILE()

Başka bir numaralandırma işlevi. Bir mobil uygulamanız varsa Login duration in seconds izlemek gerçekten yararlıdır. Örneğin, Uygulamamı Firebase'e bağladım ve kullanıcılar login yaptığında bunun ne kadar sürdüğünü görebiliyorum.

Bu işlev, satır sıralamasına göre satırları constant_integer_expression gruplarına böler ve her satıra atanan 1 tabanlı grup numarasını döndürür. Demetlerdeki satır sayısı en fazla 1 farklılık gösterebilir. Geriye kalan değerler (satır sayısının geri kalan kısmı demetlere bölünür), demet 1'den başlayarak her demet için bir tane dağıtılır. constant_integer_expression NULL, 0 veya negatif olarak değerlendirilirse, bir hata verilmektedir.

11. Sıra / yoğun_sıralama

Bunlara numaralandırma fonksiyonları da denir. Ben varsayılan sıralama işlevi olarak DENSE_RANK kullanma eğilimindeyim çünkü bu, RANK yapabileceği bir sonraki mevcut sıralamayı atlamaz. Ardışık sıra değerlerini döndürür. Sonuçları farklı gruplara ayıran bir bölümle kullanabilirsiniz. Her bölümdeki satırlar aynı değerlere sahipse aynı dereceleri alır. Örnek:

Ürün fiyatlarına başka bir örnek:

12. Döndür / döndür

Pivot, satırları sütunlara dönüştürür. Bütün yaptığı bu. Unpivot tam tersini yapar.

13. İlk_değer / son_değer

Bu, söz konusu bölümdeki ilk/son değere karşı her satır için bir delta elde etmeye yardımcı olan başka bir yararlı işlevdir.

14. Bir tabloyu yapı dizisine dönüştürün ve bunları UDF'ye aktarın

Bu, her satıra veya tabloya karmaşık bir mantıkla kullanıcı tanımlı bir işlev (UDF) uygulamanız gerektiğinde kullanışlıdır. Tablonuzu her zaman TYPE STRUCT nesnelerinden oluşan bir dizi olarak düşünebilir ve ardından bunların her birini UDF'ye aktarabilirsiniz. Bu sizin mantığınıza bağlıdır. Örneğin, satın alma işleminin sona erme sürelerini hesaplamak için kullanıyorum:

Benzer şekilde UNION ALL kullanmaya gerek kalmadan tablolar oluşturabilirsiniz. Örneğin, birim testleri için bazı test verileriyle dalga geçmek için kullanıyorum. Bu şekilde editörünüzde Alt + Shift + Down tuşlarını kullanarak bunu çok hızlı yapabilirsiniz.

15. TAKİP ETME VE SINIRSIZ TAKİP ETME özelliğini kullanarak etkinlik hunileri oluşturma

İyi bir örnek pazarlama hunileri olabilir. Veri kümeniz aynı türde sürekli olarak tekrarlanan olaylar içerebilir, ancak ideal olarak her olayı farklı türden bir sonraki olayla zincirlemek istersiniz. Bir huni veri kümesi oluşturmak için etkinlikler, satın almalar vb. gibi bir şeyin listesini almanız gerektiğinde bu yararlı olabilir. PARTITION BY ile çalışmak size, her bölümde kaç tane olursa olsun, aşağıdaki tüm olayları gruplama fırsatı verir.

16. Regexp

Yapılandırılmamış verilerden (örneğin döviz kurları, özel gruplamalar vb.) bir şey çıkarmanız gerekiyorsa bunu kullanırsınız.

Regexp kullanarak döviz kurlarıyla çalışma

Döviz kurları verileriyle ilgili bu örneği düşünün:

Regexp kullanarak Uygulama sürümleriyle çalışma

Bazen uygulamanızın ana , yayın veya mod sürümlerini almak ve özel bir rapor oluşturmak için regexp kullanmak isteyebilirsiniz:

Çözüm

SQL, verileri işlemeye yardımcı olan güçlü bir araçtır. Dijital pazarlamadaki bu SQL kullanım örneklerinin sizin için yararlı olacağını umuyoruz. Bu gerçekten kullanışlı bir beceridir ve birçok projede size yardımcı olabilir. Bu SQL parçacıkları hayatımı çok kolaylaştırdı ve iş yerinde neredeyse her gün kullanıyorum. Dahası, SQL ve modern veri ambarları veri bilimi için temel araçlardır. Sağlam lehçe özellikleri, verilerin kolaylıkla modellenmesine ve görselleştirilmesine olanak tanır. SQL, veri ambarlarının ve iş zekası profesyonellerinin kullandığı dil olduğundan, onlarla veri paylaşmak istiyorsanız mükemmel bir seçimdir. Piyasadaki hemen hemen her veri ambarı/lake çözümüyle iletişim kurmanın en yaygın yoludur.

İlk olarak datamike tarafından mydataschool.com'da yayınlandı

Mike tutkulu ve dijitale odaklanmış, büyük bir motivasyon ve coşkuya sahip, dijital pazarlamanın tüm karışımının ortaya çıkardığı zorlukları seven bir kişidir. İngiltere'de yaşıyor, 2015 yılında Newcastle Üniversitesi'nden MBA derecesini tamamladı.