Advanced SQL in Starburst

Hosts: David Phillips and Manfred Moser
Video date: 9 July 2020
Running time: 2h 14min

This training session is geared towards helping users understand how to run more complex and comprehensive SQL queries with Starburst. Delivered by David Phillips, this session covers the following topics:

• Using JSON and other complex data types
• Advanced aggregation techniques
• Window functions
• Array and map functions
• Lambda expressions
• Many other SQL functions and features

Detailed topics with timestamps

Clicking the timestamp links below will take you to YouTube, or you can scrub to that timestamp in the video player above.

• Welcome - 0:00
• General SQL Features - 9:45
  • Format function - 9:57
  • Case expressions - 12:48
  • Searched case expression - 13:34
  • IF expression - 14:24
  • TRY expression - 15:26
  • Lambda expression overview
• Using JSON - 18:36
  • JSON data type - 19:13
  • Extraction using JSONPath - 23:11
  • Casting from JSON - 26:01
  • Parting casting from JSON - 27:07
  • Formatting as JSON - 29:40
• Advanced Aggregation Techniques - 32:50
  • Counting distinct items - 33:06
  • Approximate percentiles - 36:02
  • Associated max value - 37:59
  • Associated max value using a row type - 38:39
  • Pivoting with conditional counting - 40:58
  • Pivoting with filtering - 42:08
  • Pivoting averages - 42:57
  • Aggregating a complex expression - 43:55
  • Aggregating into an array - 45:36
  • Aggregating into a lambda - 46:28
  • Order-insensitive checksums - 50:07
  • ROLLUP with single - 52:32
  • ROLLUP with multiple - 53:24
  • CUBE - 55:52
  • GROUPING SETS - 59:28
• Array and Map Functions - 1:07:47
  • Creating arrays - 1:08:40
  • Accessing array elements - 1:09:07
  • Sorting arrays - 1:11:25
  • Matching elements - 1:13:26
  • Filtering elements - 1:14:57
  • Transforming elements - 1:16:25
  • Converting arrays to strings - 1:17:44
  • Computing array product - 1:19:45
  • Unnesting an array - 1:22:16
  • Unnesting an array with ordinality - 1:23:06
  • Creating maps - 1:23:44
  • Accessing map elements - 1:25:20
  • Unnesting a map - 1:26:16
• Window Functions - 1:28:00
  • Window function overview - 1:28:58
  • Row numbering - 1:30:04
  • Row numbering order - 1:30:49
  • Row numbering with limit - 1:31:39
  • Rank - 1:32:30
  • Rank with ties - 1:33:11
  • Dense rank with ties - 1:33:54
  • Ranking without ordering - 1:34:20
  • Row numbering without ordering - 1:34:40
  • Assigning rows to buckets - 1:36:07
  • Percentage ranking - 1:37:12
  • Partitioning - 1:37:53
  • Partitioning on the same value - 1:39:26
  • Accessing leading and trailing rows - 1:40:12
  • Accessing leading and trailing rows with nulls - 1:42:56
  • Accessing leading and trailing rows without nulls - 1:43:56
  • Window frames - 1:45:13
  • Accessing the first value - 1:47:05
  • Accessing the last value - 1:47:35
  • Accessing the Nth value - 1:47:49
  • Window frame ROWS vs RANGE - 1:48:15
  • Rolling and total sum - 1:50:39
  • Partition sum - 1:52:00