Melakukan JOIN dalam rencana kueri

China dan India masing-masing memiliki lebih dari 1 miliar penduduk dan secara bersama-sama mencakup lebih dari 30% populasi dunia. Anda ingin melihat apakah keduanya merupakan negara dengan pertumbuhan penduduk terbesar.

Karena keduanya berada di Asia, Anda mengumpulkan data populasi dari World Bank untuk setiap negara Asia antara 1990 hingga 2017. Untuk menyederhanakan analisis, Anda memutuskan membandingkan data populasi dari dua tahun. Anda membandingkan populasi terbaru (2017) dengan populasi tahun 1990 untuk mendapatkan metrik pertumbuhan penduduk.

Apakah India dan China berada di puncak daftar? Apakah mereka negara Asia dengan pertumbuhan terbanyak sejak tahun 1990-an?

Setelah menulis kueri, periksa rencana kueri untuk melihat bagaimana JOIN memengaruhi langkah-langkah dalam rencana tersebut.

Latihan ini adalah bagian dari kursus

Meningkatkan Performa Kueri di PostgreSQL

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

SELECT old.country
, old.region
, old.population_1990
, new.population_2017
, 100*((new.population_2017 - old.population_1990)/new.population_2017::float) as population_growth
FROM pop_1990 old
INNER JOIN pop_2017 new 
USING(___)
ORDER BY population_growth DESC

Edit dan Jalankan Kode

Latihan ini adalah bagian dari kursus

Meningkatkan Performa Kueri di PostgreSQL

SkillTag.level.intermediateSkillTag.label

4.8+

Mulai Kursus Gratis

Bundle up as you dive into the Winter Olympics! You will learn how to join, subquery, and create temporary tables while finding which Olympic athletes brave sub-freezing temperatures to train. You will also learn about the query planner and how its functionality can guide your SQL structure to faster queries.

Exercise 1: All about joins Exercise 2: Where are all the athletes from Exercise 3: Using different joins to explore athletes' regions Exercise 4: What about the weather Exercise 5: Subqueries and common table expressions (cte)Exercise 6: Filtering to freezing with a subquery Exercise 7: Where winter is white Exercise 8: Countries with subqueries or CTEs Exercise 9: Working with temporary tables Exercise 10: Canadians temp table Exercise 11: Analyze that temp table

Dig up those past algebra memories while learning the SQL order of operations. Find which countries "should" have the most athletes by looking at population and gross domestic product (GDP) while learning the best way to filter. You will also learn when your query aggregates (sums, counts, etc.) and how you can structure your query to optimize this process.

Exercise 1: What you write is not what SQL sees Exercise 2: Order of operations impact on query structure Exercise 3: Group by and aggregations Exercise 4: Count and count distinct Exercise 5: Filtering in the WHERE clause Exercise 6: OR versus IN with athletes Exercise 7: Data type filters Exercise 8: EXPLAIN the filter query plan step Exercise 9: Filtering while joining Exercise 10: Where to place a region filter Exercise 11: Filtering in the join, where, and select Exercise 12: Aggregating with different data granularities Exercise 13: Aggregate before joining tables Exercise 14: South African trends

Zero in on the properties that improve database performance. Discover when your table is not a table but a view. Learn how your database's storage structure (row or column oriented) impacts your query structure. You will explore volcanic smog while using partitions and indexes to speed your queries.

Exercise 1: Queries and tables and views, oh my Exercise 2: Data loading and storage Exercise 3: Finding the table type Exercise 4: Row-oriented storage and partitions Exercise 5: Row-oriented storage properties Exercise 6: Previewing a row-oriented table Exercise 7: Partitioning on location Exercise 8: Using and creating indexes Exercise 9: Finding the database indexes Exercise 10: Creating and using an index Exercise 11: Compare runtimes Exercise 12: Using column-oriented storage Exercise 13: Column-oriented storage properties Exercise 14: Using the information schema

Learn the lingo of the Query Lifecycle and dive into the query planner. Explore how the query planner creates and optimizes the query plan. Find your next vacation locale by looking for countries with recent population growth while also seeing how a join impacts the query steps. Fine tune your optimization techniques by seeing how different filters speed your query times.

Exercise 1: Siklus hidup kueri dan perencana Exercise 2: Menjelajahi pg_tables Exercise 3: EXPLAIN dasar Exercise 4: Pemindaian indeks Exercise 5: EXPLAIN pada WHERE Exercise 6: Menyelami EXPLAIN lebih dalam Exercise 7: Parameter EXPLAIN Exercise 8: Mengagregasi dan mengurutkan populasi Exercise 9: Melakukan JOIN dalam rencana kueri

Latihan Saat Ini

Exercise 10: Struktur kueri dan eksekusi kueri Exercise 11: Subkueri vs. CTE Exercise 12: Mengapa berbeda?Exercise 13: Dampak penyaringan Exercise 14: Selamat