Carico del caricatore rapido

Il team di Machine Learning è interessato a prevedere l’energia consumata nella prossima sessione di un caricatore, con l’obiettivo di indirizzare la potenza verso i caricatore che potrebbero essere sotto stress in futuro. Per addestrare un modello, vorrebbero che preparassi un insieme di dati che includa informazioni su utente, modello del veicolo, stazione di ricarica ed energia consumata e che "guardi" in avanti alla ricarica successiva. È il momento di mostrare cosa sai fare!

Questo esercizio fa parte del corso

Window Functions in Snowflake

Visualizza il corso

Istruzioni dell'esercizio

Recupera time_of_day, charging_rate ed energy_consumed per ogni record dalla tabella ELECTRIC_VEHICLES.charging.
Usa una funzione di finestra per "guardare avanti" e recuperare l’energia consumata nella sessione di ricarica successiva; restituisci 0 se non trovata.
Segmenta i record per charging_station_id e usa charging_start_time per creare una sequenza di record in ordine crescente.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

SELECT
    user_id,
    charging_station_id,
    charging_start_time,
    
    -- Retrieve the time_of_day, charging_rate and energy_consumed fields
	___,
    ___,
    ___,
	
    -- "Look ahead" to the energy consumed in the next session
    ___ OVER(
      
      	-- Segment the records by charging station and sequence
      	-- records by the start time of the charge
        ___
        ___
      
    ) AS next_session_energy_consumed

FROM ELECTRIC_VEHICLES.charging;

Modifica ed esegui il codice

Questo esercizio fa parte del corso

Window Functions in Snowflake

IntermediárioNível de habilidade

4.9+

Inizia il corso 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: Altre funzioni di ranking Exercise 2: Promuovere i veicoli giusti Exercise 3: Ottimizzare la programmazione della ricarica Exercise 4: Impostare un costo minimo di ricarica Exercise 5: NTILE e CUME_DIST Exercise 6: Sondaggio tra proprietari di veicoli Exercise 7: Valutare la durata di ricarica Exercise 8: Quanto tempo passa tra una ricarica e l'altra per i proprietari dei veicoli?Exercise 9: LAG e LEAD Exercise 10: Uno sguardo al passato!Exercise 11: Mantenere l’efficienza dei caricabatterie Exercise 12: Carico del caricatore rapido

Esercizio in corso

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!