Meerdere common table expressions

Gegevenstypen en functies in Snowflake

Jacob Roach

Field Data Engineer

Common table expressions (CTE's)

WITH seniors AS ( 

    SELECT 
        student_id, 
        first_name 
    FROM STUDENTS.personal_info 
    WHERE graduation_year = 2025

)


SELECT
    ...
FROM seniors

CTE's slaan tijdelijk de resultaten op van een query om later in een andere query te gebruiken

Helpt queries te organiseren
Leesbaarder en modulairder
Niet beperkt tot één CTE

Meerdere CTE's definiëren

Meerdere tijdelijke resultaten kun je definiëren met één WITH-statement

...), <another-cte-name> AS (...)
Maakt filteren en bewerken van tabellen begrijpelijker
JOIN meerdere tijdelijke resultsets
Subqueries binnen een andere CTE

 WITH <cte-name> AS (

     <query>

 ), <another-cte-name> AS (

     -- Voeg een andere query toe!
     <another-query>
 )


 -- Deze CTE's kun je JOIN'en 
 SELECT ... ;

Tijdelijke resultsets joinen

-- Eerste common table expression
WITH seniors AS (  
    SELECT student_id, first_name FROM STUDENTS.personal_info WHERE graduation_year = 2025

-- Tweede common table expression
), final_exam_grades AS (                           
    SELECT student_id, course_name, exam_score FROM STUDENTS.grades WHERE exam_type = 'Final'
)


SELECT
    seniors.first_name, final_exam_grades.course_name, final_exam_grades.exam_score
FROM final_exam_grades

-- Join de tijdelijke resultsets
JOIN seniors ON final_exam_grades.student_id = seniors.student_id

Tijdelijke resultsets joinen

             first_name  |  course_name  |  exam_score
            ------------ | ------------- | ------------
              Ryan       |  Calculus I   |      97   
              Tatiana    |  Biology      |      98
              Pankaj     |  English III  |      92
              Taylor     |  Python       |      71
              Iris       |  Finance      |      89
              Charles    |  Marketing    |      88

                                ...

Een tijdelijk resultaat in een CTE gebruiken

WITH ny_schools AS (
    SELECT school_id, school_name, district FROM SCHOOL.school_info WHERE school_state = 'NY'
), ny_teachers AS (
    SELECT
        teacher_id, teacher_name, tenure, specialty
    FROM SCHOOLS.teachers

    WHERE school_id IN (SELECT school_id FROM ny_schools)  -- Filter op records in ny_schools

)

SELECT
    ny_teachers.teacher_name, ny_teachers.course_name, previous_courses.term
FROM SCHOOLS.previous_courses
JOIN ny_teachers ON previous_courses.teacher_id = ny_teachers.teacher_id AND
    previous_courses.course_area = ny_teachers.specialty
;

Een tijdelijk resultaat in een CTE gebruiken

WITH ny_schools AS (
    SELECT 
        school_id, 
        ...
    WHERE school_state = 'NY'
), ny_teachers AS (
    SELECT
        ...
    FROM SCHOOLS.teachers


    -- Filter op school_id in bovenstaande records
    WHERE school_id IN (
        SELECT school_id FROM ny_schools
    )

)

Eerst alleen filteren op school_id's in NY
Tijdelijk resultaat opgeslagen in ny_schools
In ny_teachers filteren op school_id in ny_schools
Je kunt ook JOIN gebruiken, enz.

Werkdruk van docenten koppelen aan hun specialisme

...  -- De ny_teachers-CTE opbouwen

SELECT
    ny_teachers.teacher_name, 
    ny_teachers.course_name, 
    previous_courses.term
FROM SCHOOLS.previous_courses

-- Tot slot de CTE joinen met de tabel previous_courses
JOIN ny_teachers ON 
    previous_courses.teacher_id = ny_teachers.teacher_id AND
    previous_courses.course_area = ny_teachers.specialty
;

Werkdruk van docenten koppelen aan hun specialisme

         teacher_name     |       course_name       |  term  
      ------------------- | ----------------------- | -------
       Marcus Lee         |  U.S. History           |  H1'22
       Priya Desai        |  Algebra II             |  H2'22
       Elena Rodríguez    |  Environmental Science  |  H1'25
       Jamal Thompson     |  English I              |  H2'21
       Mei-Ling Chen      |  World History          |  H1'23
       Gregory O'Malley   |  Calculus II            |  H2'24

                                ...

Laten we oefenen!

Gegevenstypen en functies in Snowflake