Apakah durasi pengisian makin lama?

Tim Data Science saat ini memiliki model yang memprediksi jumlah pengisi daya yang tersedia berdasarkan rata-rata durasi pengisian kendaraan. Ini memungkinkan mereka mengirim notifikasi kepada pengguna sebelumnya saat mungkin waktu yang tepat untuk mengisi daya. Namun, tim meyakini bahwa rata-rata durasi pengisian mungkin telah berubah sejak terakhir kali mereka melatih model. Dapatkah Anda memvalidasinya untuk mereka?

Latihan ini adalah bagian dari kursus

Fungsi Window di Snowflake

Petunjuk latihan

Gunakan fungsi window untuk mencari rata-rata berjalan dari charging_duration kendaraan.
Partisikan hasil berdasarkan charging_station_location.
Urutkan jendela berdasarkan charging_start_time secara menaik.
Buat jendela rekaman agar selalu antara baris pertama dan CURRENT ROW.

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

SELECT
	user_id,
    charging_station_location,
	TO_DATE(charging_start_time),
    charging_duration,

    -- Find the running average of charging duration
    ___ OVER(
      	-- Partition the results by charging_station_location
      	___

        -- Sequence the results by charging start time in ascending order
        ORDER BY ___

        -- Create the window of records to always be between the 
      	-- first row and the current row
        ___

    ) AS running_average
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: More Ranking Functions Exercise 2: Marketing the right vehicles Exercise 3: Optimizing charging scheduling Exercise 4: Setting a minimum charging cost Exercise 5: NTILE and CUME_DIST Exercise 6: Surveying vehicle owners Exercise 7: Evaluating charging duration Exercise 8: How long are vehicle owners going between charges?Exercise 9: LAG and LEAD Exercise 10: Looking into the past!Exercise 11: Maintaining charger efficiency Exercise 12: Lightning charger load

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: Fungsi jendela agregat Exercise 2: Melibatkan pemilik kendaraan dengan metrik Exercise 3: Memvisualisasikan durasi dan biaya sesi pengisian daya Exercise 4: Siapa yang mengonsumsi paling sedikit?Exercise 5: Window frame Exercise 6: Apakah durasi pengisian makin lama?

Latihan Saat Ini

Exercise 7: Mengidentifikasi pengisi daya yang tidak efisien Exercise 8: Rata-Rata dan Total Bergerak Exercise 9: Moving vs. running Exercise 10: Dampak suhu terhadap laju pengisian daya Exercise 11: Menggabungkan semuanya!Exercise 12: Selamat!