70% pertama dari kursus SQL tampaknya cukup mudah. Kesulitan dimulai pada 30% sisanya.Dari 2015 hingga 2019, saya melakukan empat wawancara untuk posisi analis data dan spesialis analisis data di lebih dari selusin perusahaan. Setelah wawancara yang gagal pada tahun 2017 - ketika saya bingung tentang pertanyaan SQL yang kompleks - saya mulai menyusun buku tugas dengan pertanyaan SQL dengan kompleksitas sedang dan tinggi untuk mempersiapkan persiapan wawancara yang lebih baik. Panduan ini sangat berguna dalam putaran terakhir wawancara pada tahun 2019. Selama setahun terakhir, saya berbagi panduan ini dengan beberapa teman, dan berkat waktu luang ekstra karena pandemi, saya memolesnya dan menyusun dokumen ini.Ada banyak tutorial SQL yang bagus untuk pemula. Favorit saya adalahKursus interaktif SQL dan Select Star SQL dari Codecademy oleh Zi Chung Kao. Namun pada kenyataannya, 70% pertama dari kursus SQL cukup sederhana, dan kesulitan sebenarnya dimulai pada 30% sisanya, yang tidak tercakup dalam panduan pemula. Jadi, pada wawancara untuk analis data dan spesialis analisis data di perusahaan teknologi sering mengajukan pertanyaan tentang 30% ini.Anehnya, saya tidak menemukan sumber lengkap tentang masalah kesulitan sedang, jadi saya menyusun panduan ini.Ini berguna untuk wawancara, tetapi pada saat yang sama akan meningkatkan efektivitas Anda dalam pekerjaan Anda saat ini dan masa depan. Secara pribadi, saya percaya bahwa beberapa templat SQL yang disebutkan juga berguna untuk sistem ETL yang menjalankan alat pelaporan dan fungsi analisis data untuk mengidentifikasi tren.
Anda perlu memahami bahwa selama wawancara dengan analis data dan analis data, mereka tidak hanya bertanya tentang SQL. Topik umum lainnya termasuk diskusi tentang proyek-proyek sebelumnya, pengujian A / B, pengembangan metrik, dan masalah analitik terbuka. Sekitar tiga tahun yang lalu, Quora memposting tips wawancara untuk posisi analis produk di Facebook. Di sana, topik ini dibahas secara lebih rinci. Namun, jika meningkatkan pengetahuan Anda tentang SQL akan membantu Anda dalam wawancara Anda, maka panduan ini sepadan dengan waktu.Di masa depan, saya dapat port kode dari panduan ini ke situs seperti Select Star SQLuntuk membuatnya lebih mudah untuk menulis pernyataan SQL - dan melihat hasil eksekusi kode secara real time. Sebagai pilihan, tambahkan pertanyaan sebagai masalah ke platform untuk mempersiapkan wawancara LeetCode . Sementara itu, saya hanya ingin menerbitkan dokumen ini sehingga orang sekarang dapat berkenalan dengan informasi ini.Asumsi dibuat dan cara menggunakan manual
Asumsi tentang pengetahuan bahasa SQL: Diasumsikan bahwa Anda memiliki pengetahuan tentang SQL. Anda mungkin sering menggunakannya di tempat kerja, tetapi ingin mengasah keterampilan Anda dalam topik seperti asosiasi diri dan fungsi jendela.Cara menggunakan manual ini: Karena papan atau notebook virtual sering digunakan dalam wawancara (tanpa menyusun kode), saya sarankan mengambil pensil dan kertas dan menuliskan solusi untuk setiap masalah, dan setelah menyelesaikan, bandingkan catatan Anda dengan jawabannya. Atau jawab jawaban Anda dengan seorang teman yang akan bertindak sebagai pewawancara!- Kesalahan sintaksis kecil tidak terlalu penting selama wawancara dengan papan tulis atau notepad. Tetapi mereka dapat mengganggu pewawancara, jadi idealnya mencoba mengurangi jumlah mereka untuk memusatkan semua perhatian pada logika.
- Jawaban yang diberikan belum tentu satu-satunya cara untuk menyelesaikan setiap masalah. Jangan ragu untuk menulis komentar dengan solusi tambahan yang dapat Anda tambahkan ke panduan ini!
Kiat untuk menyelesaikan tugas kompleks dalam wawancara SQL
Pertama, kiat standar untuk semua wawancara pemrograman ...- Dengarkan deskripsi masalah dengan seksama, ulangi esensi masalah kepada pewawancara
- Merumuskan kasus batas untuk menunjukkan bahwa Anda benar-benar memahami masalah (mis., Garis yang tidak akan disertakan dalam kueri SQL akhir yang akan Anda tulis)
- ( ) , β : ,
- SQL, , . , .
Beberapa masalah yang tercantum di sini diadaptasi dari entri blog Periscope lama (sebagian besar ditulis oleh Sean Cook sekitar 2014, meskipun kepengarangannya tampaknya telah dihapus dari materi setelah SiSense bergabung dengan Periscope ), serta dari diskusi tentang StackOverflow. Jika perlu, sumber ditandai di awal setiap pertanyaan.Pada Select Star SQL juga merupakan pilihan brainteasers yang baik , masalah pelengkap dokumen ini.Harap dicatat bahwa pertanyaan-pertanyaan ini bukan salinan harfiah dari pertanyaan-pertanyaan dari wawancara saya sendiri, dan itu tidak digunakan di perusahaan tempat saya bekerja atau bekerja.Tugas Asosiasi Sendiri
No. 1. Persentase perubahan bulan ke bulan
Konteks: Seringkali berguna untuk mengetahui bagaimana metrik kunci berubah, misalnya, pemirsa bulanan pengguna aktif, dari bulan ke bulan. Katakanlah kita memiliki tabel logins
dalam formulir ini:| user_id | tanggal |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |
Tujuan : menemukan perubahan persentase bulanan di pemirsa bulanan pengguna aktif (MAU).Solusi:(Solusi ini, seperti blok kode lainnya dalam dokumen ini, berisi komentar tentang elemen sintaks SQL yang mungkin berbeda antara varian SQL yang berbeda, dan catatan lainnya)WITH mau AS
(
SELECT
DATE_TRUNC('month', date) month_timestamp,
COUNT(DISTINCT user_id) mau
FROM
logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
a.month_timestamp previous_month,
a.mau previous_mau,
b.month_timestamp current_month,
b.mau current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM
mau a
JOIN
mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'
No. 2. Menandai struktur pohon
Konteks: misalkan Anda memiliki tabel tree
dengan dua kolom: yang pertama menunjukkan node, dan yang kedua node induk.simpul orangtua
12
2 5
3 5
4 3
5 NULL
Tugas: untuk menulis SQL sedemikian rupa sehingga kami menunjuk setiap node sebagai internal (root), root (root) atau end node / leaf (leaf), sehingga untuk nilai di atas Anda mendapatkan yang berikut:node label
1 Leaf
2 Inner
3 Inner
4 Leaf
5 Root
(Catatan: informasi lebih lanjut tentang terminologi struktur data seperti pohon dapat ditemukan di sini . Namun, tidak diperlukan untuk menyelesaikan masalah ini!)Solusi:Pengakuan: Fabian Hoffman mengusulkan solusi yang lebih umum ini pada 2 Mei 2020. Terima kasih FabianWITH join_table AS
(
SELECT
cur.node,
cur.parent,
COUNT(next.node) AS num_children
FROM
tree cur
LEFT JOIN
tree next ON (next.parent = cur.node)
GROUP BY
cur.node,
cur.parent
)
SELECT
node,
CASE
WHEN parent IS NULL THEN "Root"
WHEN num_children = 0 THEN "Leaf"
ELSE "Inner"
END AS label
FROM
join_table
Solusi alternatif, tanpa koneksi eksplisit:Pengakuan: William Chardgin pada 2 Mei 2020 menarik perhatian pada kebutuhan akan kondisi WHERE parent IS NOT NULL
bahwa solusi ini kembali Leaf
sebagai gantinya NULL
. William terima kasih!SELECT
node,
CASE
WHEN parent IS NULL THEN 'Root'
WHEN node NOT IN
(SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
END AS label
from
tree
3. Retensi pengguna per bulan (beberapa bagian)
Pengakuan: Tugas ini diadaptasi dari artikel blog SiSense, βMenggunakan Self-Associations untuk Menghitung Retensi, Aliran, dan Pengaktifan Kembali . βBagian 1
Konteks: misalkan kami memiliki statistik otorisasi pengguna di situs dalam tabel logins
:| user_id | tanggal |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |
Tugas: tulis permintaan yang menerima jumlah pengguna yang ditahan per bulan. Dalam kasus kami, parameter ini didefinisikan sebagai jumlah pengguna yang masuk ke sistem pada bulan ini dan bulan sebelumnya.Keputusan:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT a.user_id) retained_users
FROM
logins a
JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
GROUP BY
date_trunc('month', a.date)
Pengakuan:Tom Moertel menunjukkan bahwa pra-duplikasi user_id sebelum bergabung sendiri membuat solusi lebih efisien, dan menyarankan kode di bawah ini. Terima kasih tom!Solusi alternatif:WITH DistinctMonthlyUsers AS (
SELECT DISTINCT
DATE_TRUNC('MONTH', a.date) AS month_timestamp,
user_id
FROM logins
)
SELECT
CurrentMonth.month_timestamp month_timestamp,
COUNT(PriorMonth.user_id) AS retained_user_count
FROM
DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
DistinctMonthlyUsers AS PriorMonth
ON
CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
AND
CurrentMonth.user_id = PriorMonth.user_id
Bagian 2
Tugas: sekarang kami mengambil tugas sebelumnya menghitung jumlah pengguna yang ditahan per bulan - dan mengubahnya menjadi terbalik. Kami akan menulis permintaan untuk menghitung pengguna yang belum kembali ke situs bulan ini. Artinya, pengguna "hilang".Keputusan:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT b.user_id) churned_users
FROM
logins a
FULL OUTER JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
WHERE
a.user_id IS NULL
GROUP BY
DATE_TRUNC('month', a.date)
Harap dicatat bahwa masalah ini juga dapat diatasi dengan LEFT
atau RIGHT
.Bagian 3
Catatan: ini mungkin tugas yang lebih sulit daripada yang akan ditawarkan pada wawancara nyata. Pikirkan itu lebih seperti teka-teki - atau Anda dapat melewati dan melanjutkan ke tugas berikutnya.Konteks : jadi kami melakukan pekerjaan dengan baik dari dua masalah sebelumnya. Di bawah ketentuan tugas baru, kami sekarang memiliki tabel pengguna yang hilang user_churns
. Jika pengguna aktif dalam sebulan terakhir, tetapi kemudian tidak aktif dalam hal ini, maka ia dimasukkan dalam tabel untuk bulan ini. Begini tampilannya user_churns
:| user_id | month_date |
| --------- | ------------ |
| 1 | 2018-05-01 |
| 234 | 2018-05-01 |
| 3 | 2018-05-01 |
| 12 | 2018-05-01 |
| ... | ... |
| 234 | 2018-10-01 |
Tugas : sekarang Anda ingin melakukan analisis kohort, yaitu analisis totalitas pengguna aktif yang telah diaktifkan kembali di masa lalu . Buat tabel dengan pengguna ini. Anda bisa menggunakan tabel user_churns
dan membuat kohort logins
. Di Postgres, cap waktu saat ini dapat diakses melalui current_timestamp
.Keputusan:WITH user_login_data AS
(
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
a.user_id,
MAX(b.month_date) as most_recent_churn,
MAX(DATE_TRUNC('month', c.date)) as most_recent_active
FROM
logins a
JOIN
user_churns b
ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
JOIN
logins c
ON a.user_id = c.user_id
AND
DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
WHERE
DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
GROUP BY
DATE_TRUNC('month', a.date),
a.user_id
HAVING
most_recent_churn > most_recent_active
No. 4. Meningkatkan total
Pengakuan: Tugas ini diadaptasi dari artikel blog SiSense , Cash Flow Modeling dalam SQL .Konteks: misalkan kita memiliki tabel transactions
dalam bentuk ini:| tanggal | cash_flow |
| ------------ | ----------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -100 |
| 2018-01-03 | 50 |
| ... | ... |
Di mana cash_flow
pendapatan dikurangi biaya untuk setiap hari.Tujuan: menulis permintaan untuk mendapatkan jumlah total arus kas setiap hari sedemikian rupa sehingga pada akhirnya Anda mendapatkan tabel dalam formulir ini:| tanggal | cumulative_cf |
| ------------ | --------------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -1100 |
| 2018-01-03 | -1050 |
| ... | ... |
Keputusan:SELECT
a.date date,
SUM(b.cash_flow) as cumulative_cf
FROM
transactions a
JOIN b
transactions b ON a.date >= b.date
GROUP BY
a.date
ORDER BY
date ASC
Solusi alternatif menggunakan fungsi jendela (lebih efisien!):SELECT
date,
SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
transactions
ORDER BY
date ASC
Tidak. 5. Rata-Rata Bergerak
Pengakuan: Tugas ini diadaptasi dari artikel blog SiSense , Moving Averages di MySQL dan SQL Server .Catatan: moving average dapat dihitung dengan berbagai cara. Di sini kami menggunakan rata-rata sebelumnya. Dengan demikian, metrik untuk hari ketujuh dalam sebulan akan menjadi rata-rata dari enam hari sebelumnya dan dirinya sendiri.Konteks : misalkan kita memiliki tabel signups
dalam bentuk ini:| tanggal | sign_ups |
| ------------ | ---------- |
| 2018-01-01 | 10 |
| 2018-01-02 | 20 |
| 2018-01-03 | 50 |
| ... | ... |
| 2018-10-01 | 35 |
Tugas : tulis permintaan untuk mendapatkan rata-rata bergerak harian pendaftaran 7 hari.Keputusan:SELECT
a.date,
AVG(b.sign_ups) average_sign_ups
FROM
signups a
JOIN
signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
a.date
Tidak. 6. Beberapa kondisi koneksi
Pengakuan: Tugas ini diadaptasi dari artikel blog SiSense, "Menganalisis Email Anda Menggunakan SQL" .Konteks: katakanlah tabel kami emails
berisi email yang dikirim dari alamat zach@g.com
dan diterima di dalamnya:| id | subjek | dari | untuk | timestamp |
| ---- | ---------- | -------------- | -------------- | --- ------------------ |
| 1 | Yosemite | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 |
| 2 | Sur Besar | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
| 3 | Yosemite | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
| 4 | Lari | jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
| 5 | Yosemite | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
| 6 | Yosemite | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
| .. | .. | .. | .. | .. |
Tugas: tulis permintaan untuk mendapatkan waktu respons untuk setiap huruf ( id
) yang dikirim ke zach@g.com
. Jangan sertakan surat ke alamat lain. Misalkan setiap utas memiliki tema yang unik. Perlu diingat bahwa utas mungkin memiliki beberapa surat pulang-pergi antara zach@g.com
dan penerima lainnya.Keputusan:SELECT
a.id,
MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
emails a
JOIN
emails b
ON
b.subject = a.subject
AND
a.to = b.from
AND
a.from = b.to
AND
a.timestamp < b.timestamp
WHERE
a.to = 'zach@g.com'
GROUP BY
a.id
Tugas untuk fungsi jendela
1. Cari pengenal dengan nilai maksimum
Konteks: Misalkan kita memiliki tabel salaries
dengan data tentang departemen dan gaji karyawan dalam format berikut: depname | empno | gaji |
----------- + ------- + -------- +
mengembangkan | 11 | 5200 |
mengembangkan | 7 | 4200 |
mengembangkan | 9 | 4500 |
mengembangkan | 8 | 6000 |
mengembangkan | 10 | 5200 |
personil | 5 | 3500 |
personil | 2 | 3900 |
penjualan | 3 | 4800 |
penjualan | 1 | 5000 |
penjualan | 4 | 4800 |
Tugas : menulis permintaan untuk mendapatkan empno
gaji tertinggi. Pastikan solusi Anda menangani kasus gaji yang sama!Keputusan:WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salary
Solusi alternatif menggunakan RANK()
:WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;
2. Nilai rata-rata dan peringkat dengan fungsi jendela (beberapa bagian)
Bagian 1
Konteks : misalkan kita memiliki tabel salaries
dalam format ini: depname | empno | gaji |
----------- + ------- + -------- +
mengembangkan | 11 | 5200 |
mengembangkan | 7 | 4200 |
mengembangkan | 9 | 4500 |
mengembangkan | 8 | 6000 |
mengembangkan | 10 | 5200 |
personil | 5 | 3500 |
personil | 2 | 3900 |
penjualan | 3 | 4800 |
penjualan | 1 | 5000 |
penjualan | 4 | 4800 |
Tugas: tulis kueri yang mengembalikan tabel yang sama, tetapi dengan kolom baru yang menunjukkan gaji rata-rata untuk departemen. Kami akan mengharapkan tabel seperti ini: depname | empno | gaji | avg_salary |
----------- + ------- + -------- + ------------ +
mengembangkan | 11 | 5200 | 5020 |
mengembangkan | 7 | 4200 | 5020 |
mengembangkan | 9 | 4500 | 5020 |
mengembangkan | 8 | 6000 | 5020 |
mengembangkan | 10 | 5200 | 5020 |
personil | 5 | 3500 | 3700 |
personil | 2 | 3900 | 3700 |
penjualan | 3 | 4800 | 4867 |
penjualan | 1 | 5000 | 4867 |
penjualan | 4 | 4800 | 4867 |
Keputusan:SELECT
*,
ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
salaries
Bagian 2
Tugas: tulis kueri yang menambahkan kolom dengan posisi masing-masing karyawan di lembar waktu berdasarkan gajinya di departemennya, di mana karyawan dengan gaji tertinggi mendapat posisi 1. Kita akan mengharapkan tabel dalam formulir ini: depname | empno | gaji | gaji_kini |
----------- + ------- + -------- + ------------- +
mengembangkan | 11 | 5200 | 2 |
mengembangkan | 7 | 4200 | 5 |
mengembangkan | 9 | 4500 | 4 |
mengembangkan | 8 | 6000 | 1 |
mengembangkan | 10 | 5200 | 2 |
personil | 5 | 3500 | 2 |
personil | 2 | 3900 | 1 |
penjualan | 3 | 4800 | 2 |
penjualan | 1 | 5000 | 1 |
penjualan | 4 | 4800 | 2 |
Keputusan:SELECT
*,
RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
FROM
salaries
Tugas-tugas lain dari kesulitan sedang dan tinggi
No. 1. Histogram
Konteks: Katakanlah kita memiliki tabel di sessions
mana setiap baris mewakili sesi streaming video dengan panjang dalam detik:| session_id | length_seconds |
| ------------ | ---------------- |
| 1 | 23 |
| 2 | 453 |
| 3 | 27 |
| .. | .. |
Tugas: menulis kueri untuk menghitung jumlah sesi yang jatuh interval lima detik, yaitu, untuk fragmen di atas, hasilnya akan menjadi seperti ini:| ember | hitung |
| --------- | ------- |
| 20-25 | 2 |
| 450-455 | 1 |
Skor maksimum dihitung untuk label garis yang tepat ("5-10", dll.)Solusi:WITH bin_label AS
(SELECT
session_id,
FLOOR(length_seconds/5) as bin_label
FROM
sessions
)
SELECT
CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
COUNT(DISTINCT session_id) count
GROUP BY
bin_label
ORDER BY
bin_label ASC
2. Sambungan silang (beberapa bagian)
Bagian 1
Konteks: katakanlah kita memiliki tabel di state_streams
mana nama negara dan jumlah total streaming dari hosting video ditunjukkan pada setiap baris:| negara | total_streams |
| ------- | --------------- |
| NC | 34569 |
| SC | 33999 |
| CA | 98324 |
| MA | 19345 |
| .. | .. |
(Faktanya, tabel agregat dari jenis ini biasanya memiliki kolom tanggal, tetapi kami akan mengecualikannya untuk tugas ini)Tugas: menulis kueri untuk mendapatkan pasangan negara dengan jumlah utas total dalam ribuan dari satu sama lain. Untuk cuplikan di atas, kami ingin melihat sesuatu seperti:| state_a | state_b |
| --------- | --------- |
| NC | SC |
| SC | NC |
Keputusan:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a
CROSS JOIN
state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Sebagai informasi, gabungan silang juga dapat ditulis tanpa secara spesifik menentukan gabungan:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Bagian 2
Catatan: ini adalah pertanyaan bonus daripada templat SQL yang sangat penting. Anda bisa melewatinya!Tugas: bagaimana saya bisa memodifikasi SQL dari solusi sebelumnya untuk menghapus duplikat? Misalnya, contoh meja yang sama, untuk dikukus NC
dan SC
hanya ada satu kali, bukan dua.Keputusan:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state > b.state
3. Perhitungan lanjutan
Pengakuan: Tugas ini diadaptasi dari diskusi pada pertanyaan yang saya ajukan di StackOverflow (nama panggilan saya adalah zthomas.nc).Catatan: ini mungkin tugas yang lebih sulit daripada yang akan ditawarkan pada wawancara nyata. Pikirkan itu lebih seperti puzzle - atau Anda bisa melewatinya!Konteks: misalkan kita memiliki tabel table
semacam ini, di mana user
nilai yang berbeda dari suatu kelas dapat berhubungan dengan pengguna yang sama class
:| pengguna | kelas |
| ------ | ------- |
| 1 | a |
| 1 | b |
| 1 | b |
| 2 | b |
| 3 | a |
Masalah: Misalkan hanya ada dua nilai yang mungkin untuk suatu kelas. Tulis kueri untuk menghitung jumlah pengguna di setiap kelas. Dalam hal ini, pengguna dengan kedua label a
dan b
harus merujuk ke kelas b
.Untuk sampel kami, kami mendapatkan hasil berikut:| kelas | hitung |
| ------- | ------- |
| a | 1 |
| b | 2 |
Keputusan:WITH usr_b_sum AS
(
SELECT
user,
SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
FROM
table
GROUP BY
user
),
usr_class_label AS
(
SELECT
user,
CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
FROM
usr_b_sum
)
SELECT
class,
COUNT(DISTINCT user) count
FROM
usr_class_label
GROUP BY
class
ORDER BY
class ASC
Solusi alternatif menggunakan instruksi SELECT
di operator SELECT
dan UNION
:SELECT
"a" class,
COUNT(DISTINCT user_id) -
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
"b" class,
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count