Menjaga efisiensi pengisi daya

Seiring bertambahnya usia pengisi daya kendaraan, efisiensinya sering menurun. Tim operasional ingin melakukan pemeliharaan proaktif dan mengidentifikasi kapan pengisi daya perlu diganti. Untuk itu, mereka ingin memiliki laporan yang melacak perubahan laju pengisian pada tiga pengisian terakhir. Mari kita lihat apakah Anda dapat membantu!

Latihan ini adalah bagian dari kursus

Fungsi Window di Snowflake

Petunjuk latihan

Gunakan fungsi window untuk menemukan selisih antara charging_rate pada sesi saat ini dan charging_rate dari 3 sesi sebelumnya.
Partisikan himpunan hasil berdasarkan charging_station_location agar hasil terpisah untuk setiap stasiun pengisian.
Tentukan urutan sesi menggunakan charging_start_time dalam urutan menaik.

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

SELECT
    user_id,
    charging_station_location,
    charging_start_time,
    charging_rate,
	
    -- Difference between current charging rate versus three sessions ago
    ___ - ___(___, ___, 0) OVER(
      	
      	-- Make sure results are partitioned by charging_station_location
        ___
      
      	-- Sessions should be ordered by when charging began
        ___
      
    ) AS change_in_charging_rate

FROM ELECTRIC_VEHICLES.charging;

Edit dan Jalankan Kode

Latihan ini adalah bagian dari kursus

Fungsi Window di Snowflake

SkillTag.level.intermediateSkillTag.label

4.9+

Mulai Kursus Gratis

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: Fungsi Peringkat Lanjutan Exercise 2: Memasarkan kendaraan yang tepat Exercise 3: Mengoptimalkan penjadwalan pengisian daya Exercise 4: Menetapkan biaya pengisian minimum Exercise 5: NTILE dan CUME_DIST Exercise 6: Mensurvei pemilik kendaraan Exercise 7: Mengevaluasi durasi pengisian daya Exercise 8: Seberapa lama pemilik kendaraan jeda antar pengisian daya?Exercise 9: LAG dan LEAD Exercise 10: Melihat ke masa lalu!Exercise 11: Menjaga efisiensi pengisi daya

Latihan Saat Ini

Exercise 12: Beban pengisi daya cepat

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!