Join Dan Sullivan for an in-depth discussion in this video Window functions: FIRST_VALUE, part of Advanced SQL for Data Scientists.
- [Narrator] We could also select a set of attributes…grouped by department and include the first value…by department in each row using something…called The First Value Function.…Let's take a look at an example.…Let's select department, last name, salary,…and now let's put in our function here.…I want to select First Value.…And I want to select the First Value…from the list of salaries and I want that list…to be over a PARTITION by department.…So that's grouping by department.…
And I want to order it by salary itself…and I would like to start at the…highest salary and go down to the lowest.…So I'm going to use the Descending, or DESC keyword here.…Now I'm going to select from staff…and let's just review this one more time.…We have the three columns that we've been using,…department, last name and salary.…And now I'm saying I want to take the first value in the…list of salaries where that list is grouped by or…partitioned by department in descending order by salary.…
So let's execute that and see what happens.…Well, what we have here is a list with departments,…
The course begins with a brief overview of SQL. Then the five major topics a data scientist should understand when working with relational databases: basic statistics in SQL, data preparation in SQL, advanced filtering and data aggregation, window functions, and preparing data for use with analytics tools.
- Data manipulation
- ANSI standards
- SQL and variations
- Statistical functions in SQL
- String, numeric, and regular expression functions in SQL
- Advanced filtering techniques
- Advanced aggregation techniques
- Windowing functions for working with ordered data sets
Skill Level Advanced
SQL: Data Reporting and Analysiswith Emma Saunders2h 16m Intermediate
1. SQL as a Tool for Data Science
SQL data definition features5m 32s
2. Basic Statistics with SQL
3. Data Munging with SQL
4. Filtering, Joins, and Aggregation
5. Window Functions and Ordered Data
6. Preparing Data for Analytics Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.