Advanced SQL in Starburst

Cast: David Phillips and Manfred Moser

Video date: July 9, 2020

Running time:

Abstract: This video is designed to help users understand how to run more complex and comprehensive SQL queries with Starburst.

Product: Trino, Starburst Galaxy, Starburst Enterprise

Description

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
  • Window Functions - 1:28:00