Aggiungere numeri di riga alle sessioni di ricarica

Durante il corso, costruirai window function usando la tabella charging nello schema Snowflake ELECTRIC_VEHICLES. Ogni record in questa tabella contiene informazioni su una sessione di "charging" per un veicolo elettrico.

Inizierai utilizzando una window function per aggiungere un numero di riga a ciascun record.

Questo esercizio fa parte del corso

Window Functions in Snowflake

Visualizza il corso

Istruzioni dell'esercizio

Completa l'istruzione SELECT per restituire i campi charging_station_location e user_id.
Usando una window function, assegna un numero di riga a ciascun record e ordina per charging_cost.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

SELECT
	-- Return the charging_station_location and user_id
    ___,
    ___,
    charging_start_time,
    charging_cost,
    
    -- Assign a row number to each record, ordering by charging_cost
    ___() ___(___ ___ ___)
    
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: Assegnare numeri di riga Exercise 2: Costruire una funzione finestra Exercise 3: Aggiungere numeri di riga alle sessioni di ricarica

Esercizio in corso

Exercise 4: Cavalcare l’onda di calore Exercise 5: Funzioni di finestra per il ranking Exercise 6: È stata una ricarica veloce?Exercise 7: Confrontare l’energia consumata Exercise 8: Partizionare i dati in una funzione finestra Exercise 9: Segui il denaro!Exercise 10: Approfondire i costi di ricarica

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: 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!