Asgari şarj maliyetini belirleme

Bu şarj istasyonlarının sahibi olan şirket, faaliyet gösterdikleri her şehir için asgari bir şarj maliyeti belirlemek istiyor. Bunu yapmak için, her şehirdeki en ucuz onuncu şarj maliyetini, kullanıcıların herhangi bir oturumda araçlarını şarj ederken harcamak zorunda oldukları asgari tutar yapmak istiyorlar. Yerleşik Snowflake uzmanı olarak, her şehir için bu değeri bulup ileride her kaydı bununla karşılaştırmak senin görevin.

Bu egzersiz

Snowflake'ta Pencere Fonksiyonları

kursunun bir parçasıdır

Kursu Görüntüle

Egzersiz talimatları

charging_cost için en küçük onuncu değeri bulmak üzere bir pencere fonksiyonu kullan.
Her şehir için bir asgari değer bulunduğundan emin olmak için verileri charging_station_location ile bölümlendir (partition et).
Sonuçları minimum_cost olarak adlandır (alias ver).

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

SELECT
	charging_station_location,
	user_id,
	charging_cost,
    
    -- Find the tenth-smallest charging cost
    ___(___, ___) OVER(
      	-- Segment the data by charging_station_location
		___
    	ORDER BY charging_cost
    -- Alias the result as minimum_cost
    ) ___ ___
    
FROM ELECTRIC_VEHICLES.charging;

Kodu Düzenle ve Çalıştır

Bu egzersiz

Snowflake'ta Pencere Fonksiyonları

kursunun bir parçasıdır

IntermediárioNível de habilidade

4.9+

Kursa Ücretsiz Başlayın

Open the window to a world of possibilities with Snowflake window functions! You'll get the ball rolling by differentiating window functions from traditional functions. Then, you'll learn how to provide a row number and ranking for each record in a query. Once you've nailed down the basics, you'll put the "window" in window functions, using PARTITION BY. You'll explore how to find and use the first and last value of a certain window before wrapping up with a sneak peek into aggregation functions.

Exercise 1: Assigning row numbers Exercise 2: Building a window function Exercise 3: Adding row numbers to charging sessions Exercise 4: Riding the heat wave Exercise 5: Ranking window functions Exercise 6: Was that a fast charge?Exercise 7: Comparing energy consumed Exercise 8: Partitioning data in a window function Exercise 9: Follow the money!Exercise 10: Digging into charging costs

Time to crank it up! In this chapter, you’ll take ranking functions to the next level. You’ll start with a variant of RANK, called DENSE_RANK, which handles ties in a bit of a different way. You’ll also explore a more robust version of the functions you saw in the previous lesson using NTH_VALUE. Next, you’ll create “buckets” of data using NTILE, which is more useful than you may think. You’ll also pick up a nifty little tool called CUME_DIST to find the number of records less than or equal to a certain record in a window. You’ll wrap up the chapter with one of the most powerful applications of window functions you’ve seen so far; LAG and LEAD.

Exercise 1: Daha Fazla Sıralama Fonksiyonu Exercise 2: Doğru araçları pazarlamak Exercise 3: Şarj planlamasını optimize etme Exercise 4: Asgari şarj maliyetini belirleme

Geçerli Egzersiz

Exercise 5: NTILE ve CUME_DIST Exercise 6: Araç sahipleriyle anket Exercise 7: Şarj süresini değerlendirme Exercise 8: Araç sahipleri şarjlar arasında ne kadar süre geçiyor?Exercise 9: LAG ve LEAD Exercise 10: Geçmişe bakış!Exercise 11: Şarj cihazı verimliliğini koruma Exercise 12: Hızlı şarj yükü

You’ll start this final chapter with aggregation functions like AVG, COUNT, and SUM. You’ll compare the output of these functions to individual records in a window, as well as to perform additional calculations. After this, you’ll master the most exciting application of window functions; running and moving calculations! You’ll start by calculating running averages and totals for different metrics for electric vehicle charging. Finally, you’ll wrap up the course by generating moving totals and averages with a sliding window!

Exercise 1: Aggregate window functions Exercise 2: Engaging vehicle owners with metrics Exercise 3: Visualizing charging session duration and cost Exercise 4: Who's consuming the least?Exercise 5: Window frames Exercise 6: Are charging durations getting longer?Exercise 7: Identifying inefficient chargers Exercise 8: Moving Averages and Totals Exercise 9: Moving vs. running Exercise 10: Impact of temperature on charging rate Exercise 11: Putting it all together!Exercise 12: Congratulations!